Transactional Schema
Schema Ownership
OpenTier uses a single PostgreSQL database shared between both services. All schema migrations are centralized in a single location:
| Migration Tool | Location | Tables |
|---|---|---|
sqlx-cli | server/db/migrations/ | users, sessions, accounts, verification_tokens, password_reset_tokens, conversations, messages, documents, document_chunks, ingestion_jobs, chat_messages, user_memories, knowledge_submissions |
Migrations are applied automatically by the API container during its startup sequence before the server boots. The API container uses sqlx migrate run underneath to apply standard {timestamp}_{name}.up.sql / .down.sql files directly.
Entity Relationship Diagram
Table Details
users
| Column | Type | Notes |
|---|---|---|
id | UUID | gen_random_uuid() |
email | VARCHAR(255) | Unique, indexed |
email_verified | BOOLEAN | Default false; OAuth users set true immediately |
password_hash | VARCHAR(255) | bcrypt; NULL for OAuth-only accounts |
role | user_role ENUM | user, contributor, or admin |
deleted_at | TIMESTAMPTZ | Soft-delete; NULL = active |
Indexes: idx_users_email, idx_users_username, idx_users_role, idx_users_created_at, idx_users_deleted_at (partial, WHERE deleted_at IS NULL)
Trigger: update_updated_at_column() updates updated_at on every UPDATE
sessions
| Column | Type | Notes |
|---|---|---|
session_token | VARCHAR(255) | 64-char random alphanumeric; unique |
expires_at | TIMESTAMPTZ | NOW() + 7 days |
role | user_role ENUM | Duplicated from users.role at session creation time |
ip_address | INET | Client IP (IPv4 or IPv6) |
Key design note: role in sessions is a snapshot taken at login. Role changes do not propagate to active sessions.
accounts (OAuth)
Enforces UNIQUE(provider, provider_account_id) — one account per provider per external identity. One user can link multiple providers.
conversations
| Column | Type | Notes |
|---|---|---|
id | UUID | No DEFAULT — caller provides UUID |
user_id | VARCHAR(255) | Not a foreign key to users.id — loose coupling |
Critical: user_id in conversations is a VARCHAR(255), not a UUID FK referencing users. This allows the Python Intelligence service to create or reference conversations without strict FK integrity — enabling the shared-table architecture without Rust owning all writes.
messages vs chat_messages
Two tables store message content. This is the most architecturally significant split in the schema:
| Table | Written by | Read by | Contains |
|---|---|---|---|
messages | Rust API | Rust API | Minimal message rows (role, content, metadata); no sources |
chat_messages | Python Intelligence | Both services | Full AI messages with sources (JSONB), parent_id for branching |
In practice the Rust API uses chat_messages (via get_conversation) and messages appears to be vestigial or used for audit logging. This creates a dual-table situation that must be managed carefully during schema changes.
knowledge_submissions
Stores contributor-submitted resources pending admin review.
| Column | Type | Notes |
|---|---|---|
contributor_id | UUID FK | References users.id |
status | VARCHAR(20) | pending, approved, rejected |
metadata | JSONB | Free-form submission metadata |
reviewed_by | UUID FK | Admin reviewer (users.id) |
Indexes: idx_knowledge_submissions_status, idx_knowledge_submissions_contributor, idx_knowledge_submissions_created_at.
Enums
CREATE TYPE user_role AS ENUM ('user', 'contributor', 'admin');
CREATE TYPE message_role AS ENUM ('user', 'assistant', 'system');user_role is used in both users.role and sessions.role. In chat_messages, role is VARCHAR(50) (written by Python) — not the PostgreSQL enum type.