Database Schema

┌─────────────────┐
│  auth.tenants   │
└────────┬────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   auth.users    │◄───────│  auth.api_keys   │
└────────┬────────┘   N:1  └──────────────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   public.users  │◄───────│   sessions       │
└────────┬────────┘   1:N  └──────────────────┘
         │
         │ 1:N
         ▼
┌──────────────────────────┐
│   task_executions        │
│  (workflow_id = PK)      │
└──────────┬───────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐        ┌──────────────────┐
│   agent_executions       │◄───────│  tool_executions │
└──────────┬───────────────┘   1:N  └──────────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐
│     tool_calls           │
└──────────────────────────┘

┌──────────────────────────┐        ┌──────────────────┐
│   event_logs             │        │  token_usage     │
│  (workflow_id)           │        └──────────────────┘
└──────────────────────────┘

┌──────────────────────────┐        ┌───────────────────┐
│  usage_daily_aggregates  │        │  learning_cases   │
└──────────────────────────┘        └───────────────────┘

-- Get all active tenants
SELECT id, name, plan, token_limit
FROM auth.tenants
WHERE is_active = true;

-- Check token usage
SELECT name, monthly_token_usage, token_limit,
       (monthly_token_usage::FLOAT / token_limit * 100) as usage_percentage
FROM auth.tenants
WHERE monthly_token_usage > token_limit * 0.8;

-- Get all users in a tenant
SELECT u.email, u.username, u.role, u.last_login
FROM auth.users u
WHERE u.tenant_id = '...'
  AND u.is_active = true
ORDER BY u.last_login DESC NULLS LAST;

-- Find inactive users
SELECT email, created_at, last_login
FROM auth.users
WHERE last_login < NOW() - INTERVAL '90 days'
   OR last_login IS NULL;

-- List active API keys
SELECT key_prefix, name, last_used, expires_at
FROM auth.api_keys
WHERE is_active = true
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY last_used DESC NULLS LAST;

-- Find unused keys
SELECT key_prefix, name, created_at, last_used
FROM auth.api_keys
WHERE last_used IS NULL
  AND created_at < NOW() - INTERVAL '30 days';

-- Check expiring keys
SELECT key_prefix, name, expires_at
FROM auth.api_keys
WHERE expires_at BETWEEN NOW() AND NOW() + INTERVAL '7 days'
  AND is_active = true;

-- Revoke all tokens for a user
UPDATE auth.refresh_tokens
SET revoked = true, revoked_at = NOW()
WHERE user_id = '...'
  AND revoked = false;

-- Clean up expired tokens
DELETE FROM auth.refresh_tokens
WHERE expires_at < NOW() - INTERVAL '7 days';

-- Recent security events
SELECT event_type, user_id, ip_address, created_at
FROM auth.audit_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 100;

-- Failed login attempts
SELECT user_id, ip_address, COUNT(*) as attempts
FROM auth.audit_logs
WHERE event_type = 'login_failed'
  AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id, ip_address
HAVING COUNT(*) > 5;

-- Active sessions
SELECT id, user_id, tokens_used, token_budget, created_at
FROM sessions
WHERE expires_at > NOW()
  OR expires_at IS NULL
ORDER BY created_at DESC;

-- Session token usage
SELECT
    COUNT(*) as session_count,
    AVG(tokens_used) as avg_tokens,
    SUM(tokens_used) as total_tokens
FROM sessions
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Recent tasks for user
SELECT workflow_id, query, status, duration_ms, total_cost_usd
FROM task_executions
WHERE user_id = '...'
ORDER BY started_at DESC
LIMIT 20;

-- Failed tasks analysis
SELECT
    DATE(started_at) as date,
    COUNT(*) as failed_count,
    AVG(duration_ms) as avg_duration
FROM task_executions
WHERE status = 'FAILED'
  AND started_at > NOW() - INTERVAL '7 days'
GROUP BY DATE(started_at)
ORDER BY date DESC;

-- Cost analysis
SELECT
    user_id,
    COUNT(*) as task_count,
    SUM(total_cost_usd) as total_cost,
    AVG(total_cost_usd) as avg_cost,
    SUM(total_tokens) as total_tokens
FROM task_executions
WHERE started_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_cost DESC;

-- Agent execution chain for a task
SELECT agent_id, state, duration_ms, tokens_used
FROM agent_executions
WHERE workflow_id = '...'
ORDER BY created_at;

-- Agent performance metrics
SELECT
    agent_id,
    COUNT(*) as execution_count,
    AVG(duration_ms) as avg_duration,
    AVG(tokens_used) as avg_tokens
FROM agent_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY agent_id
ORDER BY execution_count DESC;

-- Tool usage statistics
SELECT
    tool_name,
    COUNT(*) as invocation_count,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    AVG(duration_ms) as avg_duration
FROM tool_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY tool_name
ORDER BY invocation_count DESC;

-- Failed tool executions
SELECT tool_name, error, created_at
FROM tool_executions
WHERE success = false
  AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

-- Tool performance
SELECT
    tool_name,
    AVG(duration_ms) as avg_ms,
    MAX(duration_ms) as max_ms,
    STDDEV(duration_ms) as stddev_ms
FROM tool_executions
WHERE success = true
GROUP BY tool_name
ORDER BY avg_ms DESC;

-- Get all events for a task
SELECT type, agent_id, message, timestamp
FROM event_logs
WHERE workflow_id = '...'
ORDER BY timestamp;

-- Event frequency analysis
SELECT
    type,
    COUNT(*) as event_count,
    DATE_TRUNC('hour', timestamp) as hour
FROM event_logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY type, hour
ORDER BY hour DESC, event_count DESC;

-- Token usage by provider
SELECT
    provider,
    model,
    COUNT(*) as call_count,
    SUM(total_tokens) as total_tokens,
    SUM(cost_usd) as total_cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY provider, model
ORDER BY total_cost DESC;

-- Daily token trend
SELECT
    DATE(created_at) as date,
    SUM(total_tokens) as tokens,
    SUM(cost_usd) as cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- User usage summary
SELECT date, total_tasks, successful_tasks, total_cost_usd
FROM usage_daily_aggregates
WHERE user_id = '...'
  AND date > CURRENT_DATE - INTERVAL '30 days'
ORDER BY date DESC;

-- Aggregate metrics
SELECT
    SUM(total_tasks) as total_tasks,
    SUM(total_cost_usd) as total_cost,
    AVG(cache_hit_rate) as avg_cache_rate
FROM usage_daily_aggregates
WHERE date > CURRENT_DATE - INTERVAL '7 days';

CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- Manual call
SELECT update_daily_aggregate(
    '00000000-0000-0000-0000-000000000002',
    '2025-10-22'
);

-- Task success rate by mode
SELECT
    mode,
    COUNT(*) as total,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) as completed,
    ROUND(100.0 * COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) / COUNT(*), 2) as success_rate
FROM task_executions
WHERE started_at > NOW() - INTERVAL '7 days'
GROUP BY mode;

-- Average execution time by complexity
SELECT
    CASE
        WHEN complexity_score < 0.3 THEN 'Low'
        WHEN complexity_score < 0.7 THEN 'Medium'
        ELSE 'High'
    END as complexity,
    COUNT(*) as task_count,
    AVG(duration_ms) as avg_ms,
    AVG(total_cost_usd) as avg_cost
FROM task_executions
WHERE complexity_score IS NOT NULL
GROUP BY complexity;

-- Top cost users
SELECT
    u.email,
    COUNT(t.id) as task_count,
    SUM(t.total_cost_usd) as total_cost,
    AVG(t.total_cost_usd) as avg_cost
FROM task_executions t
JOIN users u ON t.user_id = u.id
WHERE t.started_at > NOW() - INTERVAL '30 days'
GROUP BY u.email
ORDER BY total_cost DESC
LIMIT 10;

-- Cost breakdown by model
SELECT
    tu.provider,
    tu.model,
    COUNT(*) as usage_count,
    SUM(tu.total_tokens) as total_tokens,
    SUM(tu.cost_usd) as total_cost,
    AVG(tu.cost_usd) as avg_cost_per_call
FROM token_usage tu
WHERE tu.created_at > NOW() - INTERVAL '7 days'
GROUP BY tu.provider, tu.model
ORDER BY total_cost DESC;

-- Slow queries (tasks > 60 seconds)
SELECT
    workflow_id,
    query,
    duration_ms,
    agents_used,
    tools_invoked
FROM task_executions
WHERE duration_ms > 60000
  AND started_at > NOW() - INTERVAL '24 hours'
ORDER BY duration_ms DESC;

-- Tool execution success rate
SELECT
    tool_name,
    COUNT(*) as total,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    ROUND(100.0 * COUNT(CASE WHEN success THEN 1 END) / COUNT(*), 2) as success_rate,
    AVG(duration_ms) as avg_duration
FROM tool_executions
GROUP BY tool_name
ORDER BY total DESC;

-- Monthly partitioning for task_executions
CREATE TABLE task_executions_2025_10 PARTITION OF task_executions
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

-- Delete old events (run monthly)
DELETE FROM event_logs
WHERE created_at < NOW() - INTERVAL '90 days';

-- Archive old tasks (run annually)
INSERT INTO task_executions_archive
SELECT * FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';

DELETE FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';

EXPLAIN ANALYZE
SELECT * FROM task_executions
WHERE user_id = '...'
  AND started_at > NOW() - INTERVAL '7 days';

CREATE INDEX idx_task_user_date_status
ON task_executions(user_id, started_at, status)
INCLUDE (total_cost_usd, duration_ms);

DB_MAX_OPEN_CONNS=50
DB_MAX_IDLE_CONNS=10

-- Manual vacuum (run weekly)
VACUUM ANALYZE task_executions;
VACUUM ANALYZE event_logs;

-- Configure autovacuum
ALTER TABLE task_executions SET (autovacuum_vacuum_threshold = 1000);

-- Update statistics
ANALYZE task_executions;

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;