Cuti-E Database Schema

Cuti-E uses Cloudflare D1 (SQLite at the edge) for persistence.

Entity Relationship Diagram

erDiagram
    customers ||--o{ admin_users : "has"
    customers ||--o{ conversations : "has"
    customers ||--o{ admin_customer_memberships : "has"
    customers ||--o{ pending_team_invites : "has"
    admin_users ||--o{ admin_emails : "has"
    admin_users ||--o{ sessions : "has"
    admin_users ||--o{ admin_customer_memberships : "belongs to"
    conversations ||--o{ messages : "has"

    customers {
        TEXT customer_id PK
        TEXT name
        TEXT email
        TEXT tier
        TEXT api_key UK
        TEXT mascot_name
        TEXT mascot_avatar_url
        INTEGER created_at
    }

    admin_users {
        TEXT admin_id PK
        TEXT customer_id FK
        TEXT email
        TEXT name
        TEXT role
        TEXT password_hash
        TEXT apple_user_id
        TEXT google_user_id
        TEXT totp_secret
        INTEGER created_at
    }

    admin_emails {
        TEXT email PK
        TEXT admin_id FK
        INTEGER is_primary
        INTEGER verified
        INTEGER created_at
    }

    admin_customer_memberships {
        TEXT membership_id PK
        TEXT admin_id FK
        TEXT customer_id FK
        TEXT role
        INTEGER joined_at
    }

    pending_team_invites {
        TEXT invite_id PK
        TEXT invite_token UK
        TEXT customer_id FK
        TEXT email
        TEXT role
        TEXT invited_by FK
        INTEGER created_at
        INTEGER expires_at
    }

    sessions {
        TEXT session_id PK
        TEXT admin_id FK
        TEXT session_token UK
        TEXT current_customer_id
        INTEGER expires_at
        INTEGER created_at
    }

    conversations {
        TEXT conversation_id PK
        TEXT customer_id FK
        TEXT device_id
        TEXT app_name
        TEXT category
        TEXT status
        TEXT priority
        INTEGER created_at
    }

    messages {
        TEXT message_id PK
        TEXT conversation_id FK
        TEXT sender_type
        TEXT sender_id
        TEXT content
        INTEGER created_at
    }

Tables

customers

The root entity. Each customer (app developer) has their own data namespace.

CREATE TABLE customers (
    customer_id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT NOT NULL,
    tier TEXT DEFAULT 'free',
    api_key TEXT UNIQUE,
    mascot_name TEXT,
    mascot_avatar_url TEXT,
    created_at INTEGER
);

admin_users

Admin users who manage conversations. Supports Apple, Google, and email/password auth.

CREATE TABLE admin_users (
    admin_id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT,
    role TEXT DEFAULT 'admin',  -- 'owner', 'admin', 'viewer'
    password_hash TEXT,
    apple_user_id TEXT,
    google_user_id TEXT,
    totp_secret TEXT,
    created_at INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

admin_emails

Multi-email support for admin accounts. Login works with any verified linked email.

CREATE TABLE admin_emails (
    email TEXT PRIMARY KEY,
    admin_id TEXT NOT NULL,
    is_primary INTEGER DEFAULT 0,
    verified INTEGER DEFAULT 0,
    created_at INTEGER,
    FOREIGN KEY (admin_id) REFERENCES admin_users(admin_id)
);

admin_customer_memberships

Multi-team support. Admins can belong to multiple customers (teams).

CREATE TABLE admin_customer_memberships (
    membership_id TEXT PRIMARY KEY,
    admin_id TEXT NOT NULL,
    customer_id TEXT NOT NULL,
    role TEXT DEFAULT 'admin',
    joined_at INTEGER,
    UNIQUE(admin_id, customer_id),
    FOREIGN KEY (admin_id) REFERENCES admin_users(admin_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

pending_team_invites

Invitations to join a team, with expirable tokens.

CREATE TABLE pending_team_invites (
    invite_id TEXT PRIMARY KEY,
    invite_token TEXT UNIQUE NOT NULL,
    customer_id TEXT NOT NULL,
    email TEXT NOT NULL,
    role TEXT DEFAULT 'admin',
    invited_by TEXT NOT NULL,
    created_at INTEGER,
    expires_at INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (invited_by) REFERENCES admin_users(admin_id)
);

sessions

Admin login sessions with multi-team context switching.

CREATE TABLE sessions (
    session_id TEXT PRIMARY KEY,
    admin_id TEXT NOT NULL,
    session_token TEXT UNIQUE NOT NULL,
    current_customer_id TEXT,  -- For multi-team context switching
    expires_at INTEGER,
    created_at INTEGER,
    FOREIGN KEY (admin_id) REFERENCES admin_users(admin_id)
);

conversations

Feedback conversations from end users.

CREATE TABLE conversations (
    conversation_id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    device_id TEXT NOT NULL,
    app_name TEXT,
    category TEXT,
    status TEXT DEFAULT 'open',
    priority TEXT DEFAULT 'normal',
    created_at INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

messages

Messages within conversations, from users or admins.

CREATE TABLE messages (
    message_id TEXT PRIMARY KEY,
    conversation_id TEXT NOT NULL,
    sender_type TEXT NOT NULL,  -- 'user' or 'admin'
    sender_id TEXT,
    content TEXT NOT NULL,
    created_at INTEGER,
    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id)
);

Best Practices

Always use transactions for multi-table operations

Use D1's batch() method for atomic transactions. Without transactions, partial failures leave orphaned data.

// Atomic -- all succeed or all fail
await db.batch([
  db.prepare('INSERT INTO customers ...').bind(...),
  db.prepare('INSERT INTO admin_users ...').bind(...),
  db.prepare('INSERT INTO admin_emails ...').bind(...),
]);

Always include customer_id in queries

Every query must filter by customer_id for multi-tenant data isolation.