table consolidation

why

merging or splitting tables on a matching label either duplicates ownership logic per variant or forces distinct facts into nullable columns and application branching — both drift, neither can be constrained. deciding on shared identity, owner, lifecycle, and query shape puts each invariant where the database can prove it, and makes adding a variant a bounded change (one extension table).

rule

unify variants in one table only when they are the same durable fact. shared identity, owner, authorization, lifecycle, deletion policy, and primary query shape are the proof. a matching label or a convenient union type is not.

choose the smallest truthful shape

shape	choose it when	example
one typed table	nearly every durable field and rule is shared	`content_asset` with `type` for image, video, and document.
base plus extensions	identity and lifecycle are shared but subtype fields are stable/queryable	`content_asset` plus typed subtype extensions.
separate facts	ownership, state machine, permissions, or primary reads differ	`billing_charge`, `billing_refund`, and `billing_subscription`.

base table owns the facts every subtype must prove: id, owner, type, state, storage/external identity, timestamps.
extension table uses the base id as both primary and foreign key.
typed subtype facts stay out of nullable columns and out of JSONB.

decision test

answer each question explicitly before splitting or merging:

question	same answer for every variant	different answer for a variant
who owns and may read it?	one base fact is plausible	separate fact or a named policy boundary.
how does it begin, change, and end?	one state machine is plausible	separate lifecycle owner.
what makes one row unique?	one key/constraint shape is plausible	separate identity contract.
what does the hot read need?	one indexed projection is plausible	separate read model or table.
which subtype fields are queried or constrained?	no extension needed	typed extension table, never opaque metadata.

use a closed type domain and database checks for type-dependent invariants. application branching must not be the only proof that an image has image fields or that a document lacks video-only state.

examples

notification + notification_delivery: channel is a delivery variation; attempts and provider receipts are a separate lifecycle.
external_record holds provider, external id, sync state, raw audit payload; extract product fields that become filters, joins, or invariants.
charge, refund, subscription stay separate — related money facts, not interchangeable variants; report across them with an owned ledger or projection.
shared subject base only when person and organization truly share references and policy; person/organization-only facts go in extensions.

migration order

name the canonical fact and the target identity, owner, type, and state constraints.
create the target table/extension and indexes without weakening existing authority.
backfill in bounded batches; reconcile row counts, keys, and subtype invariants before moving reads.
move every owned writer and reader to the target shape; remove old tables and compatibility paths in the same approved cutover.

good and bad

bad

-- three separate tables with duplicated logic
create table image_asset (
  id text primary key,
  workspace_id text not null,
  title text not null,
  storage_key text not null unique,
  status text not null, -- 'processing', 'ready', 'deleted'
  width integer not null,
  height integer not null,
  created_at text not null
);

create table video_asset (
  id text primary key,
  workspace_id text not null,
  title text not null,
  storage_key text not null unique,
  status text not null, -- 'processing', 'ready', 'deleted'
  duration_seconds integer not null,
  codec text not null,
  created_at text not null
);

create table document_asset (
  id text primary key,
  workspace_id text not null,
  title text not null,
  storage_key text not null unique,
  status text not null, -- 'processing', 'ready', 'deleted'
  page_count integer not null,
  created_at text not null
);

// now every query must hit multiple tables and union results
async function getAllAssetsInWorkspace(workspaceId: string) {
  const images = await db
    .select()
    .from(image_asset)
    .where(eq(image_asset.workspace_id, workspaceId));
  const videos = await db
    .select()
    .from(video_asset)
    .where(eq(video_asset.workspace_id, workspaceId));
  const documents = await db
    .select()
    .from(document_asset)
    .where(eq(document_asset.workspace_id, workspaceId));

  return [
    ...images.map((i) => ({ type: "image", ...i })),
    ...videos.map((v) => ({ type: "video", ...v })),
    ...documents.map((d) => ({ type: "document", ...d })),
  ];
}

// updating a storage key is now three separate migrations
// deleting an asset requires three separate updates
// status-machine rules are implemented three times

// if status values differ between tables, the app crashes
// if one table changes schema and another doesn't, sync is implicit and fragile

five failures:

ownership and lifecycle logic replicated three times — a status transition or deletion job must update all three tables or assets become orphaned.
listing all assets becomes a three-table union.
storage-key uniqueness unenforceable across types — same key can exist as image and video.
schema cannot express "each asset has exactly one type" — a malformed insert can create an image without width.
each new asset type compounds application complexity.

good

-- one canonical asset table
create table content_asset (
  id text primary key,
  workspace_id text not null references workspace(id),
  slug text not null, -- authored business key
  type text not null, -- 'image', 'video', 'document' — enforced by app
  title text not null,
  storage_key text not null unique, -- globally unique across all types
  status text not null, -- 'processing', 'ready', 'deleted'
  created_at text not null,
  unique (workspace_id, slug), -- scoped authored key
  check (type in ('image', 'video', 'document'))
);

-- type-specific extensions with no fk back, only the base id
create table content_asset_image (
  content_asset_id text primary key references content_asset(id) on delete cascade,
  width integer not null check (width > 0),
  height integer not null check (height > 0)
);

create table content_asset_video (
  content_asset_id text primary key references content_asset(id) on delete cascade,
  duration_seconds integer not null check (duration_seconds > 0),
  codec text not null
);

create table content_asset_document (
  content_asset_id text primary key references content_asset(id) on delete cascade,
  page_count integer not null check (page_count > 0)
);

-- indexes for the common reads
create index content_asset_by_workspace on content_asset(workspace_id, status);

// now one query returns all assets
async function getAllAssetsInWorkspace(workspaceId: string) {
  return await db
    .select()
    .from(content_asset)
    .where(and(eq(content_asset.workspace_id, workspaceId), ne(content_asset.status, "deleted")));
}

// a single status update works for all types
async function markAsReady(assetId: string) {
  await db.update(content_asset).set({ status: "ready" }).where(eq(content_asset.id, assetId));
}

// schema enforces type-specific invariants
// you cannot create a video extension without a base asset
// you cannot delete an asset without cascade-deleting its extension
// the type column is a single source of truth

the schema now enforces:

one asset fact — ownership and lifecycle written once in the base table.
type-safe — a video without duration_seconds cannot exist.
atomic — deleting an asset cascades to its extension.
scalable — a new asset type (e.g. audio) is one new extension table, no duplicated ownership logic.
queryable — listing all assets is one index scan, not three queries.