Data model

The core entities, enums, and relationships behind the Knowledge & Advisor Core.

The schema is defined with Drizzle ORM in packages/database/drizzle/schema/ and split by concern (auth-org, knowledge, conversation, usage, audit, policy, billing, vector, reporting, kpi-reference-library, feature-flags, ai-model-config, enums, and others). All identifiers are CUIDs.

Entity map

organization ─┬─< member >── user
              ├─< invitation
              ├─< knowledgeDoc ─┬─< kbChunk (embedding)
              │                 └─< ingestionJob
              ├─< conversation ──< message
              ├─< policyGapReport
              ├─< aiUsageLog
              ├─< usageCounter
              ├─< auditLog
              │
              ├─< lp ──< template ──< templateVersion ─┬─< templateParseJob
              │                                        └─< templateFieldBinding >── kpi
              ├─< kpi ─┬─< kpiMapping
              │        ├─< kpiMethodologyHistory
              │        ├─< kpiTemplateSource
              │        └─< kpiCalculation
              ├─< investee ──< submission ─┬─< dqcRun ──< dqcFindingResolution
              │                            └─< submissionCorrection
              ├─< gpSubmission
              ├─< report
              └─< cycleAutomationRun ──< cycleAutomationEvent

Tenancy & identity

`organization`

The tenant. Holds plan tier and lifecycle state.

Column	Type	Notes
`id`	text	Primary key.
`name`, `slug`	text	`slug` is unique and used in workspace URLs.
`plan`	`OrganizationPlan`	Plan tier. Default `full`.
`suspendedAt`	timestamp	Non-null means the tenant is suspended and workspace access is blocked.
`legalName`, `description`, `logo`	text	Fund profile fields.

`user`, `member`, `session`

Table	Key columns	Notes
`user`	`email` (unique), `role`, `lastActiveOrganizationId`	`role = 'admin'` marks a platform operator; otherwise null.
`member`	`organizationId`, `userId`, `role`	Organization-level role: `member`, `admin`, or `owner`.
`session`	`userId`, `activeOrganizationId`, `impersonatedBy`	`activeOrganizationId` scopes the session to a tenant; `impersonatedBy` marks an operator support session.
`invitation`	`organizationId`, `email`, `status`	Pending invitations; signup is invitation-only.

Knowledge

`knowledgeDoc`

One row per uploaded document, global or organization-scoped.

Column	Type	Notes
`id`	text	Primary key.
`organizationId`	text	Null when `tenantScope = 'global'`.
`tenantScope`	`TenantScope`	`global` or `org`. A check constraint ties this to `organizationId`.
`source`, `title`, `category`	text	Document metadata.
`r2Key`	text	Object-storage key for the original file.
`status`	`KnowledgeDocStatus`	`pending`, `indexed`, or `failed`.
`mimeType`, `byteSize`	—	File details.
`supersededAt`	timestamp	Set when a newer version replaces this document (latest-wins).

`kbChunk`

The embedded, searchable units of a document.

Column	Type	Notes
`docId`	text	Parent document.
`organizationId`, `tenantScope`, `fundId`	—	Carried from the document for scoped retrieval.
`content`	text	Chunk text.
`chunkIndex`, `tokenCount`	integer	Position and approximate token count.
`embedding`	`vector(1536)`	The embedding. Indexed with an HNSW index using cosine distance.

`ingestionJob`

The state machine that drives a document to indexed. See the ingestion pipeline for the full lifecycle.

Column	Type	Notes
`knowledgeDocId`	text	Document being processed.
`stage`	`IngestionJobStage`	Current stage (see enums below).
`attempts`, `maxAttempts`	integer	Retry accounting; `maxAttempts` defaults to 5.
`nextRunAt`	timestamp	Back-off schedule for the next retry.
`lastError`	text	Most recent failure message.

Conversations & advisor

Table	Key columns	Notes
`conversation`	`organizationId`, `userId`, `channel`, `title`, `retentionExpiresAt`	One thread; `channel` is `web` or `whatsapp`.
`message`	`conversationId`, `role`, `content`, `citations`, `refused`, `model`, `tokensIn`, `tokensOut`	One turn. `citations` is JSON; `refused` flags out-of-corpus answers.
`policyGapReport`	`organizationId`, `knowledgeDocId`, `status`, `gaps`, `suggestions`	Gap analysis for a policy document.

A partial index on message (assistant messages where refused = true) powers the operator Knowledge Gaps view.

LP Reporting

Defined in packages/database/drizzle/schema/reporting.ts. Every table carries an organizationId, and same-fund integrity is enforced at database level.

Set-up

Table	Key columns	Notes
`lp`	`organizationId`, `name`	An LP the fund reports to.
`template`	`lpId`, `format`, `status`, reporting frequency	An LP's reporting template. `status`: `uploaded` → `parsing` → `parsed` / `failed`.
`templateVersion`	`templateId`, version metadata	One row per uploaded version, so versions can be compared.
`templateParseJob`	`templateVersionId`, stage, error	The parse/extraction job behind a version.
`kpi`	`organizationId`, `name`, `unit`, `frequency`, `theme`, `level`, `aggregation`, `referenceCanonicalName`, `referenceLinkSource`	The fund's dictionary. `name` is unique per fund, case-insensitively.
`kpiMapping`	`kpiId`, `standard`, `alignment`	Standards tagging (`direct` / `partial` / `proxy`).
`templateFieldBinding`	`templateVersionId`, `kpiId`, cell reference, confirmed	One KPI per template cell. Suggestions are unconfirmed rows.
`kpiMethodologyHistory`	`kpiId`, prior values	Change history for methodology text.
`kpiTemplateSource`	`kpiId`, `templateId`	Provenance — which templates a KPI came from, so delete can be selective.
`kpiCalculation`	`kpiId`, expression	Derived-KPI expressions.

Data collection

Table	Key columns	Notes
`investee`	`organizationId`, `lpId`, `contactEmail`, active flag	A portfolio company. `lpId` links it to the LP it reports to.
`submission`	`investeeId`, `period`, `status`, `source`, `data`	A portfolio-company submission. `period` is mandatory.
`gpSubmission`	`organizationId`, `period`, `data`	Fund-level figures — no LP dimension.
`dqcRun`	subject type (`investee` / `gp`), findings	One quality-check run and its findings.
`dqcFindingResolution`	`dqcRunId`, finding, reason, actor	Per-finding acknowledgement, tied to the exact run.
`submissionCorrection`	`submissionId`, original value, corrected value, actor, reason	Append-only correction trail.

Output

Table	Key columns	Notes
`report`	`lpId`, `period`, `status`, `narrative`, `valuesSnapshot`	`valuesSnapshot` freezes the figures and the cells they fill.
`cycleAutomationRun`	`organizationId`, `period`	Atomic claim — stops two automatic runs on the same fund and period.
`cycleAutomationEvent`	`step`, `status`	Append-only log per step attempt.

The KPI reference library

packages/database/drizzle/schema/kpi-reference-library.ts holds Nia's master KPI list: canonical names and aliases, units, methodology prose (clear definition, calculation method, numerator, denominator), and aggregation rules including weighted-average denominators and unsafe-to-aggregate markers. It is platform-wide, not tenant-scoped, and funds import from or link to it.

Usage & audit

Table	Key columns	Notes
`aiUsageLog`	`organizationId`, `userId`, `channel`, `kind`, `model`, `tokensIn`, `tokensOut`	Immutable per-run log of AI spend. `kind` is e.g. `advisor`, `gap_review`, `embed`.
`usageCounter`	`(organizationId, fundId, period)`, `runsUsed`, `runsCap`	Rolling monthly counter (`period` is `YYYY-MM`). `runsCap = 0` means "use the plan default".
`auditLog`	`organizationId`, `actorUserId`, `action`, `targetType`, `targetId`, `metadata`	Records operator actions like tier changes, suspensions, and support sessions.

Enums

Defined in packages/database/drizzle/schema/enums.ts:

Enum	Values
`OrganizationPlan`	`bot_only`, `policy_hub`, `full`
`TenantScope`	`global`, `org`
`KnowledgeDocStatus`	`pending`, `indexed`, `failed`
`IngestionJobStage`	`queued`, `parsing`, `chunking`, `embedding`, `indexing`, `completed`, `failed`, `dead_letter`
`ConversationChannel`	`web`, `whatsapp`
`MessageRole`	`user`, `assistant`, `system`
`PolicyGapReportStatus`	`pending`, `completed`, `failed`
`TemplateFormat`	`xlsx`, `docx`, `pdf`, `csv`, `image`
`TemplateStatus`	`uploaded`, `parsing`, `parsed`, `failed`
`KpiTheme`	`environmental`, `social`, `governance`, `impact`, `financial`
`KpiFrequency`	`quarterly`, `half`, `annual`, `monthly`
`KpiLevel`	`investee`, `gp`, `fund`
`KpiAggregation`	`period_end`, `sum`, `average`, `none`
`KpiStandard`	`iris_plus`, `two_x`, `gri`, `other`, `ifc_ps`, `hipso`
`MappingAlignment`	`direct`, `partial`, `proxy`
`SubmissionSource`	`excel`, `form`, `api`
`SubmissionStatus`	`draft`, `reviewing`, `approved`, `rejected`
`DqcSubjectType`	`investee`, `gp`
`LpReportStatus`	`pending`, `generating`, `completed`, `failed`
`ReportExportFormat`	`xlsx`, `pdf`, `docx`
`CycleAutomationStep`	`aggregation`, `generation`, `period_open`
`CycleAutomationStatus`	`completed`, `failed`, `skipped_cap`, `skipped_waiting`, `skipped_complete`
`FeatureFlagKey`	`advisor_hub`, `advisor_guidance`, `lp_reporting`
`AiTaskKind`	`extract`, `dqc`, `advisor`, `gap_review`, `narrative`, `embed`

Postgres enums are append-only in this schema. New values are ADDed only, never reordered or removed.

Vector storage

Embeddings live in kbChunk.embedding as a pgvector column with a fixed dimension (default 1536, configurable via EMBEDDING_DIMENSIONS). An HNSW index (vector_cosine_ops) provides approximate-nearest-neighbour search; queries set hnsw.ef_search per request to tune the recall/latency trade-off. The stored dimension must match the embedding model's output. Changing it requires a migration and re-embedding.

Data model

On this page