Skip to main content

Database overview and operations

This section is the complete reference for Aivory v2.4.7 and the post-release main branch's relational data model: 60 tables, including the current domain-enrollment and passkey tables. The authoritative schema is embedded in the Go binary:

  • SQLite (personal/dev): server/internal/store/schema.sql (1,120 lines, //go:embed, store.go:22-23)
  • PostgreSQL (full edition): server/internal/store/schema_pg.sql (1,048 lines, store.go:25-26)

Both files declare the same 60 tables (identical column names and semantics; only dialect types differ), applied idempotently at startup by store.Migrate(). The final effective schema = embedded CREATE TABLE + additive ALTER TABLE ADD COLUMN statements in store.Migrate (a few columns exist only in ALTERs — e.g. users.group_expires_at, messages.feedback, conversations.workspace_id; they are marked ※ ALTER-added in the text).

Do not reference server/migrations/

server/migrations/0001_init.sql is an early initial snapshot. No runtime code references it and it does not reflect the current structure. The only source of truth is the two schema*.sql files plus the additive migration steps in store.go.

Page navigation​

PageTablesContents
Users & authentication6users, refresh_tokens, login_histories, oauth_providers, oauth_identities, passkeys
Channels, models & usage6channels, models, model_tags, model_skills, usage_logs, usage_stats
Conversations & messages9projects, conversations, messages, message_feedback, user_feedback, conversation_shares, memories, two conversation lease tables
Files & knowledge7files, knowledge_bases, knowledge_base_shares, documents, chunks, vector_points, artifacts
Subscriptions, credits & payments15user_groups, credit/quota ledgers, packages, redeem codes, payment channels/methods/orders/events
Workspaces & members8workspaces, members, invites, policies, per-library permissions, audit log, registration_domains, domain_users
Capabilities & integrations7skills, prompts, private copies user_*, mcp_servers, user_mcp_servers, image_styles
System & audit2settings (global key/value), pending_storage_cleanup (delete-purge queue)

Totals: 6+6+9+7+15+8+7+2 = 60 tables. Aivory has no schema_migrations-style bookkeeping table (see migration mechanism below), so there is nothing else to place.

Data-model overview​

The diagram groups the tables by domain and marks the main foreign-key directions; per-table column detail lives on the pages above.

Aivory database overview: 60 tables grouped by domain with major foreign keys and the storage-engine split between editions

Where data lives in each edition​

Aivory ships a single Go backend binary; the storage engine is chosen at boot by DATABASE_URL / REDIS_URL / QDRANT_URL (see Deployment and environment). No table is personal-edition-only — all 60 tables are created in both editions; only vectors, cache, and the queue differ in engine:

Data categoryPersonal (SQLite)Full (PostgreSQL + Redis + Qdrant)
All 60 relational tablessingle file DATA_DIR/aivory.db, WAL (DATABASE_URL=/app/data/aivory.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000), deploy/docker-compose.personal.yml:28)named volume pgdata of the postgres container (postgres:16-alpine)
RAG vectorsvector_points table (embedded exact cosine search, VECTOR_BACKEND=sqlite pinned)Qdrant (qdrant/qdrant:v1.12.4, volume qdrantdata), one collection per dimension named aivory_c<dim> (server/internal/vector/qdrant.go); vector_points stays empty
Rate-limit counters, request-signature nonces, stop/ban/config pub/sub, SSE replay buffersin-process memory (single container)Redis 7 (volume redisdata, --appendonly yes)
RAG ingest async queuein-process queueasynq (on Redis)
Uploads, artifacts, local object storage, backup ZIPsdirectories created under DATA_DIR: uploads/ (local object storage under uploads/object-storage/), artifacts/, backups/ (bind mount)same DATA_DIR (bind-mounted into app); admins may switch to S3/OSS
Admin runtime config (model policy, RAG knobs, quotas, moderation, announcement — full key list in System & audit)settings table (DB key/value, hot reload)same; across replicas via Redis channel cfg:invalidate

Key points:

  • vector_points exists in both dialects and is genuinely written by the personal edition — deliberately kept, so logical backups (pg_dump / .backup) capture every relational fact without depending on a vector engine (schema.sql:793-795). Qdrant data is its "other copy of the truth" and must be backed up separately (see backup scope).
  • The personal compose file explicitly clears REDIS_URL="" and QDRANT_URL="" so a copied env value can never silently re-enable external stores.
  • SQLite is single-writer: store.Open pins SetMaxOpenConns(1) for SQLite and runs PRAGMA foreign_keys = ON per connection; PostgreSQL uses a pool (max open 20 / idle 10 / idle 5 min / lifetime 1 h, store.go:44-50,73-76). Hence the personal edition is single-instance only — never NFS-share the data dir or scale horizontally.

Naming and type conventions​

Keep these in mind before reading any table. Pages use the notation SQLite type, writing INTEGER → BIGINT (SQLite → PostgreSQL) where dialects differ:

ConventionNotes
Text primary keysMost tables use id TEXT PRIMARY KEY shaped as prefix_ + 12 hex chars (48 bits of randomness, genID, store/ids.go:14-19), e.g. u_a1b2c3d4e5f6, oa_0f1e2d3c4b5a, msg_9e8d7c6b5a43, kb_1a2b3c4d5e6f, qr_7f6e5d4c3b2a. Exceptions: usage_logs.id (autoincrement integer), usage_stats.source_log_id (integer); the 17 tables with no id column use composite or alternative single-column primary keys (model_skills, model_group_quotas, workspace_members, workspace_policies, workspace_kb_member_permissions, oauth_identities, knowledge_base_shares, vector_points, refresh_tokens, settings, usage_stats, pending_storage_cleanup, payment_order_attempts, conversation_compaction_leases, conversation_generation_leases, registration_domains, domain_users)
High-entropy tokensWhere an id doubles as an unguessable capability secret (public shares conversation_shares.id, workspaces.invite_token, workspace_invites.token) the value comes from genToken(): 48 hex chars = 192 bits (ids.go:25-30)
TimeUnix seconds integers. SQLite INTEGER DEFAULT (strftime('%s','now')); PG uses BIGINT DEFAULT (extract(epoch from now())::bigint) (avoids 2038 and large token sums). now() below means this dialect default expression
Boolean flagsINTEGER 0/1 (common examples: enabled, pinned, fast). The PG dialect deliberately keeps INTEGER instead of BOOLEAN because the Go store layer reads/writes them via int (schema_pg.sql:9-11)
JSONStored in TEXT columns (e.g. blocks, kb_ids, features, permissions, settings.value); PG keeps TEXT too, so the store layer is dialect-independent. Only exceptions: message_feedback.reasons is JSONB in PG (TEXT in SQLite) and vector_points.embedding / user_feedback.screenshot are BYTEA (SQLite BLOB)
Empty string as "none"Optional references use '' rather than NULL to mean "unlinked" (model_id, conversation_id, fallback_channel_id); workspace_id='' always means personal data
REAL → DOUBLE PRECISIONBilling amount columns (cost, credits, price_*, credit_ledger.amount, payment_orders.credits) upgrade to DOUBLE PRECISION in PG. Note: several display/limit mirror columns stay REAL in PG (PG's REAL is a 4-byte float): users.credits_permanent, user_groups.credit_allowance, credit_packages.credits, redeem_codes.credits, redeem_redemptions.credits, model_group_quotas.limit_value, workspace_policies.member_monthly_credit_limit — these are display/soft-threshold values; authoritative amounts always use the *_micros fixed-point integers
AUTOINCREMENT → BIGSERIALOnly usage_logs.id; usage_stats.source_log_id is an integer PK in both (SQLite INTEGER PRIMARY KEY, PG BIGINT)
Plaintext secretschannels.api_key, oauth_providers.client_secret (Apple stores the .p8 key), payment_channels.config, mcp_servers.headers / user_mcp_servers.headers are stored in plaintext in the database and masked only by the API. Backups must be encrypted and access-restricted
Passwordsusers.password_hash is bcrypt (store/ids.go), never plaintext

Migration: there is no external migration tool​

Aivory does not use goose / golang-migrate / Flyway and has no schema_migrations version table. The mechanism is a custom idempotent boot-time evolution (cmd/api/main.go → store.Open → store.Migrate → store.Seed, store.go:100-807):

  1. Pre-probes: columnExists() (SELECT col FROM tbl WHERE 1=0) records whether a few columns pre-existed, as the criterion for "was it just added by this process".
  2. Normalization: skill-name dedupe (dedupeSkillNames), unique text field normalization (normalizeUniqueTextFields).
  3. Apply embedded schema: db.Exec(schemaSQL or schemaPGSQL) — all CREATE TABLE IF NOT EXISTS, side-effect-free on old databases.
  4. Retirement cleanup: delete deprecated settings keys (summary_target_percent, summary_merge_max_tokens).
  5. Additive column evolution (~130 ALTER TABLE ADD COLUMNs): on SQLite a "duplicate column" error is expected and ignored; PG uses ADD COLUMN IF NOT EXISTS for a clean no-op.
  6. One-time repair UPDATEs: role collapsing (workspace_members legacy owner → admin, invalid → guest), workspace-conversation archive reset, session_id backfill, payment snapshot backfill, documents.uploaded_by_user_id attribution, plus the backfill/cleanup idempotency markers listed in full on System & audit.
  7. Legacy column removal: chunks.embedding (vectors moved out) — PG DROP COLUMN IF EXISTS; SQLite first tries DROP COLUMN and otherwise rebuilds the table inside a transaction (RENAME → CREATE → copy rows via INSERT-SELECT → DROP → recreate vector_points, store.go:809-885).
  8. Post-indexes: indexes that depend on additively-added columns run after the ALTERs; partial unique indexes here replace the schema-file definitions (project/KB names become the three-column (user, workspace, name) form; skill/prompt/MCP name uniqueness splits into per-scope partial indexes, store.go:577-618).
  9. Column-parity guard: every additively-migrated column must probe successfully per table — because step 5 swallows errors by design, a genuine failure aborts startup loudly here instead of surfacing later as "no such column" on a live server (store.go:620-671).
  10. Backfills and mirror: install the usage_stats trigger, BackfillUsageStats, then a batch of one-time backfills gated by settings marker flags (or column existence) (msg_search_text_backfill_v1, user_sort_order_backfill_v1, user_onboarded_backfill_v1, oauth_pwset_backfill_v1, user_group_billing_prices_backfill_v2) — keyset-paged, best-effort, stamped once done. The markers are rows in the settings table itself — that is Aivory's "migration bookkeeping".
  11. Seed: INSERT INTO settings(key, value) VALUES(?, ?) ON CONFLICT(key) DO NOTHING for default settings + the always-present ug_free group. No admin is seeded — the first admin can only come from the first-run setup screen (POST /api/setup, see first run).

On manual schema changes​

danger

The only change path for DDL is editing Go source: schema*.sql + a new ALTER + the column-parity guard — all three together, shipped together. Hand-adding a column to a production database is invisible to the app (the column guard and queries do not know it) and can skew the next release's migration behavior.

Rules for manual DML (row-level) work:

  • Back up first (next section) and work in a maintenance window.
  • Personal edition: SQLite's WAL supports concurrent reads, but external writes contend with the app for the write lock (they error after busy_timeout(5000)); stop the app or keep transactions very short.
  • Full edition: wrap a manual patch set in one transaction; PRAGMA foreign_keys is per-connection in SQLite — the app's connections have it on, but external sqlite3 tooling does not by default — run PRAGMA foreign_keys = ON; first.
  • Keep boolean columns 0/1, JSON columns valid, time columns Unix seconds. For settings.value remember it is JSON-encoded — bare true and "true" are different things.
  • Prefer the admin UI (including the Backup & Migration page's config export/import) over hand-edited SQL.

Backup and restore (data-layer view)​

Upgrade, backup and restore owns the backup-scope matrix, principles, and acceptance; this section only adds the concrete commands that page lacks.

SQLite (personal): hot backup​

Never cp a running WAL database (main file + -wal sidecars may tear). The app image has no sqlite3 CLI; run an online .backup from a throwaway container (it reads pages through a SQLite connection, inherently consistent):

cd /path/to/aivory/deploy
docker run --rm -v "$PWD/data-personal:/data" alpine:3 sh -c '
  apk add --no-cache sqlite >/dev/null &&
  sqlite3 /data/aivory.db ".backup /data/aivory-snapshot.db" &&
  sqlite3 /data/aivory-snapshot.db "PRAGMA integrity_check;"'

For a full file-level restore, stop the app container and archive the whole DATA_DIR (database, vectors, uploads, artifacts, archives, backups — one unit).

PostgreSQL (full): pg_dump / pg_restore​

cd /path/to/aivory/deploy
# custom compressed format, schema+data; user/db default to aivory (overridable in compose)
docker compose --env-file .env -f docker-compose.prod.yml \
  exec -T postgres pg_dump -U aivory -d aivory -Fc \
  > aivory-db-$(date +%F).dump

# restore (target db must exist and be truncatable; --clean --if-exists handles existing objects)
cat aivory-db-<date>.dump | docker compose --env-file .env -f docker-compose.prod.yml \
  exec -T postgres pg_restore -U aivory -d aivory --clean --if-exists --no-owner

The usage_stats mirror trigger is not part of the logical dump's table data — after pg_restore the app reinstalls it at startup via Migrate; do not recreate it by hand.

Qdrant: snapshots​

Qdrant publishes no host port in compose (internal network only), so snapshot commands run inside the container:

cd /path/to/aivory/deploy
# collections are named by dimension, typically aivory_c1536 (text-embedding-3-small) or aivory_c256 (bundled local embedder)
docker compose --env-file .env -f docker-compose.prod.yml exec qdrant \
  curl -s -X POST -H "api-key: ${QDRANT_API_KEY:-aivory-internal-qdrant}" \
  http://localhost:6333/collections/aivory_c1536/snapshots
# snapshot files land in /qdrant/snapshots on the qdrantdata volume; then copy them out:
docker compose --env-file .env -f docker-compose.prod.yml cp qdrant:/qdrant/snapshots ./qdrant-snapshots

Losing Qdrant is not losing your knowledge bases: the relational documents/chunks remain, but vector retrieval breaks and must be rebuilt (the admin "Storage & uploads" page offers vector maintenance; re-uploading documents also re-triggers ingest).

Redis: persistence meaning​

The full stack runs AOF with --appendonly yes, volume redisdata. But Redis is never the authoritative store of anything; losing/purging it means:

  • rate limits and login-failure counters reset (they re-accumulate);
  • request-signature nonce dedupe sets empty (a short replay-protection window — restart the app after a purge);
  • cross-process pub/sub (stop-generation, bans, cfg:invalidate) only affects the moment in time;
  • SSE replay buffers are lost: an in-flight answer still finishes and persists server-side, just without event-by-event replay — a refresh reads the complete message from the DB;
  • queued asynq RAG jobs are lost: documents not yet ready are detected as stalled by the ingest heartbeat watchdog and can be re-triggered.

Operational advice: treat pgdata + Qdrant snapshots + DATA_DIR as a consistency triad and back them up together; the Redis volume is covered by the AOF default.

Common operational SQL​

Replace time predicates per dialect: SQLite strftime('%s','now'), PostgreSQL extract(epoch from now())::bigint. The examples below run on both engines.

-- 1) users active in the last 24 hours
SELECT id, email, name, last_seen_at
FROM users
WHERE last_seen_at > strftime('%s','now') - 86400 AND status = 'active'
ORDER BY last_seen_at DESC;
-- on PG replace strftime('%s','now') with extract(epoch from now())::bigint (same below)

-- 2) per-model token spend and cost over 30 days (use the analytics source of truth;
--    unaffected by usage-log pruning)
SELECT s.model_id, m.label, m.currency,
       SUM(s.input_tokens)  AS input_tokens,
       SUM(s.output_tokens) AS output_tokens,
       SUM(s.cost)          AS cost
FROM usage_stats s
LEFT JOIN models m ON m.id = s.model_id          -- model_id survives deletion as a snapshot
WHERE s.created_at > strftime('%s','now') - 30 * 86400
GROUP BY s.model_id, m.label, m.currency
ORDER BY cost DESC;

-- 3) failed upstream requests (diagnostic detail)
SELECT created_at, user_id, model_id, purpose, channel_id, fallback, error
FROM usage_logs
WHERE status = 'error'
ORDER BY id DESC LIMIT 100;

-- 4) stuck RAG ingestion: mid-states with a stale heartbeat
SELECT id, kb_id, filename, status, ingest_updated_at
FROM documents
WHERE status IN ('pending','parsing','embedding')
  AND ingest_updated_at < strftime('%s','now') - 90 * 60;   -- 90 min without heartbeat = zombie candidate

-- 5) permanent-credit balance leaderboard (micros / 1e6)
SELECT u.email, u.credits_permanent_micros / 1000000.0 AS credits
FROM users u
WHERE u.credits_permanent_micros > 0
ORDER BY u.credits_permanent_micros DESC LIMIT 20;

-- 6) unclaimed admin credit-adjustment notices
SELECT user_id, direction, amount_micros / 1000000.0 AS amount, reason, created_at
FROM credit_adjustment_notifications
WHERE claimed_at = 0;

-- 7) redeem-code usage
SELECT r.code, r.kind, r.used_count, r.max_uses, COUNT(x.id) AS redemptions
FROM redeem_codes r
LEFT JOIN redeem_redemptions x ON x.code_id = r.id
GROUP BY r.id, r.code, r.kind, r.used_count, r.max_uses
ORDER BY redemptions DESC;

-- 8) async-delete residue: normally empty; non-empty means a purge was interrupted — an app restart sweeps it
SELECT path, user_id, created_at FROM pending_storage_cleanup ORDER BY created_at;

-- 9) prune diagnostic logs older than 180 days (stats unaffected — the mirror happens at INSERT time)
DELETE FROM usage_logs WHERE created_at < strftime('%s','now') - 180 * 86400;

-- 10) one knowledge base's size by document status
SELECT d.status, COUNT(DISTINCT d.id) AS docs, COALESCE(SUM(d.chunk_count),0) AS chunks
FROM documents d
WHERE d.kb_id = 'kb_1a2b3c4d5e6f'
GROUP BY d.status;

-- 11) workspace member permission inventory
SELECT w.name AS workspace, u.email, wm.role,
       wm.can_create_skills, wm.can_delete_kb_content
FROM workspace_members wm
JOIN workspaces w ON w.id = wm.workspace_id
JOIN users u ON u.id = wm.user_id
ORDER BY w.name, u.email;

-- 12) timed-credit window reconciliation
SELECT user_id, group_id, cycle_anchor, SUM(amount_micros) / 1000000.0 AS spent
FROM credit_ledger
WHERE kind = 'timed_debit'
GROUP BY user_id, group_id, cycle_anchor
ORDER BY spent DESC LIMIT 20;

-- 13) SQLite-specific health checks (run via external sqlite3)
PRAGMA integrity_check;      -- should print ok
PRAGMA foreign_key_check;    -- should print nothing
SELECT name, SUM(pgsize)/1024/1024 AS mb FROM dbstat GROUP BY name ORDER BY mb DESC LIMIT 10;
tip

SQLite's dbstat requires SQLITE_ENABLE_DBSTAT_VIRT at compile time (mattn/go-sqlite3 ships with it); if the table is missing, just du -h the DATA_DIR.

Last updated on