Schema Design Overview

Trovella uses a single PostgreSQL 18 database with a shared-schema, multi-tenant architecture. Every tenant's data lives in the same set of tables, isolated by Row-Level Security (RLS) policies that are defined alongside the schema in Drizzle ORM. The schema is the source of truth for both table structure and access control.

Guiding Principles

RLS is the security boundary, not application code. Every tenant-scoped table has four RLS policies (SELECT, INSERT, UPDATE, DELETE) generated by a single helper function. A missing WHERE tenant_id = ? in application code does not leak data because the database rejects it independently.

Vertical slices by domain, not by layer. Schema files are organized by feature area (auth.ts, research.ts, ai.ts, search.ts), not by database concept. Each file contains tables, enums, and Drizzle relations for its domain.

Progressive complexity. Tables start with the minimum viable columns. JSONB is used for semi-structured data that varies by context (execution reports, tool call metadata, extraction schemas) while structured columns are used for data that is queried, filtered, or joined.

AI-agent-friendly. Strict TypeScript types on every column, consistent naming patterns, and a single way to do tenant-scoped queries (withTenantContext()) make it hard for agents to introduce security bugs.

Schema File Layout

All schema definitions live in packages/db/src/schema/:

File	Domain	Tables
base.ts	Shared	tenantColumns, baseColumns, tenantPolicies() helper
roles.ts	Shared	authenticated and anonymous PostgreSQL roles
enums.ts	Shared	Cross-cutting enums (tenantTypeEnum)
auth.ts	Identity	user, session, account, verification, organization, member, invitation
ai.ts	AI	aiModel, aiModelPricing, aiBatch, aiUsage, aiCallDetails
research.ts	Research	researchPlan, planStep, planBranchingCondition, planAuditLog, researchArtifact, extractionResult, skillExecution, mcpToolCallLog, researchOutput, researchFeedback
search.ts	Search	documentChunk (with pgvector halfvec column)
mcp.ts	MCP	personalAccessToken
index.ts	All	Re-exports everything

Every table in every schema file is re-exported through index.ts, which is the single import point used by the Drizzle config and application code.

Two Categories of Tables

Tables fall into one of two categories based on their data ownership:

Tenant-scoped tables have an organization_id column, RLS policies, and .enableRLS(). All queries against these tables must go through withTenantContext(). Examples: member, researchPlan, aiUsage, documentChunk.

Global tables have no organization_id column and no RLS. They are either reference data (readable by all tenants) or user-scoped (filtered by application code, not RLS). Examples: user, organization, aiModel, personalAccessToken.

See Tenant Scoping for the full RLS pattern and Reference Data for the global table conventions.

Key Design Decisions

Several non-obvious conventions are worth understanding upfront:

• TEXT primary keys, not UUID. Better Auth generates text-format IDs. Using TEXT everywhere avoids ::uuid casting at join boundaries. See Table Conventions.
• organization_id column name, not tenant_id. Matches Better Auth's naming directly. See Tenant Scoping.
• JSONB for semi-structured data. Columns like resultSummary, stepExecutionReport, and requestMetadata use typed JSONB (.$type<T>()) for data whose shape varies by context. See Table Conventions.
• halfvec(1536) for embeddings. Half-precision vectors save 50% storage with negligible recall loss. See Table Conventions.
• Drizzle relations are separate from foreign keys. Foreign keys enforce referential integrity at the database level. Drizzle relations() enable the query API's nested selects. Both are defined, and they must stay in sync. See Relationships.

Related Topics

• See Identity & Access -- Tenant Isolation for the full RLS enforcement architecture, including withTenantContext() internals, the authenticated role, and the bare db restriction.
• See Delivery -- Migrations for the migration workflow (generate, review, migrate) and CI verification.
• See Data & Storage -- Query Patterns for how tenant-scoped queries use the transaction handle.