Database Schema
Complete documentation of the haex-vault database schema, including local SQLite tables and sync server schema.
Schema Overview
haex-vault uses a dual-database architecture: a local encrypted SQLite database on each device, and a PostgreSQL database on the sync server.
Local Database
SQLite with SQLCipher encryption, storing all user data and extension tables with CRDT columns.
Sync Server
PostgreSQL (Supabase) storing encrypted column-level changes and vault key metadata.
CRDT Columns
Every table that participates in sync has three special CRDT columns added automatically.
-- Added to all synced tables
haex_timestamp TEXT NOT NULL, -- Max HLC of all columns (for efficient queries)
haex_column_hlcs TEXT NOT NULL -- JSON with per-column HLC timestamps
DEFAULT '{}',
haex_tombstone INTEGER NOT NULL -- Soft-delete flag (0=active, 1=deleted)
DEFAULT 0Example Query
-- Query CRDT columns
SELECT
id,
title,
haex_timestamp,
haex_column_hlcs,
haex_tombstone
FROM haex_passwords
WHERE id = 'abc-123';
-- Result:
-- id: "abc-123"
-- title: "Gmail"
-- haex_timestamp: "2024-01-03T10:45:30.200-B"
-- haex_column_hlcs: '{"id":"2024-01-03T10:00:00.000-A","title":"2024-01-03T10:45:30.200-B"}'
-- haex_tombstone: 0
Core Tables
These tables are part of haex-vault core and exist in every vault.
| Table Name | Description | CRDT |
|---|---|---|
| haex_vault_settings | Key-value settings storage for the vault | Yes |
| haex_extensions | Installed extensions metadata | Yes |
| haex_extension_permissions | Extension permissions granted by user | Yes |
| haex_extension_migrations | Tracking of applied extension migrations | Yes |
| haex_workspaces | Desktop workspace definitions | Yes |
| haex_desktop_items | Items placed on workspaces | Yes |
| haex_devices | Registered devices for this vault | Yes |
| haex_sync_backends | Sync server configurations | Yes |
| haex_notifications | In-app notifications | Yes |
haex_sync_backends
CREATE TABLE haex_sync_backends (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
server_url TEXT NOT NULL,
vault_id TEXT NOT NULL,
email TEXT NOT NULL,
password TEXT NOT NULL, -- Encrypted server password
sync_key TEXT NOT NULL, -- Encrypted sync key
vault_key_salt TEXT NOT NULL, -- Salt for vault key derivation
enabled INTEGER DEFAULT 1,
priority INTEGER DEFAULT 0,
last_push_hlc_timestamp TEXT, -- Last pushed HLC
last_pull_server_timestamp TEXT, -- Last pull timestamp
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
-- CRDT columns
haex_timestamp TEXT NOT NULL,
haex_column_hlcs TEXT NOT NULL DEFAULT '{}',
haex_tombstone INTEGER NOT NULL DEFAULT 0
);
CRDT Infrastructure
These tables support the sync system but are NOT synced themselves (local-only).
| Table Name | Description | Synced |
|---|---|---|
| haex_crdt_dirty_tables | Tables with pending local changes to sync | Local Only |
| haex_crdt_migrations | Applied schema migrations tracking | Local Only |
| haex_crdt_conflicts | Detected sync conflicts (for debugging) | Local Only |
haex_crdt_dirty_tables
-- Tracks tables with pending changes
CREATE TABLE haex_crdt_dirty_tables (
table_name TEXT PRIMARY KEY,
last_modified TEXT NOT NULL
);
-- Example entry:
-- table_name: "haex_passwords"
-- last_modified: "2024-01-03 10:45:30"
Extension Tables
Extensions can create their own tables. These are automatically namespaced to prevent conflicts.
-- Extension Table Naming Convention
{public_key}__{extension_name}__{table_name}
-- Examples:
b4401f13f65e576b__haex-pass__haex_passwords_items
b4401f13f65e576b__haex-pass__haex_passwords_groups
Automatic CRDT Setup:When an extension creates a table via migrations, CRDT columns and triggers are automatically added. Extension tables sync just like core tables.
Sync Server Schema
The sync server (haex-sync-server) uses PostgreSQL with Supabase for authentication and realtime.
vault_keys
CREATE TABLE vault_keys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
vault_id TEXT NOT NULL,
encrypted_vault_key TEXT NOT NULL, -- AES-256-GCM encrypted
encrypted_vault_name TEXT NOT NULL, -- AES-256-GCM encrypted
vault_key_salt TEXT NOT NULL, -- Argon2 salt for key derivation
vault_name_salt TEXT NOT NULL, -- Argon2 salt for name derivation
vault_key_nonce TEXT NOT NULL,
vault_name_nonce TEXT NOT NULL,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now(),
UNIQUE(user_id, vault_id)
);
sync_changes (Partitioned by vault_id)
CREATE TABLE sync_changes (
vault_id TEXT NOT NULL,
table_name TEXT NOT NULL,
row_pks JSONB NOT NULL, -- Primary key(s) as JSON
column_name TEXT NOT NULL,
new_value BYTEA, -- Encrypted column value
hlc_timestamp TEXT NOT NULL,
device_id TEXT NOT NULL,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now(),
PRIMARY KEY (vault_id, hlc_timestamp, table_name, row_pks, column_name)
) PARTITION BY LIST (vault_id);
-- Partitions created automatically per vault:
-- sync_changes_abc123_def456_...
-- Auto-created when vault first syncs
Row Level Security:Each partition has RLS policies ensuring users can only access their own vault's data. Policies are automatically created when partitions are created.
SQLite Triggers
CRDT tables have INSERT and UPDATE triggers that mark the table as dirty when data changes.
-- Trigger naming convention:
z_dirty_{TABLE_NAME}_{insert|update}
-- Example triggers for haex_passwords table:
CREATE TRIGGER IF NOT EXISTS "z_dirty_haex_passwords_insert"
AFTER INSERT ON "haex_passwords"
FOR EACH ROW BEGIN
INSERT OR REPLACE INTO haex_crdt_dirty_tables
(table_name, last_modified)
VALUES ('haex_passwords', datetime('now'));
END;
CREATE TRIGGER IF NOT EXISTS "z_dirty_haex_passwords_update"
AFTER UPDATE ON "haex_passwords"
FOR EACH ROW BEGIN
INSERT OR REPLACE INTO haex_crdt_dirty_tables
(table_name, last_modified)
VALUES ('haex_passwords', datetime('now'));
END;
Auto-Generated:Triggers are automatically created by the Rust backend when tables are set up for CRDT sync. You don't need to create them manually.