Design — data model

Date: 2026-04-16
Depends on: ADR-0001, 0002, 0003, 0004

D1 schema (Drizzle)

All tables have tenant_id TEXT NOT NULL except the tenants table itself and global singletons.

-- ── Tenancy ────────────────────────────────────────────────────────────
CREATE TABLE tenants (
  id            TEXT PRIMARY KEY,                    -- nanoid
  slug          TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  plan          TEXT NOT NULL CHECK(plan IN ('free','team','business','growth','enterprise')),
  status        TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','suspended','deleted')),
  llm_route     TEXT DEFAULT 'gateway-anthropic',    -- per-tenant model override
  brand_accent  TEXT DEFAULT '#0072DC',
  allowed_origins TEXT NOT NULL DEFAULT '[]',        -- JSON array
  stripe_customer_id TEXT,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);

-- ── Identity ───────────────────────────────────────────────────────────
CREATE TABLE users (
  id            TEXT PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  name          TEXT,
  avatar_url    TEXT,
  locale        TEXT NOT NULL DEFAULT 'th',
  created_at    INTEGER NOT NULL
);

CREATE TABLE memberships (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id       TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role          TEXT NOT NULL CHECK(role IN ('owner','admin','editor','viewer')),
  status        TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','invited','suspended')),
  invited_by    TEXT,
  created_at    INTEGER NOT NULL,
  UNIQUE(tenant_id, user_id)
);
CREATE INDEX idx_memberships_tenant ON memberships(tenant_id, user_id);

CREATE TABLE invitations (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email         TEXT NOT NULL,
  role          TEXT NOT NULL,
  token         TEXT NOT NULL UNIQUE,
  expires_at    INTEGER NOT NULL,
  accepted_at   INTEGER,
  created_at    INTEGER NOT NULL
);

-- ── Auth artifacts (better-auth) ───────────────────────────────────────
-- better-auth manages `sessions`, `accounts`, `verifications` — see better-auth docs.

-- ── API keys ───────────────────────────────────────────────────────────
CREATE TABLE api_keys (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  hash          TEXT NOT NULL,                        -- sha256(key)
  prefix        TEXT NOT NULL,                        -- puccha_live_abc…
  role          TEXT NOT NULL DEFAULT 'guest',
  scope_json    TEXT,                                 -- optional further restrictions
  allowed_ips   TEXT,                                 -- JSON array or null
  expires_at    INTEGER,
  last_used_at  INTEGER,
  created_by    TEXT NOT NULL REFERENCES users(id),
  created_at    INTEGER NOT NULL,
  revoked_at    INTEGER
);
CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);

-- ── Content ────────────────────────────────────────────────────────────
CREATE TABLE documents (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  collection    TEXT NOT NULL,                        -- 'default' or tenant-defined
  slug          TEXT NOT NULL,                        -- unique within (tenant_id, collection, locale)
  locale        TEXT NOT NULL DEFAULT 'th',
  title         TEXT NOT NULL,
  summary       TEXT,
  content_md    TEXT NOT NULL,                        -- source MD (<= 2MB)
  source_type   TEXT NOT NULL CHECK(source_type IN ('upload','paste','git','api')),
  source_ref    TEXT,                                 -- e.g. R2 key or git ref
  acl_json      TEXT NOT NULL DEFAULT '{"kind":"org"}',
  tags_json     TEXT DEFAULT '[]',
  status        TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','indexing','indexed','failed','archived')),
  index_error   TEXT,
  created_by    TEXT REFERENCES users(id),
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL,
  indexed_at    INTEGER,
  UNIQUE(tenant_id, collection, locale, slug)
);
CREATE INDEX idx_documents_tenant_status ON documents(tenant_id, status);

CREATE TABLE chunks (
  id            TEXT PRIMARY KEY,                     -- `${doc_id}:${ordinal}`
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  doc_id        TEXT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  ordinal       INTEGER NOT NULL,
  anchor        TEXT,
  title         TEXT NOT NULL,
  text          TEXT NOT NULL,
  contextualized TEXT,
  tokens        INTEGER NOT NULL,
  locale        TEXT NOT NULL,
  acl_json      TEXT NOT NULL,                        -- denormalized from document
  updated_at    INTEGER NOT NULL
);
CREATE INDEX idx_chunks_tenant_doc ON chunks(tenant_id, doc_id, ordinal);

-- FTS5 virtual table for BM25 (tenant_id is a prefix column for efficient filtering)
CREATE VIRTUAL TABLE chunks_fts USING fts5(
  tenant_id UNINDEXED,
  chunk_id  UNINDEXED,
  acl_json  UNINDEXED,
  locale    UNINDEXED,
  tokens,                                              -- pre-tokenized by PyThaiNLP for Thai
  tokenize  = 'unicode61 remove_diacritics 2'
);

-- ── Observability ──────────────────────────────────────────────────────
CREATE TABLE query_log (
  id               TEXT PRIMARY KEY,
  tenant_id        TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id          TEXT,
  ts               INTEGER NOT NULL,
  locale           TEXT NOT NULL,
  query_hash       TEXT NOT NULL,
  query_length     INTEGER,
  retrieval_hits   INTEGER,
  rerank_top_score REAL,
  idk              INTEGER NOT NULL DEFAULT 0,
  latency_ms       INTEGER,
  mode             TEXT NOT NULL CHECK(mode IN ('answer','chat','search')),
  chunk_ids_json   TEXT
);
CREATE INDEX idx_query_log_tenant_ts ON query_log(tenant_id, ts);

CREATE TABLE audit_log (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  actor_user_id TEXT,
  actor_api_key_id TEXT,
  action        TEXT NOT NULL,                        -- e.g. 'doc.create', 'role.change'
  target_type   TEXT,
  target_id     TEXT,
  before_json   TEXT,
  after_json    TEXT,
  ip            TEXT,
  user_agent    TEXT,
  ts            INTEGER NOT NULL
);
CREATE INDEX idx_audit_tenant_ts ON audit_log(tenant_id, ts);

-- ── Billing ────────────────────────────────────────────────────────────
CREATE TABLE subscriptions (
  tenant_id         TEXT PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
  stripe_subscription_id TEXT,
  plan              TEXT NOT NULL,
  status            TEXT NOT NULL,                    -- stripe states
  current_period_end INTEGER,
  cancel_at_period_end INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE usage_counters (
  tenant_id     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  period        TEXT NOT NULL,                        -- 'YYYY-MM'
  answers       INTEGER NOT NULL DEFAULT 0,
  chat_turns    INTEGER NOT NULL DEFAULT 0,
  docs_indexed  INTEGER NOT NULL DEFAULT 0,
  seats         INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, period)
);

Key constraints summary

Every content/relational table has tenant_id as the first column of its primary index.
Cascade deletes from tenants clear all tenant data.
chunks.acl_json is denormalized from documents.acl_json → retrieval can filter without joins.
FTS5 has tenant_id UNINDEXED — filterable via WHERE tenant_id = ? outside the MATCH clause (fast).

R2 layout

r2://puccha-uploads/
  {tenantId}/{docId}/original.{ext}          # user-uploaded file
  {tenantId}/{docId}/normalized.md           # post-normalization

r2://puccha-exports/
  {tenantId}/{exportId}/{filename}           # admin export bundles

r2://puccha-audit/
  {tenantId}/{yyyy-mm}/audit.ndjson.gz       # Logpush-written audit archive

All keys are tenant-prefixed. Wrapper API enforces prefixing.

Vectorize namespaces

One namespace per tenant: puccha-chunks-{tenantId}.
Dimension: 1024 (bge-m3).

Metadata schema:

{
  "docId": "doc_...",
  "chunkId": "doc_...:0",
  "ordinal": 0,
  "acl": { "kind": "org" },
  "locale": "th",
  "updatedAt": "2026-04-16T..."
}

Create on first upload per tenant; delete on tenant hard-delete.

KV key conventions

Key pattern	Purpose	TTL
`sess:{tenantId}:{userId}:{sessionId}`	better-auth session	30d
`flag:{tenantId}:{flag}`	Per-tenant feature flag	—
`cache:answer:{tenantId}:{queryHash}:{locale}`	Answer cache (exact-match only)	1h
`ratelimit:{tenantId}:{userId}:{window}`	Soft rate-limit counter	window
`idempotency:{tenantId}:{key}`	API idempotency	24h

Durable Objects

RateLimiter: per-tenant, per-user, per-API-key precise counting (when KV approximation isn’t acceptable).
IngestCoordinator (v1.1): per-tenant lock for serialized ACL-change + re-index operations.

Queues

INGEST: one message per doc ingest job. Consumer Worker runs the full pipeline.
REINDEX: triggered by ACL changes or manual admin action.
WEBHOOK_RETRY (v1.1): for git-sync webhook retries.

Migrations

packages/db/migrations/ — numbered SQL files (0001_init.sql, 0002_add_api_keys.sql, …).
Applied via wrangler d1 migrations apply in a gated GitHub workflow.
Prod migration requires manual approval.
Schema diffing via Drizzle Kit (drizzle-kit generate).

Backups

D1: weekly export to R2 via scheduled Worker (prod).
R2: versioning + lifecycle rules (90-day soft-delete retention).
Vectorize: rebuildable from chunks; no direct backup (accept re-index on disaster recovery).

PII inventory (for PDPA records)

Field	Location	Purpose	Retention
`users.email`	D1	Auth, notifications	Until user deletion
`users.name`, `avatar_url`	D1	UI display	Until user deletion
`query_log.query_hash`	D1	Gap analysis	90d (Free/Team), 1y (Business), configurable (Enterprise)
`query_log.query_text` (raw)	D1	Opt-in only (Business+)	Tenant-configured
`audit_log.ip`, `user_agent`	D1	Security	90d–1y by plan
Message content (chat)	ephemeral (not persisted in v1)	—	Never stored

Tenant admins can export a user’s data bundle + trigger deletion (Right to Erasure) from the admin UI.