Schema for declaration250

CREATE TABLE sections(
        id integer primary key,
        section_order integer,
        section_key text,
        heading text,
        body text);
CREATE TABLE grievances(
        num integer primary key,
        short_title text,
        theme text,
        text text,
        plain_english text);
CREATE VIRTUAL TABLE grievances_fts using fts5(num, short_title, theme, text, plain_english);
CREATE TABLE 'grievances_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'grievances_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'grievances_fts_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3, c4);
CREATE TABLE 'grievances_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'grievances_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE signers(
        seq integer primary key,
        name text,
        state text,
        birth_year integer,
        age_at_signing integer,
        birthplace text,
        education text,
        occupation text);
CREATE VIRTUAL TABLE signers_fts using fts5(seq, name, state, birthplace, education, occupation);
CREATE TABLE 'signers_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'signers_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'signers_fts_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3, c4, c5);
CREATE TABLE 'signers_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'signers_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE catalog_databases (
    database_name TEXT PRIMARY KEY,
    path TEXT,
    is_memory INTEGER,
    schema_version INTEGER
);
CREATE TABLE catalog_tables (
    database_name TEXT,
    table_name TEXT,
    rootpage INTEGER,
    sql TEXT,
    PRIMARY KEY (database_name, table_name),
    FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name)
);
CREATE TABLE catalog_views (
    database_name TEXT,
    view_name TEXT,
    rootpage INTEGER,
    sql TEXT,
    PRIMARY KEY (database_name, view_name),
    FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name)
);
CREATE TABLE catalog_columns (
    database_name TEXT,
    table_name TEXT,
    cid INTEGER,
    name TEXT,
    type TEXT,
    "notnull" INTEGER,
    default_value TEXT, -- renamed from dflt_value
    is_pk INTEGER, -- renamed from pk
    hidden INTEGER,
    PRIMARY KEY (database_name, table_name, name),
    FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name),
    FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name)
);
CREATE TABLE catalog_indexes (
    database_name TEXT,
    table_name TEXT,
    seq INTEGER,
    name TEXT,
    "unique" INTEGER,
    origin TEXT,
    partial INTEGER,
    PRIMARY KEY (database_name, table_name, name),
    FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name),
    FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name)
);
CREATE TABLE catalog_foreign_keys (
    database_name TEXT,
    table_name TEXT,
    id INTEGER,
    seq INTEGER,
    "table" TEXT,
    "from" TEXT,
    "to" TEXT,
    on_update TEXT,
    on_delete TEXT,
    match TEXT,
    PRIMARY KEY (database_name, table_name, id, seq),
    FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name),
    FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name)
);
CREATE TABLE metadata_instance (
    key text,
    value text,
    unique(key)
);
CREATE TABLE metadata_databases (
    database_name text,
    key text,
    value text,
    unique(database_name, key)
);
CREATE TABLE metadata_resources (
    database_name text,
    resource_name text,
    key text,
    value text,
    unique(database_name, resource_name, key)
);
CREATE TABLE metadata_columns (
    database_name text,
    resource_name text,
    column_name text,
    key text,
    value text,
    unique(database_name, resource_name, column_name, key)
);
CREATE TABLE column_types (
    database_name TEXT NOT NULL,
    resource_name TEXT NOT NULL,
    column_name TEXT NOT NULL,
    column_type TEXT NOT NULL,
    config TEXT,
    PRIMARY KEY (database_name, resource_name, column_name)
);
CREATE TABLE queries (
    database_name TEXT NOT NULL,
    name TEXT NOT NULL,
    sql TEXT NOT NULL,
    title TEXT,
    description TEXT,
    description_html TEXT,
    options TEXT NOT NULL DEFAULT '{}',
    parameters TEXT NOT NULL DEFAULT '[]',
    is_write INTEGER NOT NULL DEFAULT 0 CHECK (is_write IN (0, 1)),
    is_private INTEGER NOT NULL DEFAULT 0 CHECK (is_private IN (0, 1)),
    is_trusted INTEGER NOT NULL DEFAULT 0 CHECK (is_trusted IN (0, 1)),
    source TEXT NOT NULL DEFAULT 'user',
    owner_id TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (database_name, name)
);
CREATE INDEX queries_owner_idx
    ON queries(owner_id);
CREATE TABLE apps (
    id TEXT PRIMARY KEY,
    external INTEGER NOT NULL DEFAULT 0,
    name TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    path TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT '',
    metadata TEXT NOT NULL DEFAULT '{}',
    actor_id TEXT,
    is_private INTEGER NOT NULL DEFAULT 1,
    stored_queries TEXT NOT NULL DEFAULT '[]',
    current_version INTEGER,
    deleted_at TEXT,
    deleted_actor_id TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    CHECK (external IN (0, 1)),
    CHECK (is_private IN (0, 1))
);
CREATE TABLE app_revisions (
    app_id TEXT NOT NULL REFERENCES apps(id),
    version INTEGER NOT NULL,
    actor_id TEXT,
    name TEXT,
    description TEXT,
    html TEXT,
    is_private INTEGER,
    sql_databases TEXT,
    stored_queries TEXT,
    csp_origins TEXT,
    changed_fields TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL,
    PRIMARY KEY (app_id, version),
    CHECK (is_private IN (0, 1) OR is_private IS NULL)
);
CREATE INDEX idx_apps_updated ON apps(updated_at DESC, id);
CREATE INDEX idx_apps_external_updated ON apps(external, updated_at DESC, id);
CREATE INDEX idx_apps_source ON apps(source);
CREATE VIRTUAL TABLE apps_fts
USING fts5(name, description, source, content='apps', content_rowid='rowid');
CREATE TABLE 'apps_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'apps_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'apps_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'apps_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TRIGGER apps_ai AFTER INSERT ON apps BEGIN
    INSERT INTO apps_fts(rowid, name, description, source)
    VALUES (new.rowid, new.name, new.description, new.source);
END;
CREATE TRIGGER apps_ad AFTER DELETE ON apps BEGIN
    INSERT INTO apps_fts(apps_fts, rowid, name, description, source)
    VALUES ('delete', old.rowid, old.name, old.description, old.source);
END;
CREATE TRIGGER apps_au AFTER UPDATE ON apps BEGIN
    INSERT INTO apps_fts(apps_fts, rowid, name, description, source)
    VALUES ('delete', old.rowid, old.name, old.description, old.source);
    INSERT INTO apps_fts(rowid, name, description, source)
    VALUES (new.rowid, new.name, new.description, new.source);
END;
CREATE TABLE app_access (
    id INTEGER PRIMARY KEY,
    app_id TEXT REFERENCES apps(id),
    action TEXT NOT NULL,
    subject_type TEXT NOT NULL,
    subject_id TEXT,
    allow INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    CHECK (subject_type IN ('authenticated')),
    CHECK (allow IN (0, 1))
);
CREATE INDEX idx_app_access_lookup
    ON app_access(action, app_id, subject_type, subject_id);
CREATE TABLE app_sql_databases (
    app_id TEXT NOT NULL REFERENCES apps(id),
    database_name TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (app_id, database_name)
);
CREATE INDEX idx_app_sql_databases_app
    ON app_sql_databases(app_id, database_name);
CREATE TABLE app_csp_origins (
    id INTEGER PRIMARY KEY,
    app_id TEXT NOT NULL REFERENCES apps(id),
    directive TEXT NOT NULL DEFAULT 'connect-src',
    origin TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    CHECK (directive IN ('connect-src')),
    UNIQUE (app_id, directive, origin)
);
CREATE INDEX idx_app_csp_origins_app
    ON app_csp_origins(app_id, directive);
CREATE TABLE app_user_state (
    actor_id TEXT NOT NULL,
    app_id TEXT NOT NULL REFERENCES apps(id),
    last_accessed_at TEXT,
    pinned_at TEXT,
    access_count INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (actor_id, app_id)
);
CREATE INDEX idx_app_user_state_actor_pinned
    ON app_user_state(actor_id, pinned_at DESC, last_accessed_at DESC, app_id);
CREATE INDEX idx_app_user_state_actor_recent
    ON app_user_state(actor_id, last_accessed_at DESC, app_id)