Transactional Schema (PostgreSQL 18)
Overview & Storage Boundaries
OpenTier v1.1.0 cleanly partitions data by workload:
- PostgreSQL 18 is the ACID system of record for accounts, sessions, conversations, metadata, credit ledgers, and provider catalog configurations.
- Qdrant is the dedicated vector system of record for dense neural vectors (3072d) and sparse BM25 token indices (
knowledge_chunkscollection).
All PostgreSQL migrations are centralized in server/api/migrations/ and applied automatically on API gateway startup via SQLx.
| Migration Tool | Location | Primary Tables |
|---|---|---|
sqlx-cli | server/api/migrations/ | users, sessions, accounts, conversations, chat_messages, user_memories, documents, ingestion_jobs, model_providers, models, user_credit_balances, credit_transactions, credit_holds, event_outbox |
Entity Relationship Diagram
Core Table Details
1. Identity & Authentication
users
System accounts for standard, contributor, and admin roles.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK, gen_random_uuid() | Unique user identifier. |
email | VARCHAR(255) | UNIQUE, NOT NULL | User email address. |
email_verified | BOOLEAN | NOT NULL, DEFAULT false | Verification status (OAuth auto-verifies). |
password_hash | VARCHAR(255) | NULL | Bcrypt hash (null for OAuth-only users). |
name | VARCHAR(255) | NULL | Display name. |
username | VARCHAR(50) | UNIQUE, NULL | Profile username. |
role | user_role | NOT NULL, DEFAULT ‘user’ | Enum: user, contributor, admin. |
deleted_at | TIMESTAMPTZ | NULL | Soft-deletion timestamp. |
sessions
Opaque session tokens for stateless REST & SSE authentication.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK | Session ID. |
user_id | UUID | FK users(id) ON DELETE CASCADE | Target user. |
session_token | VARCHAR(255) | UNIQUE, NOT NULL | 64-character random cryptographically secure token. |
expires_at | TIMESTAMPTZ | NOT NULL | Expiration timestamp (default: 30 days). |
role | user_role | NOT NULL | Cached user role snapshot for O(1) single-lookup auth. |
ip_address | INET | NULL | Client IP address. |
user_agent | TEXT | NULL | Client user agent string. |
accounts
External OAuth 2.0 identity bindings (Google, GitHub, Microsoft, Discord).
AI Model Catalog & Routing Tables
v1.1.0 replaces hardcoded model environment variables with dynamic, database-backed catalog configurations.
model_providers
Configured LLM and embedding providers. API keys are sealed at rest using AES-256-GCM.
| Column | Type | Description |
|---|---|---|
id | UUID PK | Provider identifier. |
name | VARCHAR(100) | Display name (e.g. Google Gemini, OpenAI). |
slug | VARCHAR(50) UNIQUE | Provider slug (google, openai, ollama, custom). |
api_base | VARCHAR(255) | Base URL endpoint override (e.g. for Ollama or vLLM). |
encrypted_api_key | TEXT | AES-256-GCM sealed ciphertext blob (decrypted via ENCRYPTION_MASTER_KEY). |
enabled | BOOLEAN | Global enable/disable toggle. |
config | JSONB | Provider-specific tunings and headers. |
models
Specific models attached to providers with pricing and fallback chains.
| Column | Type | Description |
|---|---|---|
id | UUID PK | Model identifier. |
provider_id | UUID FK | References model_providers(id). |
slug | VARCHAR(100) UNIQUE | Model slug (e.g. gemini-2.5-flash, gpt-4o, text-embedding-3-large). |
model_type | VARCHAR(20) | chat, embedding, or rerank. |
context_window | INTEGER | Token limit for prompt context. |
pricing | JSONB | Cost structure: {"input_cost_per_million": 0.15, "output_cost_per_million": 0.60}. |
is_default | BOOLEAN | Default model for the given model_type. |
fallback_slug | VARCHAR(100) | Slug of secondary model to try if this model is exhausted or errors. |
Credit Ledger & Billing Tables
v1.1.0 introduces an immutable, append-only credit ledger for real-time token metering and usage quotas. Credits are denominated such that **1 credit = 1.00). New users receive an automatic 10.00 credit signup bonus ($0.01 equivalent).
user_credit_balances
Atomic user balance and optimistic lock version tracking.
| Column | Type | Constraints | Description |
|---|---|---|---|
user_id | UUID | PK, FK users(id) | User owning the credit balance. |
balance | NUMERIC(14,4) | NOT NULL, DEFAULT 0, CHECK >= 0 | Spendable credit balance. |
held | NUMERIC(14,4) | NOT NULL, DEFAULT 0 | Currently reserved credits for active streaming calls. |
version | BIGINT | NOT NULL, DEFAULT 0 | Version counter for optimistic concurrency checks. |
credit_transactions (Append-Only Ledger)
Comprehensive audit trail recording every balance adjustment, token consumption, and grant.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | UUID | PK, gen_random_uuid() | Unique transaction ID. |
user_id | UUID | FK users(id) | Debited or credited user. |
delta | NUMERIC(14,4) | NOT NULL | Change in balance (positive for grants, negative for usage). |
balance_after | NUMERIC(14,4) | NOT NULL | Snapshot of balance immediately following transaction. |
reason | VARCHAR(30) | NOT NULL | grant, usage, refund, admin_adjustment, signup_bonus. |
model_id | UUID | FK models(id) | Model consumed during the transaction (if usage). |
tokens_in | INTEGER | DEFAULT 0 | Input prompt tokens billed. |
tokens_out | INTEGER | DEFAULT 0 | Generated completion tokens billed. |
cost_input | NUMERIC(14,6) | DEFAULT 0 | Computed credit cost of input tokens. |
cost_output | NUMERIC(14,6) | DEFAULT 0 | Computed credit cost of output tokens. |
conversation_id | UUID | NULL | Context conversation ID. |
idempotency_key | TEXT | NOT NULL, UNIQUE | Deduplication key preventing double-billing on retries. |
correlation_id | VARCHAR(64) | NULL | Distributed tracing correlation ID. |
credit_holds
Temporary reservations placed prior to streaming responses to guarantee sufficient funds before tokens are generated.
Transactional Outbox (event_outbox)
Guarantees reliable message publishing between PostgreSQL transactions and Redis Streams without dual-write race conditions.
| Column | Type | Description |
|---|---|---|
id | UUID PK | Event ID. |
stream | TEXT | Target Redis stream (e.g. opentier:intel:chat_events). |
event_type | TEXT | Event type descriptor (e.g. billing.charge.v1). |
correlation_id | TEXT | Tracing correlation ID. |
payload | JSONB | Serialized event payload. |
attempts | INTEGER | Delivery attempt count. |
published_at | TIMESTAMPTZ | Timestamp when successfully acknowledged by Redis. |
Chat & Ingestion Tables
conversations: Chat thread metadata, titles, and ownership.chat_messages: Chat messages withsources(JSONB) citations and tree branching viaparent_id.user_memories: Persistent per-user context updated asynchronously by workers.documents: Ingested content catalogs (titles, URLs, status).ingestion_jobs: Status tracking (queued,processing,completed,failed) for the background worker ingestion pipeline.