Mindful Auth Cloudflare D1 Database Schema Setup

Important: The schema below uses required column names that are hardcoded.

While you can change the table names (members, audit_logs) via configuration, the column names (record_id, password_hash, event_type, etc.) must match exactly, or Mindful Auth will fail.

Create the required Cloudflare D1 tables in your D1 database:

Members/Users table

CREATE TABLE members (
    record_id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT,
    password_salt TEXT,
    authentication_type TEXT,
    authentication_status TEXT DEFAULT 'Unlocked',
    _2fa_status TEXT DEFAULT 'Disabled',
    _2fa_secret TEXT,
    _2fa_recovery_codes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Audit logs table

CREATE TABLE audit_logs (
    record_id TEXT PRIMARY KEY,
    event_type TEXT,
    event_status TEXT,
    risk_level TEXT,
    message TEXT,
    user_record TEXT,
    user_email TEXT,
    timestamp TIMESTAMP,
    timezone TEXT,
    tenant_domain TEXT,
    requested_url TEXT,
    ip_address TEXT,
    location TEXT,
    latitude_longitude TEXT,
    continent TEXT,
    country_code TEXT,
    asn TEXT,
    isp TEXT,
    user_agent TEXT,
    error_details TEXT,
    metadata TEXT,
    session_id TEXT,
    cf_request_id TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);