AdminDatabase Schema

Database Schema

The admin system stores all configuration and audit data in a dedicated D1 database (ADMINDB), separate from the application database (DB). This...

Database Schema

The admin system stores all configuration and audit data in a dedicated D1 database (ADMIN_DB), separate from the application database (DB). This blast-radius isolation ensures admin operations cannot affect application data.

Migration

The base schema is defined in admin-migrations/0001_admin_schema.sql, with incremental changes in subsequent numbered files:

admin-migrations/0001_admin_schema.sql        — base schema (all tables below)
admin-migrations/0002_agent_scope_seed.sql    — seed data for agent scopes
admin-migrations/0003_rename_clerk_user_id.sql — column rename
admin-migrations/0004_drop_feature_flags.sql  — drops feature_flags (superseded by Cloudflare Flagship)

Apply them locally or remotely:

# Local development
wrangler d1 migrations apply bloqr-backend-admin-db --local

# Production
wrangler d1 migrations apply bloqr-backend-admin-db --remote

Tables Overview

TablePurposeRecords
admin_rolesRole definitions with permission arrays3 seeded
admin_role_assignmentsMaps Clerk users → rolesDynamic
admin_audit_logsImmutable append-only action logAppend-only
tier_configsRuntime-editable tier registry4 seeded
scope_configsRuntime-editable scope registry3 seeded
endpoint_auth_overridesPer-endpoint auth requirementsDynamic
admin_announcementsSystem-wide banners/notificationsDynamic

feature_flags was removed in admin-migrations/0004_drop_feature_flags.sql — feature flags are now 100% Cloudflare-native via Cloudflare Flagship. See "Removed tables" below.

Entity Relationship Diagram

Table Details

admin_roles

Stores role definitions with JSON permission arrays.

CREATE TABLE IF NOT EXISTS admin_roles (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    role_name   TEXT    NOT NULL UNIQUE,
    display_name TEXT   NOT NULL,
    description TEXT    NOT NULL DEFAULT '',
    permissions TEXT    NOT NULL DEFAULT '[]',   -- JSON array of permission strings
    is_active   INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

Indexes: idx_admin_roles_active(is_active)

admin_role_assignments

Maps Clerk user IDs to admin roles. Supports expiration and tracks who made the assignment.

CREATE TABLE IF NOT EXISTS admin_role_assignments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    clerk_user_id   TEXT    NOT NULL,
    role_name       TEXT    NOT NULL,
    assigned_by     TEXT    NOT NULL,
    assigned_at     TEXT    NOT NULL DEFAULT (datetime('now')),
    expires_at      TEXT,                      -- NULL = never expires
    FOREIGN KEY (role_name) REFERENCES admin_roles(role_name) ON DELETE CASCADE,
    UNIQUE(clerk_user_id, role_name)
);

Indexes: idx_role_assignments_user(clerk_user_id), idx_role_assignments_role(role_name), idx_role_assignments_expiry(expires_at)

The UNIQUE(clerk_user_id, role_name) constraint enables upsert semantics via INSERT ... ON CONFLICT ... DO UPDATE when re-assigning roles.

admin_audit_logs

Immutable append-only audit trail. No UPDATE or DELETE operations are expected on this table.

CREATE TABLE IF NOT EXISTS admin_audit_logs (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    actor_id      TEXT    NOT NULL,
    actor_email   TEXT,
    action        TEXT    NOT NULL,            -- e.g. 'tier.update', 'flag.create'
    resource_type TEXT    NOT NULL,            -- e.g. 'tier_config', 'feature_flag'
    resource_id   TEXT,
    old_values    TEXT,                        -- JSON snapshot before change
    new_values    TEXT,                        -- JSON snapshot after change
    ip_address    TEXT,
    user_agent    TEXT,
    status        TEXT    NOT NULL DEFAULT 'success',
    metadata      TEXT,
    created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

Indexes: idx_audit_actor(actor_id), idx_audit_action(action), idx_audit_resource(resource_type, resource_id), idx_audit_created(created_at), idx_audit_status(status)

tier_configs

Runtime-editable tier registry. Replaces the hardcoded TIER_REGISTRY in worker/types.ts.

CREATE TABLE IF NOT EXISTS tier_configs (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    tier_name    TEXT    NOT NULL UNIQUE,
    order_rank   INTEGER NOT NULL DEFAULT 0,    -- higher = more privileged
    rate_limit   INTEGER NOT NULL DEFAULT 10,   -- requests/min (0 = unlimited)
    display_name TEXT    NOT NULL,
    description  TEXT    NOT NULL DEFAULT '',
    features     TEXT    NOT NULL DEFAULT '{}',  -- JSON object
    is_active    INTEGER NOT NULL DEFAULT 1,
    created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at   TEXT    NOT NULL DEFAULT (datetime('now'))
);

scope_configs

Runtime-editable scope registry. Replaces the hardcoded SCOPE_REGISTRY.

CREATE TABLE IF NOT EXISTS scope_configs (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    scope_name    TEXT    NOT NULL UNIQUE,
    display_name  TEXT    NOT NULL,
    description   TEXT    NOT NULL DEFAULT '',
    required_tier TEXT    NOT NULL DEFAULT 'free',
    is_active     INTEGER NOT NULL DEFAULT 1,
    created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

endpoint_auth_overrides

Per-endpoint authentication requirement overrides. The path_pattern supports wildcards (*).

CREATE TABLE IF NOT EXISTS endpoint_auth_overrides (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    path_pattern    TEXT    NOT NULL,
    method          TEXT    NOT NULL DEFAULT '*',
    required_tier   TEXT,
    required_scopes TEXT,                       -- JSON array
    is_public       INTEGER NOT NULL DEFAULT 0,
    is_active       INTEGER NOT NULL DEFAULT 1,
    created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    UNIQUE(path_pattern, method)
);

admin_announcements

System-wide banners and notifications with time-based activation.

CREATE TABLE IF NOT EXISTS admin_announcements (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT    NOT NULL,
    body        TEXT    NOT NULL DEFAULT '',
    severity    TEXT    NOT NULL DEFAULT 'info',
    active_from TEXT,
    active_until TEXT,
    is_active   INTEGER NOT NULL DEFAULT 1,
    created_by  TEXT,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

Seed Data

The migration seeds initial data to match the current hardcoded values in worker/types.ts.

Roles (3)

RolePermissions CountKey Permissions
viewer6admin:read, audit:read, metrics:read, config:read, users:read, flags:read
editor16All viewer + config:write, flags:write, tiers:write, scopes:write, endpoints:write, announcements:write
super-admin27All permissions

Tiers (4)

TierOrderRate LimitFeatures
anonymous010/minmaxSources: 3, maxBatchSize: 1
free160/minmaxSources: 10, maxBatchSize: 5
pro2300/minmaxSources: 50, maxBatchSize: 25, priorityQueue
admin3UnlimitedmaxSources: -1, maxBatchSize: -1, priorityQueue, rawSqlAccess

Scopes (3)

ScopeRequired TierDescription
compilefreeCompile and download filter lists
rulesfreeCRUD custom filter rules
adminadminFull administrative access

Removed Tables

feature_flags (removed in 0004_drop_feature_flags.sql)

Replaced by Cloudflare Flagship — a 100% Cloudflare-native feature-flag service with a real dashboard UI, audit history, and 11 comparison operators for targeting. This table was never used in production, so the migration drops it outright with no data-migration step. Kept here for historical reference only; do not recreate it.

-- Final shape before removal:
CREATE TABLE IF NOT EXISTS feature_flags (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    flag_name           TEXT    NOT NULL UNIQUE,
    enabled             INTEGER NOT NULL DEFAULT 0,
    rollout_percentage  INTEGER NOT NULL DEFAULT 100,
    target_tiers        TEXT    NOT NULL DEFAULT '[]',    -- JSON array
    target_users        TEXT    NOT NULL DEFAULT '[]',    -- JSON array
    description         TEXT    NOT NULL DEFAULT '',
    created_by          TEXT,
    created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);
Was this page helpful?