Skip to content
Company OS
Esc
↑↓navigate↵open⌘Jpreview
On this page

Database

The database schema design using PostgreSQL via Drizzle ORM, covering tables for tenants, principals, capabilities, tasks, receipts, policies, and authentication, along with migration strategies and table relationships.

Database

The Company OS database schema implements the five primitives (Principal, Capability, Task, Receipt, Policy) using PostgreSQL with Drizzle ORM, extending Better Auth authentication tables to link users to principals. Every table containing tenant-scoped data includes a mandatory tenant_id column and is protected by Row-Level Security (RLS).

Schema Overview

The database schema consists of:

  • Principal-related tables: principal, principal_identity, agent_credential (extending Better Auth’s user, session, account, verification)
  • Primitive tables: capability, task, receipt, policy
  • Migration management: Handled by Drizzle Kit with version-controlled SQL migrations

Tables

Principal Tables

The principal table stores identities with their tenant association and human accountability:

  • id (primary key)
  • tenant_id (foreign key to tenant, mandatory for isolation)
  • class (user|agent|factory)
  • responsible_principal_id (human accountable principal)

The principal_identity table links channel-specific identifiers to principals:

  • id (primary key)
  • principal_id (foreign key to principal)
  • provider (e.g., “better-auth”, “whatsapp”, “teams”)
  • external_id (channel-specific identifier)

The agent_credential table stores credentials for non-human principals:

  • id (primary key)
  • principal_id (foreign key to principal)
  • Additional credential fields (implementation-specific)

Primitive Tables

Capability

Stores invocable function definitions registered by applications:

  • Schema (input/output Zod schemas)
  • Permission requirements
  • Effect class (read/write/delete)
  • Reversibility flag
  • The OS is the sole invoker of capabilities

Task

Encapsulates work intention:

  • tenant_id (context)
  • principal_id (actor)
  • goal (objective)
  • approval_class (determines approval requirements)
  • Correlation ID for linking to receipts

Receipt

Records every atomic action executed by the OS:

  • tenant_id and principal_id (for metering and auditing)
  • correlation_id (links to initiating task)
  • parent_receipt_id (for child receipts from tool calls/model costs)
  • capability invoked
  • side_effect_class (effect classification)
  • target (what was acted upon)
  • Costs: cost_model_cents, cost_infra_cents, tokens_in, tokens_out
  • Observability: langfuse_trace_id
  • outcome (pending | success | failed | denied | uncertain)
  • Timestamps: created_at, settled_at

Policy

Encodes approval logic as data:

  • tenant_id, principal_id, or capability_id scoping
  • Roles, effect classes, goals
  • Limits, validity periods
  • Stop conditions
  • Evaluated by OS before capability invocation

Authentication Tables (Extended Better Auth)

The schema extends Better Auth’s default tables:

  • user (from Better Auth): Extended with principal linking
  • session: User sessions with device/IP metadata
  • account: OAuth/credential accounts linked to users
  • verification: Email/SMS verification challenges

Security and Isolation

Tenant Isolation

Every table containing tenant-scoped data includes:

  • A mandatory tenant_id column
  • Row-Level Security (RLS) policies enforced at the database level
  • A TenantContext Effect service that sets SET LOCAL app.tenant_id on every database connection
  • Queries lacking TenantContext fail at compile time

This ensures application bugs cannot cause cross-tenant data leaks, as RLS acts as a defense-in-depth mechanism.

Principal Identity Resolution

Channel identifiers (WhatsApp number, Teams user ID, Better Auth session) must be explicitly linked to a principal by a tenant administrator. The OS never implicitly creates principals; mismatched channel identities are rejected.

Migrations

Schema migrations are managed by Drizzle Kit:

  • Migration files generated in /packages/db/src/migrations/
  • Generated via bun db:generate command
  • Applied via bun db:migrate command
  • Configuration in drizzle.config.ts specifies PostgreSQL dialect and output directory

Relationships

The schema defines relationships between tables:

  • One principal has many identities (principal_identity)
  • One principal has many agent credentials (agent_credential)
  • One user (Better Auth) links to one principal
  • One principal has many sessions
  • One principal has many accounts
  • One task has many receipts (via correlation ID)
  • One receipt can have many child receipts (via parent_receipt_id)
  • Policies can reference principals, capabilities, or be tenant-scoped

Implementation Notes

The schema is defined in /packages/db/src/schema/auth.ts using Drizzle ORM’s pgTable utility. Relations are defined in the same file using Drizzle’s relations helper. The schema is exported from /packages/db/src/schema/index.ts and consumed by the database client in /packages/db/src/index.ts.

Evidence for the schema design and primitives can be found in the architectural decisions documented in ADR-0001, which specifies the table structures for principal, principal_identity, agent_credential, and the five primitives, along with the enforcement mechanisms for tenant isolation and metering.

Was this page helpful?