Database Schema

-- Extension Table Naming Convention
{public_key}__{extension_name}__{table_name}

-- Examples:
b4401f13f65e576b__haex-pass__haex_passwords_items
b4401f13f65e576b__haex-pass__haex_passwords_groups

-- Server-side: per-identity vault key metadata stored in the
-- haex-sync-server. Authentication is DID-based (the server never
-- sees the user's vault password).
CREATE TABLE vault_keys (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  did                   TEXT NOT NULL,         -- Decentralized Identifier (DID)
  vault_id              TEXT NOT NULL,
  encrypted_vault_key   BYTEA NOT NULL,        -- AES-256-GCM ciphertext
  encrypted_vault_name  BYTEA NOT NULL,        -- AES-256-GCM ciphertext
  vault_key_salt        BYTEA NOT NULL,
  vault_name_salt       BYTEA NOT NULL,
  vault_key_nonce       BYTEA NOT NULL,
  vault_name_nonce      BYTEA NOT NULL,
  created_at            TIMESTAMP DEFAULT now(),
  updated_at            TIMESTAMP DEFAULT now(),
  UNIQUE(did, vault_id)
);

-- Server-side change log. One row per (column, row, change).
-- The server only stores encrypted ciphertext; HLC ordering decides
-- replacement.
CREATE TABLE sync_changes (
  space_id      TEXT NOT NULL,
  table_name    TEXT NOT NULL,
  row_pks       JSONB NOT NULL,        -- Primary key(s) as JSON
  column_name   TEXT NOT NULL,
  encrypted_value BYTEA,                -- AES-256-GCM ciphertext
  nonce         BYTEA NOT NULL,
  haex_hlc      TEXT NOT NULL,         -- HLC of this column change
  device_id     TEXT NOT NULL,
  created_at    TIMESTAMP DEFAULT now(),
  PRIMARY KEY (space_id, haex_hlc, table_name, row_pks, column_name)
);

-- Indexed for "give me everything since cursor X" pull queries.
CREATE INDEX ON sync_changes (space_id, haex_hlc);

-- haex-vault installs three triggers per CRDT table.
-- Naming convention: z_dirty_{TABLE}_{insert|update|delete}

-- INSERT / UPDATE: mark the table as dirty so the sync
-- orchestrator picks it up on the next push.
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;

-- DELETE: log the deletion in haex_deleted_rows BEFORE
-- the actual row is removed, then also mark dirty.
CREATE TRIGGER IF NOT EXISTS "z_dirty_haex_passwords_delete"
BEFORE DELETE ON "haex_passwords"
FOR EACH ROW BEGIN
  INSERT INTO haex_deleted_rows
  (table_name, row_pks, haex_hlc)
  VALUES ('haex_passwords',
          json_object('id', OLD.id),
          /* current HLC */ ?);

  INSERT OR REPLACE INTO haex_crdt_dirty_tables
  (table_name, last_modified)
  VALUES ('haex_passwords', datetime('now'));
END;