---
type: Reference
title: Database
description: 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.
tags: [database, schema, postgresql, drizzle, migrations]
verified:
  - by: openwiki/0.7.2
    at: 2026-10-14T20:42:32.326Z
---
# 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.