Multi-tenancy database patterns and strategies

-- Tenant context middleware (application sets this per request)
/*
// Node.js example
app.use(async (req, res, next) => {
  const tenantSlug = req.hostname.split('.')[0]; // subdomain
  const tenant = await db.query(
    'SELECT id FROM tenants WHERE slug = $1',
    [tenantSlug]
  );

  if (!tenant.rows[0]) {
    return res.status(404).send('Tenant not found');
  }

  // Set tenant context for this connection
  await req.dbClient.query(
    'SET LOCAL app.current_tenant_id = $1',
    [tenant.rows[0].id]
  );

  next();
});
*/

-- Tenant-specific connection pooling
/*
const pools = {};

function getTenantPool(tenantSlug) {
  if (!pools[tenantSlug]) {
    pools[tenantSlug] = new Pool({
      ...dbConfig,
      options: `-c search_path=${tenantSlug},public`
    });
  }
  return pools[tenantSlug];
}
*/

-- Cross-tenant queries (admin only)
CREATE VIEW all_tenants_users AS
SELECT
  t.name AS tenant_name,
  t.slug AS tenant_slug,
  u.id,
  u.username,
  u.email,
  u.created_at
FROM tenants t
JOIN users u ON t.id = u.tenant_id;

-- Tenant metrics
SELECT
  t.name AS tenant,
  COUNT(DISTINCT u.id) AS user_count,
  COUNT(DISTINCT o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_revenue,
  pg_size_pretty(
    pg_total_relation_size('users') *
    COUNT(DISTINCT u.id)::FLOAT /
    NULLIF((SELECT COUNT(*) FROM users), 0)
  ) AS estimated_storage
FROM tenants t
LEFT JOIN users u ON t.id = u.tenant_id
LEFT JOIN orders o ON t.id = o.tenant_id
GROUP BY t.id, t.name
ORDER BY total_revenue DESC;

-- Tenant data export
COPY (
  SELECT *
  FROM users
  WHERE tenant_id = 123
) TO '/backup/tenant_123_users.csv' WITH CSV HEADER;

-- Tenant data deletion (GDPR compliance)
BEGIN;

DELETE FROM orders WHERE tenant_id = 123;
DELETE FROM users WHERE tenant_id = 123;
DELETE FROM tenants WHERE id = 123;

COMMIT;

-- Or with CASCADE
DELETE FROM tenants WHERE id = 123;  -- Cascades to users, orders

-- Tenant isolation verification
CREATE OR REPLACE FUNCTION verify_tenant_isolation()
RETURNS TABLE(table_name TEXT, missing_policy BOOLEAN) AS $$
BEGIN
  RETURN QUERY
  SELECT
    t.tablename::TEXT,
    NOT EXISTS (
      SELECT 1 FROM pg_policies p
      WHERE p.tablename = t.tablename
        AND p.policyname LIKE 'tenant_isolation%'
    ) AS missing_policy
  FROM pg_tables t
  WHERE t.schemaname = 'public'
    AND EXISTS (
      SELECT 1 FROM information_schema.columns c
      WHERE c.table_name = t.tablename
        AND c.column_name = 'tenant_id'
    );
END;
$$ LANGUAGE plpgsql;

-- Check for tables missing RLS policies
SELECT * FROM verify_tenant_isolation()
WHERE missing_policy = true;

-- Tenant-aware backup script
/*
#!/bin/bash
for tenant_id in $(psql -t -c "SELECT id FROM tenants"); do
  pg_dump --data-only --table=users --table=orders     --where="tenant_id=$tenant_id"     mydb > "tenant_${tenant_id}_backup.sql"
done
*/

-- Schema-based tenant cleanup
DROP SCHEMA tenant_123 CASCADE;
DELETE FROM tenants WHERE slug = 'tenant_123';

-- Tenant onboarding
CREATE OR REPLACE FUNCTION onboard_tenant(
  tenant_name VARCHAR,
  tenant_slug VARCHAR,
  admin_email VARCHAR
)
RETURNS INT AS $$
DECLARE
  new_tenant_id INT;
  new_user_id INT;
BEGIN
  -- Create tenant
  INSERT INTO tenants (name, slug)
  VALUES (tenant_name, tenant_slug)
  RETURNING id INTO new_tenant_id;

  -- Create admin user
  INSERT INTO users (tenant_id, username, email, role)
  VALUES (new_tenant_id, 'admin', admin_email, 'admin')
  RETURNING id INTO new_user_id;

  -- Create default data
  INSERT INTO settings (tenant_id, key, value)
  VALUES
    (new_tenant_id, 'theme', 'light'),
    (new_tenant_id, 'timezone', 'UTC');

  RETURN new_tenant_id;
END;
$$ LANGUAGE plpgsql;

-- Use
SELECT onboard_tenant('New Corp', 'newcorp', 'admin@newcorp.com');

-- Tenant query performance
-- Ensure composite indexes include tenant_id first
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);
CREATE INDEX idx_orders_tenant_date ON orders(tenant_id, created_at DESC);

-- Partition by tenant (for very large deployments)
CREATE TABLE orders (
  id BIGSERIAL,
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  total DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, tenant_id)
) PARTITION BY LIST (tenant_id);

CREATE TABLE orders_tenant_1 PARTITION OF orders
  FOR VALUES IN (1);

CREATE TABLE orders_tenant_2 PARTITION OF orders
  FOR VALUES IN (2);

-- Partition pruning benefits large tenants