Database Schema

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

Table	Purpose	Records
admin_roles	Role definitions with permission arrays	3 seeded
admin_role_assignments	Maps Clerk users → roles	Dynamic
admin_audit_logs	Immutable append-only action log	Append-only
tier_configs	Runtime-editable tier registry	4 seeded
scope_configs	Runtime-editable scope registry	3 seeded
endpoint_auth_overrides	Per-endpoint auth requirements	Dynamic
admin_announcements	System-wide banners/notifications	Dynamic

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

erDiagram
    admin_roles {
        INTEGER id PK
        TEXT role_name UK
        TEXT display_name
        TEXT description
        TEXT permissions "JSON array"
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
    }

    admin_role_assignments {
        INTEGER id PK
        TEXT clerk_user_id
        TEXT role_name FK
        TEXT assigned_by
        TEXT assigned_at
        TEXT expires_at "nullable"
    }

    admin_audit_logs {
        INTEGER id PK
        TEXT actor_id
        TEXT actor_email
        TEXT action
        TEXT resource_type
        TEXT resource_id
        TEXT old_values "JSON"
        TEXT new_values "JSON"
        TEXT ip_address
        TEXT user_agent
        TEXT status
        TEXT metadata "JSON"
        TEXT created_at
    }

    tier_configs {
        INTEGER id PK
        TEXT tier_name UK
        INTEGER order_rank
        INTEGER rate_limit
        TEXT display_name
        TEXT description
        TEXT features "JSON object"
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
    }

    scope_configs {
        INTEGER id PK
        TEXT scope_name UK
        TEXT display_name
        TEXT description
        TEXT required_tier
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
    }

    endpoint_auth_overrides {
        INTEGER id PK
        TEXT path_pattern
        TEXT method
        TEXT required_tier "nullable"
        TEXT required_scopes "JSON array"
        INTEGER is_public
        INTEGER is_active
        TEXT created_at
        TEXT updated_at
    }

    admin_announcements {
        INTEGER id PK
        TEXT title
        TEXT body
        TEXT severity "info|warning|error|success"
        TEXT active_from "nullable"
        TEXT active_until "nullable"
        INTEGER is_active
        TEXT created_by
        TEXT created_at
        TEXT updated_at
    }

    admin_roles ||--o{ admin_role_assignments : "role_name"

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)

Role	Permissions Count	Key Permissions
viewer	6	admin:read, audit:read, metrics:read, config:read, users:read, flags:read
editor	16	All viewer + config:write, flags:write, tiers:write, scopes:write, endpoints:write, announcements:write
super-admin	27	All permissions

Tiers (4)

Tier	Order	Rate Limit	Features
anonymous	0	10/min	maxSources: 3, maxBatchSize: 1
free	1	60/min	maxSources: 10, maxBatchSize: 5
pro	2	300/min	maxSources: 50, maxBatchSize: 25, priorityQueue
admin	3	Unlimited	maxSources: -1, maxBatchSize: -1, priorityQueue, rawSqlAccess

Scopes (3)

Scope	Required Tier	Description
compile	free	Compile and download filter lists
rules	free	CRUD custom filter rules
admin	admin	Full 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'))
);