Database Setup

Initialize and manage the D1 database.

Database Overview

Go Bananas! uses Cloudflare D1 (SQLite) for all persistent data:

Tenants own all entities with cascading relationships

Initial Setup

Create Database

wrangler d1 create go-bananas-db

Note the database ID from the output and update wrangler.jsonc.

Initialize Schema

npm run db:init

This runs schema.sql which creates all tables.

Verify Tables

wrangler d1 execute go-bananas-db --command "SELECT name FROM sqlite_master WHERE type='table'"

Expected tables:

• tenants
• api_keys
• images
• sessions
• characters
• product_references
• style_presets
• usage_logs
• search_presets

Schema Reference

tenants

Stores tenant configuration with encrypted Gemini keys:

CREATE TABLE tenants (
    tenant_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    encrypted_gemini_key TEXT NOT NULL,
    iv TEXT NOT NULL,
    monthly_quota_mb INTEGER DEFAULT 1024,
    rate_limit_per_minute INTEGER DEFAULT 60,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

api_keys

Multiple API keys per tenant:

CREATE TABLE api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    api_key TEXT UNIQUE NOT NULL,
    label TEXT,
    key_type TEXT DEFAULT 'live',
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    last_used_at TEXT,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_api_keys_key ON api_keys(api_key);
CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);

api_key never holds the key itself: it stores sha256:<hex>:<preview> (the SHA-256 hash plus the masked preview shown in the console). See Security → Key Validation.

images

Image metadata with edit lineage:

CREATE TABLE images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    session_id TEXT,
    r2_key TEXT NOT NULL,
    r2_thumbnail_key TEXT,
    public_url TEXT NOT NULL,
    thumbnail_url TEXT,
    width INTEGER,
    height INTEGER,
    size_bytes INTEGER,
    format TEXT,
    prompt TEXT,
    negative_prompt TEXT,
    operation_type TEXT DEFAULT 'generate',
    parent_image_id INTEGER,
    edit_depth INTEGER DEFAULT 0,
    edit_prompt TEXT,
    model_id TEXT,
    aspect_ratio_hint TEXT,
    has_synthid INTEGER DEFAULT 0,
    gemini_file_id TEXT,
    gemini_file_expires_at TEXT,
    style_preset_id INTEGER,
    character_id INTEGER,
    product_reference_id INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_image_id) REFERENCES images(id),
    FOREIGN KEY (style_preset_id) REFERENCES style_presets(id),
    FOREIGN KEY (character_id) REFERENCES characters(id),
    FOREIGN KEY (product_reference_id) REFERENCES product_references(id)
);

CREATE INDEX idx_images_tenant ON images(tenant_id);
CREATE INDEX idx_images_session ON images(session_id);
CREATE INDEX idx_images_created ON images(created_at DESC);
CREATE INDEX idx_images_parent ON images(parent_image_id);
CREATE INDEX idx_images_character ON images(character_id);
CREATE INDEX idx_images_product ON images(product_reference_id);
CREATE INDEX idx_images_preset ON images(style_preset_id);

sessions

Session state for conversational editing:

CREATE TABLE sessions (
    session_id TEXT NOT NULL,
    tenant_id TEXT NOT NULL,
    last_image_id INTEGER,
    total_images INTEGER DEFAULT 0,
    total_edits INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    last_activity_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id, tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (last_image_id) REFERENCES images(id)
);

CREATE INDEX idx_sessions_tenant ON sessions(tenant_id);
CREATE INDEX idx_sessions_activity ON sessions(last_activity_at DESC);

characters

Persistent character library:

CREATE TABLE characters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    character_name TEXT NOT NULL,
    base_prompt TEXT NOT NULL,
    description TEXT,
    negative_prompt TEXT,
    system_instruction TEXT,
    preferred_aspect_ratio TEXT,
    reference_image_ids TEXT,
    tags TEXT,
    times_used INTEGER DEFAULT 0,
    last_used_at TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(tenant_id, character_name),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_characters_tenant ON characters(tenant_id);
CREATE INDEX idx_characters_name ON characters(character_name);
CREATE INDEX idx_characters_usage ON characters(times_used DESC);

product_references

Product images for marketing:

CREATE TABLE product_references (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    product_name TEXT NOT NULL,
    product_url TEXT NOT NULL,
    product_description TEXT,
    r2_key TEXT NOT NULL,
    r2_thumbnail_key TEXT,
    width INTEGER,
    height INTEGER,
    format TEXT,
    size_bytes INTEGER,
    mime_type TEXT,
    tags TEXT,
    times_used INTEGER DEFAULT 0,
    last_used_at TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(tenant_id, product_name),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_products_tenant ON product_references(tenant_id);
CREATE INDEX idx_products_name ON product_references(product_name);

style_presets

Reusable style templates:

CREATE TABLE style_presets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    name TEXT NOT NULL,
    prompt TEXT,
    negative_prompt TEXT,
    system_instruction TEXT,
    aspect_ratio TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(tenant_id, name),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_presets_tenant ON style_presets(tenant_id);
CREATE INDEX idx_presets_name ON style_presets(name);

usage_logs

Analytics and quota tracking:

CREATE TABLE usage_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    session_id TEXT,
    operation TEXT NOT NULL,
    images_generated INTEGER DEFAULT 0,
    total_size_bytes INTEGER DEFAULT 0,
    api_calls_made INTEGER DEFAULT 1,
    estimated_cost_cents INTEGER,
    duration_ms INTEGER,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_usage_tenant_time ON usage_logs(tenant_id, timestamp);
CREATE INDEX idx_usage_operation ON usage_logs(operation);

Migrations

Migration Files

Migrations are in migrations/ directory:

migrations/
├── 0001_initial_schema.sql
├── 0002_add_characters.sql
├── 0003_add_products.sql
├── 0004_add_style_presets.sql
└── ...

Apply Migrations

npm run db:migrate

Or manually:

wrangler d1 execute go-bananas-db --file migrations/0005_new_feature.sql

Create New Migration

# Create migration file
touch migrations/0006_add_feature.sql

Template:

-- Migration: Add feature X
-- Date: 2024-01-15

-- Add new column
ALTER TABLE images ADD COLUMN new_field TEXT;

-- Create new index
CREATE INDEX idx_images_new_field ON images(new_field);

-- Add new table
CREATE TABLE new_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id TEXT NOT NULL,
    -- other columns
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
);

CREATE INDEX idx_new_table_tenant ON new_table(tenant_id);

Backup & Recovery

Export Database

wrangler d1 export go-bananas-db --output backup.sql

Import Database

wrangler d1 execute go-bananas-db --file backup.sql

Scheduled Backups

Create a scheduled worker for automated backups:

export default {
  async scheduled(event, env, ctx) {
    // Export and store to R2
    const backup = await env.DB.dump();
    await env.R2_BACKUPS.put(
      `backup-${new Date().toISOString()}.sql`,
      backup
    );
  }
};

Maintenance Queries

Check Table Sizes

SELECT
    name,
    COUNT(*) as row_count
FROM (
    SELECT 'tenants' as name, COUNT(*) FROM tenants
    UNION ALL
    SELECT 'images', COUNT(*) FROM images
    UNION ALL
    SELECT 'sessions', COUNT(*) FROM sessions
    UNION ALL
    SELECT 'characters', COUNT(*) FROM characters
    UNION ALL
    SELECT 'usage_logs', COUNT(*) FROM usage_logs
);

Clean Old Usage Logs

DELETE FROM usage_logs
WHERE timestamp < datetime('now', '-90 days');

Find Orphaned Images

SELECT id, r2_key
FROM images
WHERE tenant_id NOT IN (SELECT tenant_id FROM tenants);

Inactive Sessions

UPDATE sessions
SET is_active = 0
WHERE last_activity_at < datetime('now', '-24 hours')
  AND is_active = 1;

Usage by Tenant

SELECT
    t.tenant_id,
    t.name,
    COUNT(i.id) as image_count,
    COALESCE(SUM(i.size_bytes), 0) / 1048576.0 as storage_mb,
    t.monthly_quota_mb
FROM tenants t
LEFT JOIN images i ON i.tenant_id = t.tenant_id
GROUP BY t.tenant_id
ORDER BY storage_mb DESC;

Performance Tuning

Index Optimization

Ensure indexes exist for common queries:

-- Verify indexes
SELECT * FROM sqlite_master WHERE type='index';

-- Add missing indexes
CREATE INDEX IF NOT EXISTS idx_images_prompt ON images(prompt);
CREATE INDEX IF NOT EXISTS idx_images_operation ON images(operation_type);

Query Analysis

-- Analyze query performance
EXPLAIN QUERY PLAN
SELECT * FROM images
WHERE tenant_id = 'acme-corp'
  AND prompt LIKE '%sunset%'
ORDER BY created_at DESC
LIMIT 20;

Vacuum Database

-- Reclaim space after deletions
VACUUM;

Next Steps

• Tenant Management →
• Operations →
• Scaling →