Product database

The product database is the source of truth for users, organizations, subscriptions, workspaces, private documents, tasks, and execution state. Product services access it through role-specific credentials and row-level security. Legal corpus data does not live here.

Core relationships

erDiagram
    USER ||--o{ EXTERNAL_IDENTITY : authenticates_with
    USER ||--o{ MEMBERSHIP : joins
    ORGANIZATION ||--o{ MEMBERSHIP : has
    ORGANIZATION ||--o{ PROJECT : owns
    PROJECT ||--o{ PROJECT_MEMBER : grants_access
    USER ||--o{ PROJECT_MEMBER : participates
    ORGANIZATION ||--o{ CONVERSATION : owns
    PROJECT o|--o{ CONVERSATION : groups
    CONVERSATION ||--o{ CONVERSATION_MESSAGE : contains
    PROJECT o|--o{ WORKSPACE_DOCUMENT : contains
    CONVERSATION o|--o{ WORKSPACE_DOCUMENT : contains
    WORKSPACE_DOCUMENT ||--o{ WORKSPACE_DOCUMENT_VERSION : versions
    WORKSPACE_DOCUMENT_VERSION ||--o{ DOCUMENT_CHUNK : indexes
    CONVERSATION ||--o{ TASK : requests
    TASK ||--o{ TASK_EXECUTION_STAGE : progresses_through
    TASK ||--o{ TASK_EVENT : emits
    TASK ||--o{ TASK_INPUT_DEPENDENCY : reads

Every tenant-owned path carries an organization boundary. A workspace document belongs to exactly one project or one conversation. Its immutable versions preserve the content snapshot used by tasks and retrieval.

Supporting domains

flowchart LR
    Identity[Identity<br/>users, memberships, invitations] --> Workspace[Workspace<br/>projects, conversations, documents]
    Billing[Billing and entitlements<br/>plans, subscriptions, capabilities] --> Admission[Work admission]
    Workspace --> Admission
    Admission --> Tasks[Tasks and durable runs]
    Tasks --> Accounting[Credits and model usage]
    Tasks --> Operations[Attempts, workflow commands,<br/>worker presence and blob purge]
    Security[Audit, login, security<br/>and rate-limit events] -. observes .-> Identity
    Security -. observes .-> Workspace

The TypeScript schema under apps/kesita-app/platform/db/schema/ is the readable model. The generated product baseline is the executable database definition.

Object storage and pgvector

flowchart LR
    Upload[Private user file] --> Blob[(Product blob container)]
    Blob -->|path, media type, size and hash| Version[(workspace_document_version)]
    Version --> Extract[Parser and product worker]
    Extract --> Chunk[(document_chunk)]
    Chunk --> Lexical[PostgreSQL full-text index]
    Chunk --> Vector[pgvector<br/>vector 1024]
    Lexical --> Retrieval[Authorized hybrid retrieval]
    Vector --> Retrieval
    Purge[(blob_purge)] -. durable cleanup .-> Blob

PostgreSQL stores authoritative metadata, extracted content, task state, and indexes. The private product blob container stores file bytes; database rows reference those objects by path and integrity metadata. The separate deletion journal supports durable cleanup.

Each private document chunk stores a 1024-dimensional embedding and its embedding model. Retrieval combines vector similarity with the simple full-text index, after tenant authorization. Changing the embedding model requires rebuilding compatible chunks; it is not a configuration-only change.

Ownership rules

• Product APIs and workers never query the legal database directly.
• Request traffic uses the RLS-scoped application role; workers and maintenance use separate restricted roles.
• Blob access is private and service-owned. Browser clients receive only explicitly authorized access.
• Schema changes start in the Drizzle schema, regenerate the baseline, and are verified against a fresh database.