Skip to main content

RFC-007: D1 Database Implementation

Overview​

Technical implementation plan for Cloudflare D1 database for Phase 3 features.

Schema Definition​

customers​

CREATE TABLE customers (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    license_key TEXT UNIQUE NOT NULL,
    license_type TEXT NOT NULL DEFAULT 'starter',
    purchase_date DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT (datetime('now')),
    updated_at DATETIME NOT NULL DEFAULT (datetime('now'))
);

payments​

CREATE TABLE payments (
    id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    xendit_payment_id TEXT,
    amount INTEGER NOT NULL,
    platform_fee INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    paid_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

photos​

CREATE TABLE photos (
    id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    r2_key TEXT NOT NULL,
    print_status TEXT NOT NULL DEFAULT 'pending',
    printer_id TEXT,
    created_at DATETIME NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

printers​

CREATE TABLE printers (
    id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    connection_type TEXT NOT NULL,
    is_default INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Worker Integration​

wrangler.toml​

[[d1_databases]]
binding = "DB"
database_name = "photobooth-db"
database_id = "xxxx-xxxx-xxxx"

API Routes​

MethodEndpointDescription
POST/api/customersCreate customer
GET/api/customers/:idGet customer
POST/api/paymentsCreate payment
GET/api/payments/:idGet payment status
POST/api/photosLog photo
GET/api/printersList printers
POST/api/printersRegister printer

TypeScript Types​

interface Customer {
  id: string;
  name: string;
  email: string;
  phone?: string;
  license_key: string;
  license_type: "starter" | "pro" | "enterprise";
  purchase_date: Date;
  created_at: Date;
}

interface Payment {
  id: string;
  customer_id: string;
  xendit_payment_id?: string;
  amount: number;
  platform_fee: number;
  status: "pending" | "processing" | "completed" | "failed";
  paid_at?: Date;
}

interface Photo {
  id: string;
  customer_id: string;
  r2_key: string;
  print_status: "pending" | "printing" | "completed" | "failed";
  printer_id?: string;
}

Backup Strategy​

Since D1 is single-region, implement backup:

// Weekly backup to R2
export async function backupDatabase(env: Env) {
  const db = env.DB;
  const dump = await db.dump(); // Export SQL
  await env.ASSETS.put("backups/db-backup.sql", dump);
}

Implementation Phases​

  1. Phase 1: Set up D1, create tables
  2. Phase 2: CRUD API routes for customers
  3. Phase 3: Payment integration hooks
  4. Phase 4: Photo logging
  5. Phase 5: Backup strategy