06 — Knoll Postgres Schema (Schema v1, plan 2.1)

Purpose. This document specifies the Knoll Analyzer's own Postgres schema — the small "ordinary data" database that a developer turns into Drizzle models (packages/db, plan 2.1/D8). It is the source of truth for all business facts and all relationship facts (the GRAG knowledge graph is only a projection — KG edges are AGE-only, best-effort and non-replayable, see 07-kg-schema-knoll-advisory.md and 13-platform-gaps-issues.md). Field names and enum values mirror the prototype types in next-monorepo/apps/web/lib/types.ts verbatim (now English-identifier) so the mock-data seed (plan 2.2) loads losslessly.

Status / verified against: 2026-07-06 · integration plan tasks/todo.md rev. 2 (plan 2.1, incl. all ⚠ review corrections) · GRAG repo document-processing-pipelines @ db63a95 · prototype apps/web/lib/types.ts, lib/mock-data/*, app/(app)/settings/page.tsx.

Sibling docs: 01-architecture.md (system split), 03-id-conventions.md (id naming conventions — the grag_refs table contract is normative here, §3.15), 04-provisioning-runbook.md (who creates the GRAG side), 08-gutachten-pipeline-spec.md (ai_runs consumer), 10-document-pipeline.md (files + upload lane), 12-gdpr-compliance.md (deletion semantics), 14-cost-model.md (pipeline_id attribution).

1. Scope and design rules

2. Enum definitions

2.1 Domain enums (values verbatim from apps/web/lib/types.ts, English)

Analysis.phase is not an enum: smallint CHECK (phase BETWEEN 1 AND 6) — the six process phases (PHASES in types.ts) are a UI constant, not data.

2.2 Technical enums (English, Knoll-internal)

3. Tables

3.1 Identity & firm

users

firms

fee_tiers (drives analysis pricing, plan 2.1)

Seed (from the prototype Settings page): 10–50 Mio. → 15.000 €; 50–100 Mio. → 30.000 €;

100 Mio. → NULL (individuell). Edits are Partner-only ("Anpassungen sind Partnern vorbehalten"). TODO-VERIFY: whether the pricing model keys the tier on revenue only or also employees/balance_sheet_total (HGB class) — check Knoll-Analyzer-Docs pricing model before wiring the default-fee picker.

3.2 Taxonomy master: success_levers

Master of the closed 11-lever taxonomy. Knoll DB is the master; the GRAG KG entity dictionary is a projection seeded via POST /kg-service/api/v1/entities/upsert (plan 3.5). kg-service has no entity delete route — deactivation here tombstones the KG projection (properties.status="inactive").

Seed (order + slugs; derived with the extraction slugifier — NFKC → lowercase → whitespace→- → strip remaining non-alphanumerics, kg-service/src/kg_service/workers/ extraction.py::slugify, 07-kg-schema-knoll-advisory.md §4.1. Quirk: & is stripped after whitespace joining, so "Products & Services" → products--services with a double hyphen — intentional, byte-identical with the 07 §4.2 upsert payload so document mentions dedupe onto the seeded entities):

3.3 clients

clients

GRAG side: one workspace client-<slug>-<shortid> per client, provisioned per plan 3.1 and tracked in grag_refs (knoll_type='client', grag_type='workspace').

contacts

3.4 analyses

analyses

Deliberately not stored: questionnaire_progress / checklist_progress (derive from questionnaire_answers vs. the pinned definition and from checklist_items statuses), expert_report_id (derive: latest expert_reports version per §6), pipeline state (lives in ai_runs).

GRAG side: one KB kb-analysis-<shortid> (= voyager collection) in the client workspace; ⚠ no GRAG project layer (plan 0.1/3.1). Tracked in grag_refs.

milestones (⚠ review addition, plan 2.1)

contracts (⚠ review addition — status tracking IS in scope; e-signature is not)

analysis_team (⚠ review addition)

3.5 Questionnaire

questionnaire_versions

Versioning strategy: versions are immutable once referenced by any analysis. A changed questionnaire = new row with version + 1; running analyses keep their pinned version (analyses.questionnaire_version_id), new analyses pick the latest. v1 seed = the 16-chapter definition from lib/mock-data/questionnaire.ts (verified: 16 chapters; sourced from Anlage C.I.1 — Fragenkatalog Analyzer/Kundenfragebogen, 11 main chapters — plus Anlage C.I.2 — Fragebogen IT / IST-Aufnahme). TODO-VERIFY: diff the mock definition against Anlage C.I.1 (raw/Anlage_C.I.1_Kundenfragebogen.md) + C.I.2 (raw/Anlage_C.I.2_Ergaenzung_Fragebogen_IT.md) in Knoll-Analyzer-Docs/ before sealing version 1 — ⚠ NOT Anlage A.I.2.2, which is the KIU initial assessment (industry assessment/opportunities/market participants/project proposals), a different artifact.

questionnaire_answers (autosave-friendly, plan 6.2)

questionnaire_submissions (submit + re-submit anchor, plan 4.5)

On submit: render answers_snapshot to markdown → ingest into the analysis-KB → run the deterministic KG upserts (plan 3.6). The resulting GRAG document id goes to grag_refs (knoll_type='questionnaire_submission'). ⚠ Re-submit (plan 4.5): new row, re-render, POST /workspaces/api/v1/documents/{oldId}/supersede the previous doc, re-run the idempotent KG upserts; the old grag_refs row flips to sync_status='superseded'.

3.6 Checklist (C.I.3, 46 items)

checklist_template (seeded template)

TODO-VERIFY: diff the 46 mock items against the original Anlage C.I.3 in Knoll-Analyzer-Docs/ before freezing the seed ids.

checklist_items (per-analysis status)

The deterministic scoring rule "no business plan → MarketPosition ≤ 1.5" is code, not data (plan 4.3 / 5.3 step 4): it reads the business-plan item's status and caps the market-position score before persisting to report_lever_scores.

3.7 Expert reports (versioned)

expert_reports

report_lever_scores

recommendations

Expert report versioning strategy (plan 2.1):

1. Versions are immutable. Every pipeline run (plan 5.3) writes a new expert_reports row with version + 1 plus its child rows; nothing is edited in place after approval.
2. Approval = Partner sets status='Final' + approved_by/_at. The current expert report of an analysis is the highest-version row with status='Final' (UI falls back to the highest Provisional draft when none is Final).
3. Corrections after approval → new version (re-run or manual edit path); the old Final stays for audit.
4. GRAG projections follow the version: the rendered PDF is ingested into the analysis-KB, and a new approval supersedes the previous expert-report document there (POST /workspaces/api/v1/documents/{id}/supersede, plan 4.4); KG facts (SCORES, RECOMMENDS edges) are re-upserted idempotently with analysis_id + version in the edge properties (plan 3.6).
5. The prototype's ExpertReport.pipeline (PipelineStep[]) is not stored — the pipeline panel (plan 6.4) derives it from ai_runs (§3.12).

3.8 service providers

service_providers

service_provider_ratings (rating matrix D.III.2, scale 1–6, 6 = best)

Ranking is deterministic in code (plan 5.4); the LLM only drafts the rationale text. RESOLVED (was TODO-VERIFY; read from the Anlage D.III.2 xlsx): the 0.35/0.35/0.25 weighting belongs to the "Matrix zur Qualitätsbewertung" only — it rolls the three quality sub-criteria (error frequency 0.35, functionality 0.35, customer rating 0.25) into the single main criterion "Qualität Dienstleistung". The remaining main criteria (number of work samples — an absolute count on service_providers.work_samples, not a 1–6 grade — deadline compliance, price positioning, reference potential, negotiation position, capacities) carry no weighting in the source. The cross-criteria aggregation to overall_grade is therefore undefined in D.III.2 and is an explicit Knoll design decision — document the chosen formula in code and here before implementing.

3.9 projects (Project Generator)

projects

internal_total is derived (sum of calculation_line_items.amount) — computed in code (plan 5.5: "amounts computed in code"), not stored.

project_lever (m:n — Project.lever is an array in types.ts)

project_service_provider (m:n — Project.serviceProviderIds)

calculation_line_items

3.10 Acquisition: leads

The acquisition chat transcript itself lives in GRAG (conversation on kb-methodology, plan 5.1a) — linked via grag_refs (knoll_type='lead', grag_type='conversation'). ag-01 lead research and ag-03 outreach are explicitly deferred (plan 5.8) — no tables for them in v1.

3.11 activities (dashboard feed + weekly digest source, plan 2.7)

3.12 AI: ai_agents and ai_runs

ai_agents (registry — feeds the /ai-agents pages, plan 2.1/6.1)

last_run / total_runs from the prototype are derived from ai_runs (⚠ review: MAX(started_at) / COUNT(*) per agent_id) — not stored.

ai_runs (LLM state machine, ⚠ nullable analysis_id + agent_id per review)

One row per single-shot LLM/gateway run the Knoll backend orchestrates (expert-report pipeline steps, lead scoring, checklist classification, project-brief drafting). Chat turns through the BFF (plan D3) are not ai_runs — GRAG persists those conversations itself.

Well-known step values (extensible; keep in one TS constant):

State machine:

queued ──► running ──► succeeded                    (ungated runs: 4.3, 5.1a, 5.4, 5.5)
              │
              ├──────► awaiting_review ──► approved (gated pipeline steps, plan 5.3:
              │              │                       Partner review between steps)
              │              └──────────► rejected  (→ new run row, attempt reset)
              ├──────► failed                       (gateway error / validation retries
              │                                      exhausted per plan 1.14)
              └──────► cancelled                    (user/operator abort)

Transitions are Knoll-backend-only; approved/rejected additionally require users.role = 'Partner' and write audit_log. A rejected or failed run is never mutated back to queued — retry = new row (same step, attempt restarts; the old row keeps the failure evidence).

3.13 Settings & notifications

notification_preferences (plan 2.1/2.7, Settings tab 3)

Absence of a row = prototype default: questionnaire/expert_report/kyc/milestone true, summary (weekly Monday digest) false. The email layer (plan 2.7) reads this table before every send.

knoll_ai_settings (plan 2.1, Settings AI tab — consumed by 5.3)

The "Human-in-the-loop approval" toggle from the prototype is deliberately not a column — it is QMS-mandatory and hard-coded in the 5.3 pipeline ("kann nicht deaktiviert werden"). GRAG-side tenant settings (chat.default_model, chunking.default_*, …) stay in GRAG's settings store and are read through GET/PUT $GRAG_URL/workspaces/api/v1/settings/{key} (plan 1.7/6.6) — not duplicated here.

3.14 files — file-store metadata (plan 2.4)

GRAG keeps no binaries. Every original upload is persisted to the Knoll file store before it is forwarded to the BFF upload lane (plan 4.1), and every generated PDF (expert report, offer) lands here too. This table is the metadata index; the bytes live in an S3-compatible bucket (or Postgres bytea at pilot scale, D8).

Storage-key convention (deterministic, prefix-deletable):

client/{client_id}/analysis/{analysis_id}/{file_id}     # analysis-scoped files
client/{client_id}/{file_id}                             # client-scoped, no analysis
firm/{firm_id}/{file_id}                                 # firm-level files

Never embed filename in the key (umlauts, collisions) — it lives only in this table. The per-client/per-analysis prefixes make GDPR deletion a prefix delete + row hard-delete + audit_log entry (12-gdpr-compliance.md, plan 7.5). Invariant checked by the sync job: files.grag_document_id equals the grag_id of the single live grag_refs row for this file, or both are NULL.

3.15 grag_refs — the Knoll ↔ GRAG id ledger

One row per GRAG resource that a Knoll entity owns. This table is the normative grag_refs contract — column names, knoll_type value list, grag_sync_status lifecycle and uniqueness as defined below. Id naming conventions remain authoritative in 03-id-conventions.md, but every other restatement of this table is superseded by this section and must reference it verbatim: 03 §5's variant (knoll_type/grag_kind/ payload_sha256, creating/ready/deleting states, job/schema kinds), 10-document-pipeline.md §2/§6's ingest statuses and 11-resilience-and-errors.md §5.3's status list. Two consequences: ingest jobIds are recorded in grag_job_id on the owning row (diagnostic only) — never as separate grag_refs rows; and GRAG-side document ingest substates (converting, indexing, indexed, …) live in the GRAG document inventory and are read live per §6 — never stored in sync_status. Written by the provisioning module (plan 3.1), the upload lane (plan 4.1), chat setup (plan 5.2) and the KG seeder (plan 3.5).

ID conventions recorded here (summary — 03-id-conventions.md is authoritative; all workspaces-plane ids must match ^[a-z0-9][a-z0-9-]{0,62}$, verified at workspaces/src/workspaces/schemas.py:39):

Idempotency: deterministic client-chosen ids + treat HTTP 409 as already-provisioned. ⚠ There is no Idempotency-Key support anywhere in the workspaces service (review-verified; the mention in workspaces/CLAUDE.md has no implementation) — do not send the header (plan 3.1).

Example — provisioning a KB-chat conversation (plan 5.2) and recording it:

curl -sS -X POST "$GRAG_URL/workspaces/api/v1/conversations" \
  -H "Authorization: Bearer $GRAG_API_KEY" \
  -H "X-Tenant-ID: $GRAG_TENANT" \
  -H "Content-Type: application/json" \
  -d '{
    "id": "conv-file-7f3k2-0001",
    "kb_id": "kb-analysis-7f3k2",
    "workspace_id": "client-hartmann-a1b2",
    "title": "Frag die Akte – Hartmann Maschinenbau"
  }'
# 200 → INSERT INTO grag_refs (knoll_type, knoll_id, grag_type, grag_tenant_id,
#          grag_workspace_id, grag_kb_id, grag_id, sync_status, last_synced_at)
#   VALUES ('analysis_chat', '<analysis-uuid>', 'conversation', 'knoll',
#           'client-hartmann-a1b2', 'kb-analysis-7f3k2', 'conv-file-7f3k2-0001',
#           'synced', now());
# 409 → already provisioned: verify the existing grag_refs row, do not error.

(Dev examples use -H "X-Tenant-ID: knoll-dev".)

3.16 audit_log

Append-only. No FKs — rows must survive entity deletion (accountability after GDPR deletion); personal data inside details must follow 12-gdpr-compliance.md (pseudonymize/minimize).

Minimum audited events (QMS + plan 7.5/7.7): every expert-report approval/rejection, every fee/fee-tier change, role changes, GDPR data export/deletion executions, GRAG provisioning/de-provisioning, deterministic KG writes (plan 3.6), file deletions.

4. Relations overview

firms ─┬─ users ──┬─ notification_preferences
       │          └─ (approvals / reviews / uploads via FKs)
       ├─ fee_tiers
       ├─ knoll_ai_settings (1:1)
       └─ clients ─┬─ contacts
                   ├─ files (also ← analyses/checklist_items/expert_reports)
                   └─ analyses ─┬─ milestones
                                ├─ contracts ──────────────► files
                                ├─ analysis_team ───────────► users
                                ├─ questionnaire_answers     (version via
                                ├─ questionnaire_submissions  analyses.questionnaire_version_id
                                │                            → questionnaire_versions)
                                ├─ checklist_items ────────► checklist_template, files
                                ├─ expert_reports ─┬─ report_lever_scores ─► success_levers
                                │                 ├─ recommendations ─────► success_levers
                                │                 └─ pdf ──────────────────► files
                                ├─ projects ─┬─ calculation_line_items
                                │            ├─ project_lever ──────────► success_levers
                                │            ├─ project_service_provider ─► service_providers
                                │            └─ recommendation_id ───────► recommendations
                                └─ ai_runs (nullable analysis_id/agent_id/expert_report_id)
service_providers ── service_provider_ratings (← users, ← projects)
leads ──► ai_runs, clients (conversion)
ai_agents ──► ai_runs
activities ──► analyses?, clients?, users?
grag_refs (knoll_type + knoll_id → any owning row; no hard FK by design)
audit_log (no FKs, append-only)

grag_refs.knoll_id is deliberately a soft reference (text, no FK): it must outlive the owning row long enough for the GRAG-side purge fan-out (plan 3.2/7.5) to complete, then the row flips to sync_status='deleted' and may be hard-deleted after the retention window.

5. Indexes worth defining

Beyond PKs and the UNIQUE constraints listed above (Drizzle: define in the table builders):

6. What is deliberately NOT stored here (GRAG-owned) — and the cache-only rule

Cache-only rule. Knoll MAY cache GRAG reads (in-memory/Redis, short TTL) for UI latency, but: (1) no GRAG payload is ever the basis of a durable business-state write — the only durable Knoll-side copies are ids in grag_refs, the files.grag_document_id mirror, and binaries in the Knoll file store; (2) any state shown as authoritative (document status, conversation history, spend) is re-fetched, not replayed from cache; (3) staleness is resolved by re-fetch + the idempotent reconcile job (plan 3.6), never by writing cached values back.

Example of the canonical cache-only read (document status for the checklist tab, plan 6.3):

curl -sS "$GRAG_URL/workspaces/api/v1/kbs/kb-analysis-7f3k2/documents" \
  -H "Authorization: Bearer $GRAG_API_KEY" \
  -H "X-Tenant-ID: $GRAG_TENANT"
# → {"items":[{"id":"…","name":"Jahresabschluss-2025.pdf","status":"indexed", …}]}
# Join items[].id against grag_refs.grag_id / files.grag_document_id for display;
# do NOT persist the payload.

Also NOT here: end-user identity on the GRAG side (GRAG has no user model — plan resource-mapping table); the X-Pipeline-Id ledger rows themselves (ledger is authoritative, ai_runs.cost_usd is a backfilled convenience copy); the Methodenhandbuch corpus (kb-methodology, plan 3.3 — Knoll stores only its grag_refs row).

7. Open TODO-VERIFY items

• RESOLVED (was TODO-VERIFY): rating matrix D.III.2 — the 0.35/0.35/0.25 weighting applies only to the quality matrix (error frequency 0.35 / functionality 0.35 / customer rating 0.25 → main criterion "Qualität Dienstleistung"); the other main criteria carry no weighting in the xlsx. Remaining OPEN DECISION (not a verify item): the cross-criteria aggregation formula for service_providers.overall_grade (§3.8).
• TODO-VERIFY: checklist C.I.3 — the mock seed has exactly 46 items (groups A, B.1–B.11); diff wording/grouping against the original Anlage before freezing checklist_template ids.
• TODO-VERIFY: questionnaire v1 — diff the 16-chapter mock definition (lib/mock-data/questionnaire.ts) against Anlage C.I.1 + C.I.2 (raw/Anlage_C.I.1_Kundenfragebogen.md, raw/Anlage_C.I.2_Ergaenzung_Fragebogen_IT.md) before sealing questionnaire_versions version 1 (⚠ not A.I.2.2 — that is the KIU initial assessment, a different artifact).
• TODO-VERIFY: fee-tier semantics — prototype shows revenue-keyed tiers (10–50 → 15.000 €, 50–100 → 30.000 €, >100 → individuell); confirm against the pricing model whether employees/balance_sheet_total (HGB class) co-determine the tier.