Raypx

The database schema is defined in two files under packages/database/src/schema/:

auth.ts -- Tables managed by Better Auth (user, session, account, verification).
app.ts -- Application tables (profiles, subscriptions, conversations, etc.).

Auth Tables

Defined in packages/database/src/schema/auth.ts:

`user`

Column	Type	Constraints	Description
`id`	`text`	PK	User ID (set by Better Auth).
`name`	`text`	NOT NULL	Display name.
`email`	`text`	NOT NULL, UNIQUE	Email address.
`email_verified`	`boolean`	NOT NULL, default `false`	Whether email is verified.
`image`	`text`	nullable	Avatar URL.
`created_at`	`timestamptz`	NOT NULL, default `now()`	Creation time.
`updated_at`	`timestamptz`	NOT NULL, auto-update	Last update time.

Indexes: idx_user_email on email.

`session`

Column	Type	Constraints	Description
`id`	`uuid`	PK, UUID v7	Session ID.
`token`	`text`	NOT NULL, UNIQUE	Session token.
`expires_at`	`timestamptz`	NOT NULL	Expiration time.
`user_id`	`text`	NOT NULL, FK -> `user.id`, CASCADE	Owner.
`ip_address`	`text`	nullable	Client IP.
`user_agent`	`text`	nullable	Browser user-agent.
`last_active`	`timestamptz`	NOT NULL, default `now()`	Last activity.
`created_at`	`timestamptz`	NOT NULL, default `now()`	Creation time.
`updated_at`	`timestamptz`	NOT NULL, auto-update	Last update time.

Indexes: idx_session_token, idx_session_user_id.

`account`

Column	Type	Constraints	Description
`id`	`uuid`	PK, UUID v7	Account ID.
`account_id`	`text`	NOT NULL	Provider account ID.
`provider_id`	`text`	NOT NULL	Provider name.
`user_id`	`text`	NOT NULL, FK -> `user.id`, CASCADE	Owner.
`access_token`	`text`	nullable	OAuth access token.
`refresh_token`	`text`	nullable	OAuth refresh token.
`id_token`	`text`	nullable	OAuth ID token.
`password`	`text`	nullable	Hashed password.
`scope`	`text`	nullable	OAuth scopes.
`created_at`	`timestamptz`	NOT NULL	Creation time.
`updated_at`	`timestamptz`	NOT NULL, auto-update	Last update time.

Unique index: uidx_account_provider_account on (provider_id, account_id).

`verification`

Column	Type	Constraints	Description
`id`	`uuid`	PK, UUID v7	Verification ID.
`identifier`	`text`	NOT NULL	Email or phone being verified.
`value`	`text`	NOT NULL	Verification token.
`expires_at`	`timestamptz`	NOT NULL	Token expiration.
`created_at`	`timestamptz`	NOT NULL	Creation time.
`updated_at`	`timestamptz`	NOT NULL, auto-update	Last update time.

App Tables

Defined in packages/database/src/schema/app.ts:

`profile`

User settings and preferences.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE, UNIQUE.
`default_model`	`text`	`"mock:raypx-1"`	Default AI model.
`preferred_locale`	`text`	`"en-US"`	Preferred language.
`output_tone`	`text`	`"professional"`	Output style.
`marketing_opt_in`	`boolean`	`false`	Email marketing consent.
`created_at`	`timestamptz`	`now()`	Creation time.
`updated_at`	`timestamptz`	auto-update	Last update time.

`subscription`

Billing subscription state.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`provider`	`text`	`"manual"`	Payment provider.
`plan_tier`	`text`	`"free"`	Plan tier (`free`, `pro`).
`status`	`text`	`"trialing"`	Status (`trialing`, `active`, `past_due`, `cancelled`).
`current_period_start`	`timestamptz`	null	Period start.
`current_period_end`	`timestamptz`	null	Period end.
`cancel_at_period_end`	`boolean`	`false`	Cancellation flag.

`conversation`

AI conversation threads.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`title`	`text`	--	Conversation title.
`task`	`text`	`"summarize"`	Task type.
`status`	`text`	`"active"`	`active` or `archived`.

`message`

Individual messages within a conversation.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`conversation_id`	`uuid`	--	FK -> `conversation.id`, CASCADE.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`role`	`text`	--	`system`, `user`, `assistant`, or `tool`.
`content`	`text`	--	Message body.
`model`	`text`	null	AI model used.

`document`

Uploaded user documents.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`title`	`text`	--	Document title.
`storage_key`	`text`	--	Storage reference key, UNIQUE.
`content_type`	`text`	`"text/plain"`	MIME type.
`size_bytes`	`integer`	`0`	File size in bytes.
`status`	`text`	`"uploaded"`	`uploaded`, `processed`, or `failed`.
`summary`	`text`	null	AI-generated summary.
`metadata`	`jsonb`	`{}`	Arbitrary metadata.

`run`

AI processing runs (linked to conversations or documents).

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`conversation_id`	`uuid`	null	FK -> `conversation.id`, SET NULL.
`document_id`	`uuid`	null	FK -> `document.id`, SET NULL.
`feature`	`text`	`"document-summary"`	Feature name.
`model`	`text`	--	AI model used.
`status`	`text`	`"queued"`	`queued`, `running`, `succeeded`, `failed`, `cancelled`.
`prompt`	`text`	--	Input prompt.
`response_text`	`text`	null	AI response.
`error_message`	`text`	null	Error details.

`artifact`

Output artifacts produced by AI runs.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`run_id`	`uuid`	--	FK -> `run.id`, CASCADE.
`kind`	`text`	`"summary"`	`summary`, `rewrite`, `extraction`, `file`, `report`.
`title`	`text`	--	Artifact title.
`content`	`text`	--	Artifact body.
`metadata`	`jsonb`	`{}`	Arbitrary metadata.

`usage_event`

Usage tracking for billing and analytics.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	--	FK -> `user.id`, CASCADE.
`run_id`	`uuid`	null	FK -> `run.id`, SET NULL.
`metric`	`text`	--	`ai_tokens`, `ai_requests`, `documents`, `storage_bytes`.
`quantity`	`integer`	--	Amount consumed.
`model`	`text`	null	AI model.
`feature`	`text`	`"document-summary"`	Feature name.
`cost_usd`	`numeric(10,4)`	`"0"`	Cost in USD.

`job`

Background job queue entries.

Column	Type	Default	Description
`id`	`uuid`	UUID v7	PK.
`user_id`	`text`	null	FK -> `user.id`, SET NULL.
`run_id`	`uuid`	null	FK -> `run.id`, SET NULL.
`type`	`text`	--	Job type identifier.
`status`	`text`	`"queued"`	`queued`, `running`, `succeeded`, `failed`, `cancelled`.
`attempts`	`integer`	`0`	Retry count.
`payload`	`jsonb`	`{}`	Job input data.
`result`	`jsonb`	null	Job output data.
`error_message`	`text`	null	Error details.
`scheduled_at`	`timestamptz`	`now()`	When to execute.

Conventions

UUID v7 Primary Keys

All app tables use UUID v7 for primary keys. UUID v7 is time-ordered, which provides better index locality than UUID v4:

uuid("id")
  .primaryKey()
  .$defaultFn(() => uuidv7())

The user table uses text for its primary key because Better Auth manages user IDs. All other tables use UUID v7.

Timestamps

All tables include created_at and updated_at columns using timestamptz (timestamp with timezone). The updated_at column auto-updates on every write:

timestamptz("created_at").defaultNow().notNull(),
timestamptz("updated_at")
  .defaultNow()
  .notNull()
  .$onUpdateFn(() => new Date()),

Cascading Deletes

Foreign keys to user.id use onDelete: "cascade". When a user is deleted, all related rows in profiles, conversations, messages, documents, and usage events are automatically removed.

Foreign keys to conversation.id and document.id use onDelete: "set null" for the run table, preserving run history even if the parent resource is deleted.

Text Enums

Enum-like fields are stored as text columns with TypeScript type narrowing:

status: text("status").$type<ConversationStatus>().notNull().default("active")

This avoids PostgreSQL enum types, which are difficult to alter in production.

Schema