Skip to content

Product database

Partial

Workspace schema, access policies and configuration pinning exist; the fuller proposed memory and block-editing models do not.

Code and tests

Product PostgreSQL owns users, organizations, workspaces, accepted revisions, memory, tasks and accounting. RLS and resource-level authorization are enforced through scoped product database roles. Both Next app repositories and Go product services access this database.

erDiagram
  USER ||--o{ MEMBERSHIP : joins
  ORGANIZATION ||--o{ MEMBERSHIP : grants
  ORGANIZATION ||--o{ PROJECT : owns
  PROJECT ||--o{ PROJECT_MEMBER : authorizes
  USER ||--o{ PROJECT_MEMBER : participates
  ORGANIZATION ||--o{ CONVERSATION : owns
  PROJECT o|--o{ CONVERSATION : groups
  CONVERSATION ||--o{ MESSAGE : contains
  PROJECT o|--o{ DOCUMENT : owns
  CONVERSATION o|--o{ DOCUMENT : owns
  DOCUMENT ||--o{ DOCUMENT_VERSION : versions
  CONVERSATION ||--o{ TASK : requests
  TASK ||--o{ DURABLE_RUN : executes

A document has exactly one owner: a project or a conversation. Document versions and head pointers must belong to that same document and tenant. Project membership does not automatically expose restricted conversations.

AreaResponsibility
IdentityUsers, external identities, memberships and invitations
WorkspaceProjects, conversation visibility, messages and source membership
Work productsDocuments, immutable revisions, proposals and accepted heads
ExecutionTasks, runs, input dependencies, progress and committed commands
AccountingEntitlements, credits, provider attempts, reservations and settlement
OperationsWorker presence, audit/security events and durable blob cleanup

Durable execution owns command commit semantics; Workers & recovery owns publication authority.

Projects capture a search configuration; project conversations inherit it and standalone conversations capture the default. Tasks and ingestion runs pin their selection at admission. Default changes affect new scopes without silently changing existing work.

Moving a conversation into a project adopts the destination configuration and queues missing private generations from existing extracted text. Preserve document versions/citation anchors and do not charge a second OCR reservation. Already admitted tasks retain their captured configuration.

Runtime roles use RLS-scoped access; workers and schema maintenance have separate restricted credentials. Product services never query legal PostgreSQL directly.

Storage defines derived indexing and search configurations defines profile retention.

Ownership checks and composite references are defined in platform/db/schema/workspace.ts; RLS is in the product policies. Both are under apps/kesita-app/. These constraints stop a document version or head from referencing another tenant or document. TestPostgresWorkspacePoliciesDirectlyEnforceAccess covers direct SQL access, while TestEmbeddingConfigurationPinningAndConversationMove covers configuration inheritance and moves. The full proposed block-editing and memory-revision models remain unimplemented.