Skip to content

Database overview

Implemented

Both schemas and their ownership boundaries exist. Tables do not imply all associated producers or algorithms are implemented.

Code and tests

Product PostgreSQL and legal PostgreSQL are separate authorities. Blob storage holds original bytes and exports; indexes are derived representations with explicit publication and deletion lifecycles.

The diagram below describes canonical ownership. Qdrant additionally serves Legal Core’s vector/sparse candidates and indexed filters; canonical texts, enrichments and relations stay in legal PostgreSQL. See the single Qdrant projection contract for implemented rebuild publication and remaining lifecycle work. This does not change private-document storage.

flowchart LR
  ProductAPI["Next app, Product API and worker"] --> Product[("Product PostgreSQL")]
  ProductAPI --> Blobs[("Private blobs")]
  ProductAPI -->|Authenticated requests| Core["Legal Core"]
  Core --> Legal[("Legal PostgreSQL")]
  Corpus["Corpus worker"] --> Legal
  Corpus --> Originals[("Corpus originals")]
  Core --> Originals
DataOwner
Users, organizations, memberships and entitlementsProduct
Projects, conversations, accepted documents and memoryProduct
Tasks, evidence links, provider accounting and dispatch intentsProduct
Private extraction and retrieval generationsProduct PostgreSQL; product-worker builds private generations
Canonical legal identity, text, validity, citations and classificationLegal
Shared search generations and embedding configurationsLegal, with matching product configuration references

Product definitions live in apps/kesita-app/platform/db/schema/; generate the clean baseline from that model. Legal DDL lives in services/legal-core-api/schema/target.sql. Diagrams explain relationships without duplicating every field.

Use English identifiers and explicit ownership constraints. Tenant keys and composite foreign keys protect relationships; nullable source alternatives need discriminator checks. Cross-domain IDs are validated through contracts, not cross-database foreign keys.

Start with Product, Legal, Storage & indexes and Search configurations.

Composite owner/tenant constraints live in the product schema and legal target.sql. They prevent an otherwise valid ID from joining to the wrong owner. tests/suites/data-foundation/product-data-access.yaml and legal-target-schema.yaml exercise the boundaries. Schema availability does not mean classification or analytics producers are implemented.