Multi-Tenant SaaS Applications

-- Tenant-isolated data
CREATE TABLE saas.customer_data (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  customer_id TEXT NOT NULL,
  entity_type TEXT,
  entity_data JSON,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (TYPE = 'USER', FLUSH_POLICY = 'rows:10000,interval:600');
 
-- Cross-tenant analytics (aggregated)
CREATE TABLE saas.analytics (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  metric_name TEXT,
  metric_value DOUBLE,
  tenant_id TEXT,
  timestamp TIMESTAMP DEFAULT NOW()
) WITH (TYPE = 'SHARED', FLUSH_POLICY = 'interval:3600');
 
-- Create service account per tenant
CREATE USER 'tenant_acme'
  WITH PASSWORD 'SecureKey123!'
  ROLE service;

await client.query(
  'INSERT INTO saas.customer_data (customer_id, entity_type, entity_data) VALUES ($1, $2, $3)',
  [tenantId, 'invoice', JSON.stringify(invoice)]
);
 
const rows = await client.query(
  'SELECT * FROM saas.customer_data WHERE customer_id = $1 ORDER BY created_at DESC',
  [tenantId]
);