Database Blueprint & Key Security

To support decentralized authorization, NexID Auth-DPoP divides state ownership between the Central Auth Server (identities and application registries) and the Downstream Application Servers (permissions and user profiles).

1. Central Auth Server Database (Cloudflare D1)

The Central Auth Server utilizes Cloudflare D1 (SQLite) as its primary global data store. It tracks registered downstream applications permitted to request backchannel upgrades and manages the core identity credentials.

-- Applications Register
-- Tracks client IDs and their corresponding verification hashes
CREATE TABLE registered_applications (
    client_id TEXT PRIMARY KEY,
    application_name TEXT NOT NULL,
    api_key_hash TEXT NOT NULL,                  -- SHA-256 hash of the application's secret key
    allow_custom_permissions INTEGER DEFAULT 1,  -- Boolean: 1 = Allowed to inject custom bitmasks
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);
 
-- Central User Store
-- Contains core credentials; does not store roles or permissions
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

2. Downstream Application Database (Local SQL)

This database schema exists entirely at the application server origin (e.g., PostgreSQL, MySQL, SQLite). It holds application-specific business tables, local permission mappings, and a shadow user table mapping directly to the Central Auth’s user ID.

-- Local Shadow User Table
-- Maps directly to the 'sub' (subject) claim from the validated JWT
CREATE TABLE users (
    id TEXT PRIMARY KEY,                         -- Maps to 'sub' from the JWT
    display_name TEXT,
    avatar_url TEXT
);
 
-- Local Application Permissions Registry
-- Powers of 2 represent individual bits (1, 2, 4, 8, etc.)
CREATE TABLE local_permissions (
    id INTEGER PRIMARY KEY,                      -- Powers of 2: 1, 2, 4, 8, etc.
    name TEXT UNIQUE NOT NULL,
    description TEXT
);
 
-- User-Permission Assignments
-- Maps users to their specific roles/permissions locally
CREATE TABLE user_permissions (
    user_id TEXT,
    local_permission_id INTEGER,
    PRIMARY KEY (user_id, local_permission_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (local_permission_id) REFERENCES local_permissions(id) ON DELETE CASCADE
);

Retrieving the Permission Bitmask

To fetch a user’s local permission bitmask, the application’s integration library executes a fast query using the SUM function. Because permissions are represented as distinct bits (powers of 2), summing them computes the combined bitmask value:

SELECT IFNULL(SUM(local_permission_id), 0) as user_bitmask 
FROM user_permissions 
WHERE user_id = ?;

3. Security Architecture: The “Application Auth Key”

To prevent malicious clients or unauthorized services from spoofing user permissions during the upgrade phase, we enforce strict server-to-server validation.

API Key Generation

When a developer registers a downstream application on the Central Auth Platform:

1. The platform generates a high-entropy secret token (e.g., sk_live_abc123...).
2. This secret is displayed to the developer once and must be stored securely in their server-side environment variables (APP_AUTH_KEY).

Zero-Knowledge Secret Storage

To maintain high security, the Central Auth Server never stores the plain text secret in its database.

• The worker computes a one-way SHA-256 hash of the plain text secret.
• Only the SHA-256 hash is saved in the api_key_hash field of the registered_applications table.

Upgrade Validation Handshake

When the application server calls /api/tokens/upgrade:

1. It sends the plain text APP_AUTH_KEY in the Authorization bearer header.
2. The Cloudflare Worker hashes the incoming header value using SHA-256.
3. The Worker executes a constant-time SQL lookup against the registered_applications table to find a matching api_key_hash.