kvitt/server/crates/kvitt-server/migrations/0001_init.sql

-- Kvitt server schema v1. Do not edit; add a new migration and bump the schema version (SPEC §14.8).

CREATE TABLE meta (
    key   TEXT PRIMARY KEY NOT NULL,
    value TEXT NOT NULL
) STRICT;

-- One global, strictly increasing counter for server_version. Exactly one row.
CREATE TABLE sync_sequence (
    id    INTEGER PRIMARY KEY CHECK (id = 1),
    value INTEGER NOT NULL
) STRICT;

CREATE TABLE users (
    id              TEXT PRIMARY KEY NOT NULL,
    role            TEXT NOT NULL CHECK (role IN ('admin', 'member')),
    display_name    TEXT NOT NULL CHECK (length(display_name) BETWEEN 1 AND 40),
    picture_version INTEGER CHECK (picture_version IS NULL OR picture_version >= 1),
    created_at      TEXT NOT NULL,
    updated_at      TEXT NOT NULL,
    server_version  INTEGER NOT NULL UNIQUE
) STRICT;
CREATE UNIQUE INDEX users_single_admin ON users (role) WHERE role = 'admin';

CREATE TABLE devices (
    id                 TEXT PRIMARY KEY NOT NULL,
    user_id            TEXT NOT NULL REFERENCES users (id),
    name               TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 60),
    token_hash         TEXT NOT NULL UNIQUE,
    fcm_token          TEXT,
    app_version_code   INTEGER,
    created_at         TEXT NOT NULL,
    last_seen_at       TEXT NOT NULL,
    revoked_at         TEXT,
    revoked_by_user_id TEXT REFERENCES users (id)
) STRICT;
CREATE INDEX devices_user ON devices (user_id);

CREATE TABLE invites (
    id                 TEXT PRIMARY KEY NOT NULL,
    kind               TEXT NOT NULL CHECK (kind IN ('admin_setup', 'partner', 'device')),
    code_hash          TEXT NOT NULL UNIQUE,
    target_user_id     TEXT REFERENCES users (id),
    created_by_user_id TEXT REFERENCES users (id),
    created_at         TEXT NOT NULL,
    expires_at         TEXT NOT NULL,
    accepted_at        TEXT,
    accepted_device_id TEXT REFERENCES devices (id),
    cancelled_at       TEXT,
    CHECK ((kind = 'device') = (target_user_id IS NOT NULL)),
    CHECK ((kind = 'admin_setup') = (created_by_user_id IS NULL) OR kind = 'device')
) STRICT;

CREATE TABLE accounts (
    id                 TEXT PRIMARY KEY NOT NULL,
    name               TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 60),
    name_key           TEXT NOT NULL,
    created_by_user_id TEXT NOT NULL REFERENCES users (id),
    created_at         TEXT NOT NULL,
    updated_at         TEXT NOT NULL,
    archived_at        TEXT,
    deleted_at         TEXT,
    server_version     INTEGER NOT NULL UNIQUE
) STRICT;
-- name_key = Unicode-lowercased, trimmed name, computed in Rust.
CREATE UNIQUE INDEX accounts_name_live ON accounts (name_key) WHERE deleted_at IS NULL;

CREATE TABLE account_default_shares (
    account_id TEXT NOT NULL REFERENCES accounts (id),
    user_id    TEXT NOT NULL REFERENCES users (id),
    share_bp   INTEGER NOT NULL CHECK (share_bp BETWEEN 0 AND 10000),
    PRIMARY KEY (account_id, user_id)
) STRICT, WITHOUT ROWID;

CREATE TABLE categories (
    id             TEXT PRIMARY KEY NOT NULL,
    default_key    TEXT UNIQUE CHECK (default_key IS NULL OR default_key IN
                       ('groceries', 'restaurants', 'home', 'utilities', 'transport',
                        'travel', 'entertainment', 'health', 'shopping', 'other')),
    name           TEXT CHECK (name IS NULL OR length(name) BETWEEN 1 AND 40),
    icon           TEXT NOT NULL,
    color          TEXT NOT NULL,
    sort_order     INTEGER NOT NULL,
    created_at     TEXT NOT NULL,
    updated_at     TEXT NOT NULL,
    archived_at    TEXT,
    deleted_at     TEXT,
    server_version INTEGER NOT NULL UNIQUE,
    CHECK (name IS NOT NULL OR default_key IS NOT NULL)
) STRICT;

CREATE TABLE recurring_templates (
    id                    TEXT PRIMARY KEY NOT NULL,
    account_id            TEXT NOT NULL REFERENCES accounts (id),
    category_id           TEXT NOT NULL REFERENCES categories (id),
    amount_ore            INTEGER NOT NULL CHECK (amount_ore BETWEEN 1 AND 1000000000),
    description           TEXT NOT NULL CHECK (length(description) BETWEEN 1 AND 100),
    notes                 TEXT NOT NULL DEFAULT '' CHECK (length(notes) <= 1000),
    payer_user_id         TEXT NOT NULL REFERENCES users (id),
    split_mode            TEXT NOT NULL CHECK (split_mode IN ('default', 'ratio', 'exact', 'full')),
    day_of_month          INTEGER NOT NULL CHECK (day_of_month BETWEEN 1 AND 31),
    start_period          TEXT NOT NULL,
    end_period            TEXT,
    paused                INTEGER NOT NULL DEFAULT 0 CHECK (paused IN (0, 1)),
    last_generated_period TEXT,
    created_by_user_id    TEXT NOT NULL REFERENCES users (id),
    updated_by_user_id    TEXT NOT NULL REFERENCES users (id),
    created_at            TEXT NOT NULL,
    updated_at            TEXT NOT NULL,
    deleted_at            TEXT,
    server_version        INTEGER NOT NULL UNIQUE
) STRICT;
CREATE INDEX recurring_templates_account ON recurring_templates (account_id);

-- Modes ratio and full: share_bp set, share_ore NULL. Mode exact: share_ore set, share_bp NULL. Mode default: no rows.
CREATE TABLE recurring_template_splits (
    template_id TEXT NOT NULL REFERENCES recurring_templates (id),
    user_id     TEXT NOT NULL REFERENCES users (id),
    share_bp    INTEGER CHECK (share_bp IS NULL OR share_bp BETWEEN 0 AND 10000),
    share_ore   INTEGER CHECK (share_ore IS NULL OR share_ore >= 0),
    PRIMARY KEY (template_id, user_id),
    CHECK ((share_bp IS NULL) <> (share_ore IS NULL))
) STRICT, WITHOUT ROWID;

CREATE TABLE expenses (
    id                    TEXT PRIMARY KEY NOT NULL,
    account_id            TEXT NOT NULL REFERENCES accounts (id),
    category_id           TEXT NOT NULL REFERENCES categories (id),
    amount_ore            INTEGER NOT NULL CHECK (amount_ore BETWEEN 1 AND 1000000000),
    description           TEXT NOT NULL CHECK (length(description) BETWEEN 1 AND 100),
    notes                 TEXT NOT NULL DEFAULT '' CHECK (length(notes) <= 1000),
    expense_date          TEXT NOT NULL,
    payer_user_id         TEXT NOT NULL REFERENCES users (id),
    split_mode            TEXT NOT NULL CHECK (split_mode IN ('default', 'ratio', 'exact', 'full')),
    recurring_template_id TEXT REFERENCES recurring_templates (id),
    recurring_period      TEXT,
    import_key            TEXT UNIQUE,
    created_by_user_id    TEXT NOT NULL REFERENCES users (id),
    updated_by_user_id    TEXT NOT NULL REFERENCES users (id),
    updated_by_device_id  TEXT REFERENCES devices (id),
    created_at            TEXT NOT NULL,
    updated_at            TEXT NOT NULL,
    deleted_at            TEXT,
    server_received_at    TEXT NOT NULL,
    server_version        INTEGER NOT NULL UNIQUE,
    CHECK ((recurring_template_id IS NULL) = (recurring_period IS NULL))
) STRICT;
CREATE UNIQUE INDEX expenses_recurring_once ON expenses (recurring_template_id, recurring_period)
    WHERE recurring_template_id IS NOT NULL;
CREATE INDEX expenses_account ON expenses (account_id);
CREATE INDEX expenses_category ON expenses (category_id);

-- Exactly two rows per expense, one per household user. share_bp NULL for mode exact.
CREATE TABLE expense_splits (
    expense_id TEXT NOT NULL REFERENCES expenses (id),
    user_id    TEXT NOT NULL REFERENCES users (id),
    share_ore  INTEGER NOT NULL CHECK (share_ore >= 0),
    share_bp   INTEGER CHECK (share_bp IS NULL OR share_bp BETWEEN 0 AND 10000),
    PRIMARY KEY (expense_id, user_id)
) STRICT, WITHOUT ROWID;

CREATE TABLE settlements (
    id                   TEXT PRIMARY KEY NOT NULL,
    from_user_id         TEXT NOT NULL REFERENCES users (id),
    to_user_id           TEXT NOT NULL REFERENCES users (id),
    amount_ore           INTEGER NOT NULL CHECK (amount_ore BETWEEN 1 AND 1000000000),
    settlement_date      TEXT NOT NULL,
    note                 TEXT NOT NULL DEFAULT '' CHECK (length(note) <= 200),
    import_key           TEXT UNIQUE,
    created_by_user_id   TEXT NOT NULL REFERENCES users (id),
    updated_by_user_id   TEXT NOT NULL REFERENCES users (id),
    updated_by_device_id TEXT REFERENCES devices (id),
    created_at           TEXT NOT NULL,
    updated_at           TEXT NOT NULL,
    deleted_at           TEXT,
    server_received_at   TEXT NOT NULL,
    server_version       INTEGER NOT NULL UNIQUE,
    CHECK (from_user_id <> to_user_id)
) STRICT;

CREATE TABLE settlement_allocations (
    settlement_id TEXT NOT NULL REFERENCES settlements (id),
    account_id    TEXT NOT NULL REFERENCES accounts (id),
    amount_ore    INTEGER NOT NULL CHECK (amount_ore >= 1),
    PRIMARY KEY (settlement_id, account_id)
) STRICT, WITHOUT ROWID;
CREATE INDEX settlement_allocations_account ON settlement_allocations (account_id);

-- Append-only. changes is a JSON array of {"field","old","new"} (SPEC §14.4.7).
CREATE TABLE audit_log (
    id              TEXT PRIMARY KEY NOT NULL,
    entity_type     TEXT NOT NULL CHECK (entity_type IN ('expense', 'settlement')),
    entity_id       TEXT NOT NULL,
    action          TEXT NOT NULL CHECK (action IN ('create', 'update', 'delete')),
    source          TEXT NOT NULL CHECK (source IN ('app', 'recurring', 'import')),
    actor_user_id   TEXT NOT NULL REFERENCES users (id),
    actor_device_id TEXT REFERENCES devices (id),
    client_at       TEXT NOT NULL,
    server_at       TEXT NOT NULL,
    changes         TEXT NOT NULL,
    server_version  INTEGER NOT NULL UNIQUE
) STRICT;
CREATE INDEX audit_log_entity ON audit_log (entity_type, entity_id);

-- Idempotency of POST /sync. Rows older than 90 days are pruned daily.
CREATE TABLE sync_mutations (
    mutation_id TEXT PRIMARY KEY NOT NULL,
    device_id   TEXT NOT NULL REFERENCES devices (id),
    entity_type TEXT NOT NULL CHECK (entity_type IN ('expense', 'settlement')),
    entity_id   TEXT NOT NULL,
    result      TEXT NOT NULL,
    received_at TEXT NOT NULL
) STRICT;
CREATE INDEX sync_mutations_received ON sync_mutations (received_at);

INSERT INTO sync_sequence (id, value) VALUES (1, 10);
INSERT INTO meta (key, value) VALUES ('schema_version', '1');
INSERT INTO meta (key, value) VALUES ('sync_epoch', lower(hex(randomblob(16))));

INSERT INTO categories (id, default_key, name, icon, color, sort_order, created_at, updated_at, archived_at, deleted_at, server_version) VALUES
    ('01920000-0000-7000-8000-000000000001', 'groceries',     NULL, 'shopping_cart',    'green',  10,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 1),
    ('01920000-0000-7000-8000-000000000002', 'restaurants',   NULL, 'restaurant',       'orange', 20,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 2),
    ('01920000-0000-7000-8000-000000000003', 'home',          NULL, 'home',             'brown',  30,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 3),
    ('01920000-0000-7000-8000-000000000004', 'utilities',     NULL, 'bolt',             'yellow', 40,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 4),
    ('01920000-0000-7000-8000-000000000005', 'transport',     NULL, 'directions_car',   'blue',   50,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 5),
    ('01920000-0000-7000-8000-000000000006', 'travel',        NULL, 'flight',           'teal',   60,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 6),
    ('01920000-0000-7000-8000-000000000007', 'entertainment', NULL, 'movie',            'purple', 70,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 7),
    ('01920000-0000-7000-8000-000000000008', 'health',        NULL, 'medical_services', 'red',    80,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 8),
    ('01920000-0000-7000-8000-000000000009', 'shopping',      NULL, 'shopping_bag',     'pink',   90,  '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 9),
    ('01920000-0000-7000-8000-00000000000a', 'other',         NULL, 'category',         'grey',   100, '2026-10-17T00:00:00.000Z', '2026-10-17T00:00:00.000Z', NULL, NULL, 10);