---
title: Data model
description: 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 | reviewing | verified | suspended` (see the [lifecycle](/architecture/multi-tenancy)). |
| `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 | active | suspended | removed` (see the [membership lifecycle](/architecture/multi-tenancy)). |
| `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](/architecture/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 | disabled`. |
| 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 | active | archived`. |
| `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.

### Relationship overview

```mermaid
erDiagram
    USER ||--o{ ORGANIZATION_MEMBERS : belongs
    ORGANIZATION ||--o{ ORGANIZATION_MEMBERS : has
    ORGANIZATION ||--o{ ROLES : scopes
    ORGANIZATION_MEMBERS ||--o{ MEMBER_ROLES : holds
    ROLES ||--o{ MEMBER_ROLES : granted_to
    ROLES ||--o{ ROLE_PERMISSIONS : grants
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : included_in
    ORGANIZATION ||--o{ ORGANIZATION_APPLICATIONS : enables
    APPLICATIONS ||--o{ ORGANIZATION_APPLICATIONS : enabled_for
    ORGANIZATION ||--o{ PRODUCTS : owns
```