tenants:
  - cp_id: "CP1-UK-0001"
    api_key_hash: "sha256:..."
    rate_limit: "1000 req/min"
    endpoints:
      - /auth/verify
      - /routing/request
      - /emergency/location

  - cp_id: "CP2-US-0042"
    api_key_hash: "sha256:..."
    rate_limit: "5000 req/min"
    endpoints:
      - /auth/verify
      - /auth/tokens
      - /routing/request

-- CP Configuration
CREATE TABLE cp_tenants (
    cp_id VARCHAR(50) PRIMARY KEY,
    cp_name VARCHAR(255) NOT NULL,
    private_key_encrypted TEXT NOT NULL,  -- Ed25519 private key
    public_key TEXT NOT NULL,              -- Ed25519 public key
    auth_mode VARCHAR(20) NOT NULL,        -- 'direct_query' or 'token_pool'
    api_key_hash VARCHAR(128) NOT NULL,
    rate_limit_per_min INTEGER DEFAULT 1000,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'active'    -- 'active', 'suspended', 'terminated'
);

-- Authentication Logs
CREATE TABLE auth_logs (
    log_id BIGSERIAL PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    call_reference VARCHAR(100) NOT NULL,
    caller_id VARCHAR(20) NOT NULL,
    called_id VARCHAR(20) NOT NULL,
    originating_cp VARCHAR(50) NOT NULL,
    verification_result VARCHAR(20) NOT NULL, -- 'verified', 'rejected', 'error'
    response_time_ms INTEGER NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_auth_logs_cp_timestamp ON auth_logs (cp_id, timestamp);
CREATE INDEX idx_auth_logs_call_ref ON auth_logs (call_reference);

-- Local Directory Cache
CREATE TABLE directory_cache (
    phone_number VARCHAR(20) PRIMARY KEY,
    current_cp VARCHAR(50) NOT NULL,
    pstn2_enabled BOOLEAN DEFAULT true,
    routing_endpoint VARCHAR(255) NOT NULL,
    last_updated TIMESTAMP NOT NULL,
    cache_expires TIMESTAMP NOT NULL
);

CREATE INDEX idx_directory_cache_cp ON directory_cache (current_cp);

-- CP Number Assignments
CREATE TABLE cp_numbers (
    phone_number VARCHAR(20) PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    routing_endpoint VARCHAR(255) NOT NULL,
    emergency_enabled BOOLEAN DEFAULT true,
    assigned_date TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_cp_numbers_cp_id ON cp_numbers (cp_id);

-- Emergency Call Locations
CREATE TABLE emergency_locations (
    call_reference VARCHAR(100) PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    caller_id VARCHAR(20) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    accuracy_meters DECIMAL(10, 2),
    altitude_meters DECIMAL(10, 2),
    timestamp TIMESTAMP NOT NULL,
    psap_notified BOOLEAN DEFAULT false,
    psap_id VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL  -- 24 hours after call
);

CREATE INDEX idx_emergency_locations_cp_timestamp ON emergency_locations (cp_id, timestamp);

-- Active Calls
CREATE TABLE active_calls (
    call_reference VARCHAR(100) PRIMARY KEY,
    originating_cp VARCHAR(50) NOT NULL,
    terminating_cp VARCHAR(50) NOT NULL,
    caller_id VARCHAR(20) NOT NULL,
    called_id VARCHAR(20) NOT NULL,
    routing_method VARCHAR(20) NOT NULL, -- 'direct', 'relay', 'pstn_fallback'
    call_started TIMESTAMP DEFAULT NOW(),
    call_ended TIMESTAMP,
    duration_seconds INTEGER
);

CREATE INDEX idx_active_calls_orig_cp ON active_calls (originating_cp, call_started);
CREATE INDEX idx_active_calls_term_cp ON active_calls (terminating_cp, call_started);

-- Usage Metrics (Aggregated)
CREATE TABLE usage_metrics (
    metric_id BIGSERIAL PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    metric_type VARCHAR(50) NOT NULL, -- 'auth_verify', 'directory_lookup', 'emergency', 'routing'
    metric_count INTEGER NOT NULL,
    period_start TIMESTAMP NOT NULL,
    period_end TIMESTAMP NOT NULL
);

CREATE INDEX idx_usage_metrics_cp_period ON usage_metrics (cp_id, period_start);

-- Billing Records
CREATE TABLE billing_records (
    invoice_id BIGSERIAL PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    billing_period_start TIMESTAMP NOT NULL,
    billing_period_end TIMESTAMP NOT NULL,
    total_calls INTEGER NOT NULL,
    total_minutes DECIMAL(12, 2),
    base_fee DECIMAL(10, 2) NOT NULL,
    usage_fee DECIMAL(10, 2) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'GBP',
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'paid', 'overdue'
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_billing_records_cp_period ON billing_records (cp_id, billing_period_start);

-- Enable Row-Level Security
CREATE TABLE cp_tenants (
    cp_id VARCHAR(50) PRIMARY KEY,
    cp_name VARCHAR(255) NOT NULL,
    private_key_encrypted TEXT NOT NULL,
    public_key TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE call_records (
    call_id BIGSERIAL PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL REFERENCES cp_tenants(cp_id),
    call_reference VARCHAR(100) NOT NULL,
    caller_id VARCHAR(20) NOT NULL,
    called_id VARCHAR(20) NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW()
);

-- Enable RLS on call_records
ALTER TABLE call_records ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own tenant's data
CREATE POLICY tenant_isolation_policy ON call_records
    USING (cp_id = current_setting('app.current_tenant')::VARCHAR);

-- Set current tenant in session
SET app.current_tenant = 'CP1-UK-0001';

-- Now queries only return rows for CP1-UK-0001
SELECT * FROM call_records;  -- Only sees CP1's records

// middleware/tenant-context.ts
import { Request, Response, NextFunction } from 'express';
import { db } from '../db';

export async function tenantContext(
  req: Request,
  res: Response,
  next: NextFunction
) {
  // Extract API key from Authorization header
  const apiKey = req.headers.authorization?.replace('Bearer ', '');

  if (!apiKey) {
    return res.status(401).json({ error: 'Missing API key' });
  }

  // Look up tenant by API key
  const tenant = await db.query(
    'SELECT cp_id FROM cp_tenants WHERE api_key_hash = $1 AND status = $2',
    [hashApiKey(apiKey), 'active']
  );

  if (tenant.rows.length === 0) {
    return res.status(401).json({ error: 'Invalid API key' });
  }

  // Set tenant context for this request
  const cpId = tenant.rows[0].cp_id;
  await db.query("SELECT set_config('app.current_tenant', $1, false)", [cpId]);

  // Store tenant ID in request for later use
  req.tenantId = cpId;

  next();
}

// Apply middleware to all API routes
app.use('/api/v1', tenantContext);

// routes/auth.ts
import { Router } from 'express';
import { db } from '../db';
import { signRequest } from '../crypto';

const router = Router();

router.post('/auth/verify', async (req, res) => {
  const { caller_id, called_id, call_reference, originating_cp } = req.body;

  // Tenant context already set by middleware
  const cpId = req.tenantId;

  // Get tenant's private key
  const tenant = await db.query(
    'SELECT private_key_encrypted FROM cp_tenants WHERE cp_id = $1',
    [cpId]
  );

  const privateKey = decrypt(tenant.rows[0].private_key_encrypted);

  // Sign authentication request
  const signature = signRequest(privateKey, {
    caller_id,
    called_id,
    call_reference,
    originating_cp,
    timestamp: new Date().toISOString(),
  });

  // Query originating CP for verification
  const verification = await queryOriginatingCP(
    originating_cp,
    caller_id,
    called_id,
    call_reference,
    signature
  );

  // Log the authentication attempt (automatically isolated by RLS)
  await db.query(
    `INSERT INTO auth_logs
     (cp_id, call_reference, caller_id, called_id, originating_cp, verification_result, response_time_ms)
     VALUES ($1, $2, $3, $4, $5, $6, $7)`,
    [cpId, call_reference, caller_id, called_id, originating_cp,
     verification.verified ? 'verified' : 'rejected', verification.response_time]
  );

  res.json({
    verified: verification.verified,
    verification_method: 'direct_query',
    response_time_ms: verification.response_time,
    timestamp: new Date().toISOString(),
  });
});

export default router;

# Kubernetes deployment
apiVersion: apps/v1
kind: Deployment
metadata:
  name: map-api-server
spec:
  replicas: 10  # Scale to 10+ instances
  selector:
    matchLabels:
      app: map-api
  template:
    metadata:
      labels:
        app: map-api
    spec:
      containers:
      - name: api-server
        image: map-api:latest
        resources:
          requests:
            memory: "512Mi"
            cpu: "500m"
          limits:
            memory: "1Gi"
            cpu: "1000m"
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: url
---
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: map-api-hpa
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: Deployment
    name: map-api-server
  minReplicas: 5
  maxReplicas: 50
  metrics:
  - type: Resource
    resource:
      name: cpu
      target:
        type: Utilization
        averageUtilization: 70

-- Critical indexes for multi-tenant queries
CREATE INDEX idx_call_records_cp_timestamp ON call_records(cp_id, timestamp);
CREATE INDEX idx_auth_logs_cp_timestamp ON auth_logs(cp_id, timestamp);
CREATE INDEX idx_cp_numbers_cp_id ON cp_numbers(cp_id);
CREATE INDEX idx_directory_cache_number ON directory_cache(phone_number);

-- Partial indexes for active tenants only
CREATE INDEX idx_active_tenants ON cp_tenants(cp_id) WHERE status = 'active';

-- Bad: Full table scan
SELECT * FROM call_records WHERE caller_id = '+441234567890';

-- Good: Uses tenant context and index
SELECT * FROM call_records
WHERE cp_id = 'CP1-UK-0001'
  AND caller_id = '+441234567890'
  AND timestamp > NOW() - INTERVAL '7 days';

// pg-pool configuration
const pool = new Pool({
  max: 100,                    // Maximum connections
  min: 10,                     // Minimum idle connections
  idleTimeoutMillis: 30000,    // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect in 2s
});

import crypto from 'crypto';

function generateApiKey(): { key: string; hash: string } {
  // Generate 256-bit random key
  const key = crypto.randomBytes(32).toString('base64');

  // Hash for storage (never store plaintext)
  const hash = crypto.createHash('sha256').update(key).digest('hex');

  return { key, hash };
}

// Usage
const { key, hash } = generateApiKey();
// Give 'key' to customer (one time only)
// Store 'hash' in database

import { createCipheriv, createDecipheriv, randomBytes } from 'crypto';

function encryptPrivateKey(privateKey: string, masterKey: string): string {
  const iv = randomBytes(16);
  const cipher = createCipheriv('aes-256-gcm', Buffer.from(masterKey, 'hex'), iv);

  let encrypted = cipher.update(privateKey, 'utf8', 'base64');
  encrypted += cipher.final('base64');

  const authTag = cipher.getAuthTag();

  // Return: IV + AuthTag + Encrypted data
  return iv.toString('base64') + ':' + authTag.toString('base64') + ':' + encrypted;
}

function decryptPrivateKey(encryptedKey: string, masterKey: string): string {
  const [ivB64, authTagB64, encrypted] = encryptedKey.split(':');

  const iv = Buffer.from(ivB64, 'base64');
  const authTag = Buffer.from(authTagB64, 'base64');
  const decipher = createDecipheriv('aes-256-gcm', Buffer.from(masterKey, 'hex'), iv);

  decipher.setAuthTag(authTag);

  let decrypted = decipher.update(encrypted, 'base64', 'utf8');
  decrypted += decipher.final('utf8');

  return decrypted;
}

CREATE TABLE audit_logs (
    log_id BIGSERIAL PRIMARY KEY,
    cp_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(50),  -- If user-level access
    action VARCHAR(100) NOT NULL,  -- 'auth_verify', 'config_update', 'key_rotate'
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(100),
    old_value JSONB,
    new_value JSONB,
    ip_address INET NOT NULL,
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_logs_cp_timestamp ON audit_logs (cp_id, timestamp);
CREATE INDEX idx_audit_logs_action ON audit_logs (action, timestamp);

import { Counter, Histogram, Gauge } from 'prom-client';

// API requests per tenant
const apiRequests = new Counter({
  name: 'map_api_requests_total',
  help: 'Total API requests by tenant and endpoint',
  labelNames: ['cp_id', 'endpoint', 'status'],
});

// Response time per tenant
const responseTime = new Histogram({
  name: 'map_api_response_time_seconds',
  help: 'API response time by tenant and endpoint',
  labelNames: ['cp_id', 'endpoint'],
  buckets: [0.1, 0.3, 0.5, 1, 2, 5],
});

// Active tenants
const activeTenants = new Gauge({
  name: 'map_active_tenants',
  help: 'Number of active tenant CPs',
});

// Usage in middleware
app.use((req, res, next) => {
  const start = Date.now();

  res.on('finish', () => {
    const duration = (Date.now() - start) / 1000;

    apiRequests.inc({
      cp_id: req.tenantId,
      endpoint: req.route.path,
      status: res.statusCode,
    });

    responseTime.observe({
      cp_id: req.tenantId,
      endpoint: req.route.path,
    }, duration);
  });

  next();
});

// Onboarding API endpoint
async function onboardNewCP(req: Request, res: Response) {
  const { cp_name, contact_email, country, auth_mode } = req.body;

  // 1. Generate unique CP ID
  const cp_id = generateCPID(country); // e.g., "CP1-UK-0123"

  // 2. Generate Ed25519 key pair
  const { publicKey, privateKey } = generateKeyPair();

  // 3. Encrypt private key with master key
  const encryptedPrivateKey = encryptPrivateKey(privateKey, MASTER_KEY);

  // 4. Generate API key
  const { key: apiKey, hash: apiKeyHash } = generateApiKey();

  // 5. Insert into database
  await db.query(
    `INSERT INTO cp_tenants
     (cp_id, cp_name, private_key_encrypted, public_key, api_key_hash, auth_mode, contact_email)
     VALUES ($1, $2, $3, $4, $5, $6, $7)`,
    [cp_id, cp_name, encryptedPrivateKey, publicKey, apiKeyHash, auth_mode, contact_email]
  );

  // 6. Create default configuration
  await createDefaultConfig(cp_id);

  // 7. Send welcome email with credentials
  await sendWelcomeEmail(contact_email, {
    cp_id,
    api_key: apiKey,  // Show once, never again
    public_key: publicKey,
    api_endpoint: 'https://api.map.example.com/v1',
    documentation_url: 'https://docs.map.example.com',
  });

  // 8. Return response (without private key or API key)
  res.json({
    success: true,
    cp_id,
    status: 'active',
    message: 'Credentials sent to email. Please store API key securely.',
  });
}