Repository-per-table patterns

Plain Human Explanation

Users do not experience tables. They experience workflows: placing an order, receiving a refund, joining a team, or cancelling a subscription.

A repository per table can be fine for simple lookup code. It becomes incidental complexity when the product rule spans several tables and every caller has to remember the right sequence.

Technical Explanation

In TypeScript apps, repository-per-table patterns often create code like usersRepo, ordersRepo, orderItemsRepo, and ledgerRepo all called from one service method. The service now owns transaction order, rollback expectations, and data consistency, even though those are persistence concerns.

Prefer a workflow-shaped persistence boundary when several writes must succeed together. The boundary can still use tables internally, but the caller should ask for the product operation: createPaidOrder, recordRefund, or provisionTeam.

Why It Matters

• User impact: customers do not get half-finished states, such as an order without items or a refund without a ledger entry.
• Product behavior: transactions line up with the workflow the product promises.
• Risk: table-shaped access can scatter one product rule across several files and make partial writes easier.
• Decision point: use a workflow boundary when several tables must change together or be read together as one decision.

The Core Move

Keep table details inside the persistence layer when the caller needs a workflow guarantee. Let the public function name describe the business operation, not the storage layout.

Repository-per-table patterns: Small Example

Bad TypeScript Example

type OrderRepositories = {
  orders: {
    insert(row: { userId: string }): Promise<{
      id: string;
    }>;
  };
  orderItems: {
    insert(row: { orderId: string; sku: string; quantity: number }): Promise<void>;
  };
};

export async function placeOrder(userId: string, sku: string, quantity: number, repos: OrderRepositories) {
  const order = await repos.orders.insert({ userId });
  await repos.orderItems.insert({ orderId: order.id, sku, quantity });
  return order.id;
}

type OrderRepositories = {
  orders: {
    insert(row: {
      userId: string;
    }): Promise<{
      id: string;
    }>;
  };
  orderItems: {
    insert(row: {
      orderId: string;
      sku: string;
      quantity: number;
    }): Promise<void>;
  };
};

export async function placeOrder(
  userId: string,
  sku: string,
  quantity: number,
  repos: OrderRepositories,
) {
  const order = await repos.orders.insert({
    userId,
  });

  await repos.orderItems.insert({
    orderId: order.id,
    sku,
    quantity,
  });

  return order.id;
}

Good TypeScript Example

type PlaceOrderInput = {
  userId: string;
  item: {
    sku: string;
    quantity: number;
  };
};

type PlaceOrderResult = { ok: true; orderId: string } | { ok: false; error: "invalid-quantity" };

type OrderStore = {
  createOrderWithItems(input: PlaceOrderInput): Promise<{
    orderId: string;
  }>;
};

export async function placeOrder(input: PlaceOrderInput, store: OrderStore): Promise<PlaceOrderResult> {
  if (!Number.isInteger(input.item.quantity) || input.item.quantity < 1) {
    return { ok: false, error: "invalid-quantity" };
  }

  const order = await store.createOrderWithItems(input);
  return { ok: true, orderId: order.orderId };
}

type PlaceOrderInput = {
  userId: string;
  item: {
    sku: string;
    quantity: number;
  };
};

type PlaceOrderResult =
  | {
      ok: true;
      orderId: string;
    }
  | {
      ok: false;
      error: "invalid-quantity";
    };

type OrderStore = {
  createOrderWithItems(
    input: PlaceOrderInput,
  ): Promise<{
    orderId: string;
  }>;
};

export async function placeOrder(
  input: PlaceOrderInput,
  store: OrderStore,
): Promise<PlaceOrderResult> {
  if (
    !Number.isInteger(
      input.item.quantity,
    ) ||
    input.item.quantity < 1
  ) {
    return {
      ok: false,
      error: "invalid-quantity",
    };
  }

  const order =
    await store.createOrderWithItems(input);

  return {
    ok: true,
    orderId: order.orderId,
  };
}

What Changed

• The bad version makes every caller know that placing an order means writing both order and item rows.
• The good version keeps the product rule about valid quantities next to the order workflow.
• The store can enforce the transaction internally without exposing table order to product code.

Repository-per-table patterns: Realistic Example

This example uses refunds. A refund is not just a row in a refunds table; it also changes the order and records money movement.

Bad TypeScript Example

type RefundRepos = {
  refunds: {
    insert(row: { orderId: string; amountCents: number }): Promise<{
      id: string;
    }>;
  };
  orders: {
    updateStatus(orderId: string, status: string): Promise<void>;
  };
  ledger: {
    insert(row: { refundId: string; amountCents: number }): Promise<void>;
  };
};

export async function issueRefund(orderId: string, amountCents: number, repos: RefundRepos) {
  const refund = await repos.refunds.insert({ orderId, amountCents });
  await repos.orders.updateStatus(orderId, "refunded");
  await repos.ledger.insert({ refundId: refund.id, amountCents: -amountCents });
  return refund.id;
}

type RefundRepos = {
  refunds: {
    insert(row: {
      orderId: string;
      amountCents: number;
    }): Promise<{
      id: string;
    }>;
  };
  orders: {
    updateStatus(
      orderId: string,
      status: string,
    ): Promise<void>;
  };
  ledger: {
    insert(row: {
      refundId: string;
      amountCents: number;
    }): Promise<void>;
  };
};

export async function issueRefund(
  orderId: string,
  amountCents: number,
  repos: RefundRepos,
) {
  const refund = await repos.refunds.insert(
    {
      orderId,
      amountCents,
    },
  );

  await repos.orders.updateStatus(
    orderId,
    "refunded",
  );

  await repos.ledger.insert({
    refundId: refund.id,
    amountCents: -amountCents,
  });

  return refund.id;
}

Good TypeScript Example

type RefundRequest = {
  orderId: string;
  amountCents: number;
};

type RefundStore = {
  recordRefund(request: RefundRequest): Promise<{
    refundId: string;
    orderStatus: "refunded";
  }>;
};

export async function issueRefund(request: RefundRequest, store: RefundStore) {
  if (!Number.isInteger(request.amountCents) || request.amountCents <= 0) {
    return { ok: false, error: "invalid-refund-amount" };
  }

  const result = await store.recordRefund(request);
  return { ok: true, refundId: result.refundId, orderStatus: result.orderStatus };
}

type RefundRequest = {
  orderId: string;
  amountCents: number;
};

type RefundStore = {
  recordRefund(
    request: RefundRequest,
  ): Promise<{
    refundId: string;
    orderStatus: "refunded";
  }>;
};

export async function issueRefund(
  request: RefundRequest,
  store: RefundStore,
) {
  if (
    !Number.isInteger(
      request.amountCents,
    ) ||
    request.amountCents <= 0
  ) {
    return {
      ok: false,
      error: "invalid-refund-amount",
    };
  }

  const result =
    await store.recordRefund(request);

  return {
    ok: true,
    refundId: result.refundId,
    orderStatus: result.orderStatus,
  };
}

What Changed

• The bad version exposes the table sequence to application code.
• The good version keeps validation in the workflow and persistence consistency inside recordRefund.
• The caller receives a product result, not a pile of table write details.

Repository-per-table patterns: System Example

At system scale, table-shaped repositories can spread one onboarding workflow across organization, membership, subscription, and audit code.

Larger System-Level Bad TypeScript Example

type TeamRepos = {
  organizations: {
    insert(row: { name: string }): Promise<{
      id: string;
    }>;
  };
  memberships: {
    insert(row: { orgId: string; userId: string; role: string }): Promise<void>;
  };
  subscriptions: {
    insert(row: { orgId: string; plan: string }): Promise<void>;
  };
  audits: {
    insert(row: { orgId: string; event: string }): Promise<void>;
  };
};

export async function createTeam(
  input: {
    ownerId: string;
    name: string;
  },
  repos: TeamRepos,
) {
  const org = await repos.organizations.insert({ name: input.name });
  await repos.memberships.insert({ orgId: org.id, userId: input.ownerId, role: "owner" });
  await repos.subscriptions.insert({ orgId: org.id, plan: "team-trial" });
  await repos.audits.insert({ orgId: org.id, event: "team.created" });
  return org.id;
}

type TeamRepos = {
  organizations: {
    insert(row: { name: string }): Promise<{
      id: string;
    }>;
  };
  memberships: {
    insert(row: {
      orgId: string;
      userId: string;
      role: string;
    }): Promise<void>;
  };
  subscriptions: {
    insert(row: {
      orgId: string;
      plan: string;
    }): Promise<void>;
  };
  audits: {
    insert(row: {
      orgId: string;
      event: string;
    }): Promise<void>;
  };
};

export async function createTeam(
  input: {
    ownerId: string;
    name: string;
  },
  repos: TeamRepos,
) {
  const org =
    await repos.organizations.insert({
      name: input.name,
    });

  await repos.memberships.insert({
    orgId: org.id,
    userId: input.ownerId,
    role: "owner",
  });

  await repos.subscriptions.insert({
    orgId: org.id,
    plan: "team-trial",
  });

  await repos.audits.insert({
    orgId: org.id,
    event: "team.created",
  });

  return org.id;
}

Larger System-Level Good TypeScript Example

type CreateTeamInput = {
  ownerId: string;
  name: string;
};

type CreatedTeam = {
  orgId: string;
  ownerRole: "owner";
  plan: "team-trial";
};

type TeamProvisioningStore = {
  createTeamWithOwnerAndTrial(input: CreateTeamInput): Promise<CreatedTeam>;
};

type TeamEvents = {
  recordTeamCreated(team: CreatedTeam): Promise<void>;
};

export async function createTeam(input: CreateTeamInput, store: TeamProvisioningStore, events: TeamEvents) {
  const team = await store.createTeamWithOwnerAndTrial(input);
  await events.recordTeamCreated(team);
  return team;
}

type CreateTeamInput = {
  ownerId: string;
  name: string;
};

type CreatedTeam = {
  orgId: string;
  ownerRole: "owner";
  plan: "team-trial";
};

type TeamProvisioningStore = {
  createTeamWithOwnerAndTrial(
    input: CreateTeamInput,
  ): Promise<CreatedTeam>;
};

type TeamEvents = {
  recordTeamCreated(
    team: CreatedTeam,
  ): Promise<void>;
};

export async function createTeam(
  input: CreateTeamInput,
  store: TeamProvisioningStore,
  events: TeamEvents,
) {
  const team =
    await store.createTeamWithOwnerAndTrial(
      input,
    );

  await events.recordTeamCreated(team);

  return team;
}

What Changed

• The bad version makes application code coordinate four tables in the right order.
• The good version gives persistence one workflow-shaped transaction boundary.
• Auditing stays explicit as a product event without forcing the caller to know every table involved.

Repository-per-table patterns: When To Use It

Use This When

• A workflow needs several table writes to succeed together.
• Callers repeat the same repository sequence in multiple places.
• Reviewers must understand storage layout before they can review product behavior.

Avoid This When

• A screen truly edits one table and has no cross-table product rule.
• You are building an internal admin tool where table shape is the product.
• A workflow boundary would hide useful query details without adding consistency.

Tradeoffs

Workflow-shaped stores can hide SQL details from application code. That is good when they protect consistency, but bad when they become vague persistence services. Keep the public method tied to a real workflow.

Related Concepts

• Shallow abstractions
• Vague helpers
• Mega-services

Repository-per-table patterns: Practice Prompt

Beginner Exercise

Find one workflow that calls two or more table repositories. Write the product operation it is really trying to perform.

Intermediate Exercise

Create a workflow-shaped store method name for that operation. Keep the implementation idea simple: one method that owns the transaction.

Stretch Exercise

Write a test that proves the workflow does not leave a partial state when the second write fails, or explain why no reasonable local test seam exists.

Reflection Question

Is the caller trying to express product behavior, or is it being forced to speak database table language?