Schema Design for Documents, Chunks, Metadata, and Embeddings

A durable retrieval schema separates source identity, document versions, addressable chunks, permissions, and model-specific embeddings. This prevents one content update from destroying provenance, supports multiple embedding versions during migration, and makes deletion auditable. Put frequently filtered fields in typed columns; reserve JSON for truly variable metadata rather than hiding every business rule.

What will you be able to do?

Model the source-to-chunk hierarchy.
Choose typed columns versus jsonb.
Store multiple model versions safely.
Preserve citation coordinates and content checksums.
Define deletion ownership explicitly.

What are the core entities?

Rendering diagram...

Sources represent external identity. Documents represent versioned normalized content. Chunks are stable citation and retrieval units. Embeddings are derived representations that can coexist by model.

sql

CREATE TABLE app.documents (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  source_key text NOT NULL,
  version integer NOT NULL CHECK (version > 0),
  title text NOT NULL,
  language_code text NOT NULL,
  content_checksum text NOT NULL,
  source_modified_at timestamptz,
  published_at timestamptz,
  deleted_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (tenant_id, source_key, version)
);

CREATE TABLE app.chunks (
  id uuid PRIMARY KEY,
  document_id uuid NOT NULL REFERENCES app.documents(id) ON DELETE CASCADE,
  ordinal integer NOT NULL CHECK (ordinal >= 0),
  heading_path text[] NOT NULL DEFAULT '{}',
  content text NOT NULL CHECK (length(content) > 0),
  token_count integer CHECK (token_count >= 0),
  char_start integer,
  char_end integer,
  chunk_checksum text NOT NULL,
  UNIQUE (document_id, ordinal),
  CHECK (char_start IS NULL OR char_end >= char_start)
);

Embeddings belong in a separate table when you need multiple models or asynchronous lifecycle. A single vector column on chunks is simpler for one stable model; make the tradeoff deliberately.

Which metadata deserves a column?

Use columns for tenant, state, language, dates, model ID, sensitivity, and other frequent filters or constraints. Use jsonb for provider-specific attributes that vary and are not core authorization. If a JSON key becomes a common predicate, promote it or create a carefully justified expression index.

How do citations remain stable?

Store source URI/key, document version, heading path, page or character range, and chunk checksum. A citation should resolve to the exact authorized version that supported the answer. Never cite a URL alone if its content can change silently.

How do you verify it?

Create a fixture with one source, two document versions, several chunks, and two embedding models. Prove both model versions can coexist. Delete only the second document inside a transaction and inspect cascades before rolling back. Use \d+ to review constraints and storage.

What breaks in production?

One giant table duplicates titles and permissions on every chunk.
Mutable URLs are treated as version identity.
Every attribute lives in unvalidated JSON.
Cascade direction does not match data ownership.
Chunk IDs change on every re-run, invalidating labels and citations.

AI pair-work prompt

Normalize this proposed retrieval schema [paste] into sources, document versions, chunks, permissions, model registry, and embeddings. State lifecycle ownership and cascade behavior. Preserve stable citation coordinates. Use typed columns for frequent filters and explain each remaining JSON field.

Verification contract: Load a two-version fixture and test update, re-embedding, access revocation, and deletion without losing the ability to audit the prior state.

Check your understanding

Why separate document versions from sources?
Which fields should not be hidden inside JSON?
When is a separate embedding table better than one vector column?

Official references