keys-for-all/docs/DATABASE_SCHEMA.md

Keys for All - Database Schema

Database: PostgreSQL 14+

Tables

apps

Registered applications that can use the Keys for All system.

CREATE TABLE apps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id VARCHAR(255) UNIQUE NOT NULL, -- e.g., "com.voiceuwu.app"
    name VARCHAR(255) NOT NULL,
    api_key VARCHAR(255) UNIQUE NOT NULL,
    webhook_url VARCHAR(500),
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_apps_api_key ON apps(api_key);

keys

All generated license keys.

CREATE TABLE keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key VARCHAR(19) UNIQUE NOT NULL, -- XXXX-XXXX-XXXX-XXXX format
    app_id UUID REFERENCES apps(id),
    level INTEGER NOT NULL DEFAULT 1, -- 1 or 2
    type VARCHAR(50) NOT NULL, -- 'purchase', 'promotional', 'community'
    status VARCHAR(50) NOT NULL DEFAULT 'unused', -- 'unused', 'active', 'expired', 'revoked'
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE,
    first_activated_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_keys_key ON keys(key);
CREATE INDEX idx_keys_status ON keys(status);
CREATE INDEX idx_keys_app_id ON keys(app_id);

activations

Track where and when keys are activated.

CREATE TABLE activations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key_id UUID REFERENCES keys(id),
    app_id UUID REFERENCES apps(id),
    device_id VARCHAR(255),
    device_info JSONB DEFAULT '{}',
    ip_address INET,
    activated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activations_key_id ON activations(key_id);
CREATE INDEX idx_activations_device_id ON activations(device_id);

purchases

Track purchases that generated keys.

CREATE TABLE purchases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id UUID REFERENCES apps(id),
    transaction_id VARCHAR(255) UNIQUE NOT NULL,
    product_id VARCHAR(255) NOT NULL,
    keys_granted INTEGER NOT NULL,
    price DECIMAL(10, 2),
    currency VARCHAR(3) DEFAULT 'USD',
    receipt_data TEXT,
    verified_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_purchases_transaction_id ON purchases(transaction_id);
CREATE INDEX idx_purchases_app_id ON purchases(app_id);

purchase_keys

Link between purchases and the keys they generated.

CREATE TABLE purchase_keys (
    purchase_id UUID REFERENCES purchases(id),
    key_id UUID REFERENCES keys(id),
    PRIMARY KEY (purchase_id, key_id)
);

shares

Track key sharing between users.

CREATE TABLE shares (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    share_code VARCHAR(50) UNIQUE NOT NULL,
    from_key_id UUID REFERENCES keys(id),
    to_key_id UUID REFERENCES keys(id),
    keys_shared INTEGER NOT NULL DEFAULT 1,
    message TEXT,
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'claimed', 'expired'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE,
    claimed_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_shares_code ON shares(share_code);
CREATE INDEX idx_shares_status ON shares(status);

community_pool

Keys donated to the community pool.

CREATE TABLE community_pool (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key_id UUID REFERENCES keys(id) UNIQUE,
    donor_message TEXT,
    anonymous BOOLEAN DEFAULT true,
    donated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    claimed_at TIMESTAMP WITH TIME ZONE,
    claimed_by_request_id UUID
);

CREATE INDEX idx_pool_claimed ON community_pool(claimed_at) WHERE claimed_at IS NULL;

community_requests

Requests for keys from the community pool.

CREATE TABLE community_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id UUID REFERENCES apps(id),
    email VARCHAR(255) NOT NULL,
    reason TEXT,
    status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'approved', 'fulfilled', 'rejected'
    ip_address INET,
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    fulfilled_at TIMESTAMP WITH TIME ZONE,
    key_id UUID REFERENCES keys(id)
);

CREATE INDEX idx_requests_status ON community_requests(status);
CREATE INDEX idx_requests_email ON community_requests(email);

analytics_events

Track key usage events for analytics.

CREATE TABLE analytics_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id UUID REFERENCES apps(id),
    event_type VARCHAR(50) NOT NULL, -- 'activation', 'validation', 'share', etc.
    key_id UUID REFERENCES keys(id),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_analytics_app_id ON analytics_events(app_id);
CREATE INDEX idx_analytics_event_type ON analytics_events(event_type);
CREATE INDEX idx_analytics_created_at ON analytics_events(created_at);

rate_limits

Track API rate limiting.

CREATE TABLE rate_limits (
    id VARCHAR(255) PRIMARY KEY, -- "{api_key}:{endpoint}" or "{ip}:{endpoint}"
    count INTEGER NOT NULL DEFAULT 0,
    window_start TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_rate_limits_window ON rate_limits(window_start);

Views

v_active_keys

Active keys with full information.

CREATE VIEW v_active_keys AS
SELECT 
    k.*,
    a.name as app_name,
    COUNT(DISTINCT act.id) as activation_count,
    COUNT(DISTINCT s.id) as share_count
FROM keys k
JOIN apps a ON k.app_id = a.id
LEFT JOIN activations act ON k.id = act.key_id
LEFT JOIN shares s ON k.id = s.from_key_id
WHERE k.status = 'active'
GROUP BY k.id, a.name;

v_community_pool_stats

Community pool statistics.

CREATE VIEW v_community_pool_stats AS
SELECT 
    COUNT(*) FILTER (WHERE claimed_at IS NULL) as available_keys,
    COUNT(*) FILTER (WHERE claimed_at IS NOT NULL) as distributed_keys,
    COUNT(DISTINCT DATE(donated_at)) as active_days,
    COUNT(*) FILTER (WHERE donated_at > CURRENT_TIMESTAMP - INTERVAL '24 hours') as donations_today
FROM community_pool;

Functions

generate_key()

Generate a random key in XXXX-XXXX-XXXX-XXXX format.

CREATE OR REPLACE FUNCTION generate_key() RETURNS VARCHAR AS $$
DECLARE
    chars VARCHAR := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    result VARCHAR := '';
    i INTEGER;
BEGIN
    FOR i IN 1..16 LOOP
        IF i IN (5, 10, 15) THEN
            result := result || '-';
        END IF;
        result := result || substr(chars, floor(random() * length(chars) + 1)::int, 1);
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

cleanup_expired()

Clean up expired shares and keys.

CREATE OR REPLACE FUNCTION cleanup_expired() RETURNS void AS $$
BEGIN
    -- Expire unclaimed shares
    UPDATE shares 
    SET status = 'expired' 
    WHERE status = 'pending' 
    AND expires_at < CURRENT_TIMESTAMP;
    
    -- Expire unused keys
    UPDATE keys 
    SET status = 'expired' 
    WHERE status = 'unused' 
    AND expires_at < CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

Indexes for Performance

-- Composite indexes for common queries
CREATE INDEX idx_keys_app_status ON keys(app_id, status);
CREATE INDEX idx_activations_key_device ON activations(key_id, device_id);
CREATE INDEX idx_analytics_app_date ON analytics_events(app_id, created_at);

-- Partial indexes for active records
CREATE INDEX idx_active_keys ON keys(key) WHERE status = 'active';
CREATE INDEX idx_pending_requests ON community_requests(created_at) WHERE status = 'pending';

Scheduled Jobs

Daily Cleanup

-- Run daily to clean up expired records
SELECT cleanup_expired();

-- Archive old analytics events
INSERT INTO analytics_events_archive 
SELECT * FROM analytics_events 
WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '90 days';

DELETE FROM analytics_events 
WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '90 days';

Hourly Stats Update

-- Update materialized view for dashboard
REFRESH MATERIALIZED VIEW mv_dashboard_stats;