Data model

Every table in the database - the public identity schema, the platform schema, the retail vertical schema, and the admin schema.

This page documents every table in the database, grouped by schema, as defined by the migrations under infra/database/migrations. The migrations are the source of truth; the services read rows from these tables.

public schema - Better Auth identity

Created by 0001_identity.sql. Better Auth owns these tables and the platform never queries them directly - it goes through AuthService.

Table	Purpose
`user`	The identity: id (text), name, email (unique), email_verified, image, timestamps.
`session`	Auth sessions: token (unique), expires_at, user_id, ip_address, user_agent.
`account`	OAuth/provider accounts linked to users.
`verification`	Verification tokens (email verification, password reset).

platform schema - the tenant core

Users (0002_platform_users.sql):

Column	Notes
`id`	uuid PK - the platform user id memberships reference.
`identity_id`	text, references `public.user(id)` - the Better Auth identity.
`display_name`, `avatar_url`	Profile fields synced by the request runtime.

The request runtime upserts a row on every request (syncPlatformUser), keeping identity_id unique.

Organizations (0003_organizations.sql, refined by 0006_organization_lifecycle.sql):

Column	Notes
`id`	uuid PK.
`name`	text.
`slug`	text, unique.
`status`	`pending
`verified_at`	timestamptz, set when status becomes `verified`.
`created_at`, `updated_at`	timestamptz.

Organization members (0003, refined by 0006):

Column	Notes
`id`	uuid PK.
`organization_id`	FK to `platform.organizations`, cascade delete.
`user_id`	FK to `platform.users`, cascade delete.
`status`	`invited
`created_at`, `updated_at`	timestamptz.
UNIQUE	`(organization_id, user_id)`.

Indexed on user_id.

Permissions (0004_permissions.sql):

Column	Notes
`id`	uuid PK.
`key`	text, unique - the `application.resource.action` string.
`description`	text, nullable.
`created_at`	timestamptz.

Roles (0004):

Column	Notes
`id`	uuid PK.
`name`	text.
`organization_id`	FK to `platform.organizations`, cascade delete.
UNIQUE	`(name, organization_id)` - roles are organization-scoped.

Role permissions (0004) - join table:

Column	Notes
`role_id`	FK to `platform.roles`, cascade delete.
`permission_id`	FK to `platform.permissions`, cascade delete.
PK	`(role_id, permission_id)`.

Member roles (0004) - join table:

Column	Notes
`member_id`	FK to `platform.organization_members`, cascade delete.
`role_id`	FK to `platform.roles`, cascade delete.
PK	`(member_id, role_id)`.

Applications (0005_applications.sql, extended by 0007_application_registry_metadata.sql):

Column	Notes
`id`	uuid PK.
`name`, `slug`	text; slug unique.
`description`	text, nullable.
`icon`, `color`, `route`, `supports_offline`	Registry metadata added in 0007 (see Application registry).
`created_at`, `updated_at`	timestamptz.

Organization applications (0005) - which orgs can use which apps:

Column	Notes
`organization_id`	FK, cascade delete.
`application_id`	FK, cascade delete.
`status`	`active
PK	`(organization_id, application_id)`.

retail schema - the reference vertical

Products (0008_retail_products.sql):

Column	Notes
`id`	uuid PK.
`organization_id`	FK to `platform.organizations`, cascade delete - the tenant boundary.
`name`	text.
`description`	text, nullable.
`price_cents`	integer >= 0 - money is integer cents, never floats.
`currency`	text, default `'USD'`.
`status`	`draft
`created_at`, `updated_at`	timestamptz.

Indexed on organization_id so tenant-scoped queries stay fast. Every future vertical schema follows this pattern: its own schema, every row carrying organization_id, structurally scoped queries.

admin schema - operator identity

Created by 0009_admin.sql (+ 0009_admin_waitlist_emails.sql). Separate from tenant identity:

Admins:

Column	Notes
`id`	uuid PK.
`name`	text, unique - the login name.
`password_hash`	text - scrypt, `scrypt$salt$hash`.
`display_name`	text.
`created_at`, `updated_at`	timestamptz.

Sessions:

Column	Notes
`id`	uuid PK.
`admin_id`	FK to `admin.admins`.
`token_hash`	text - SHA-256 of the raw token; only the hash is stored.
`expires_at`	timestamptz.

The migration ledger

platform_migrations records every applied migration (id, name, applied timestamp), so re-running db:migrate is a no-op. The migrator runs pending migrations inside one transaction.