-- Normalized core
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL CHECK (status IN ('active', 'suspended', 'deleted')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  currency TEXT NOT NULL DEFAULT 'USD',
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'refunded', 'failed')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Strategic denormalization: hot read path for dashboard
ALTER TABLE users 
  ADD COLUMN last_order_status TEXT GENERATED ALWAYS AS (
    (SELECT status FROM orders WHERE user_id = users.id ORDER BY created_at DESC LIMIT 1)
  ) STORED;

-- Volatile attributes: JSONB with indexed paths
ALTER TABLE users ADD COLUMN preferences JSONB NOT NULL DEFAULT '{}';

CREATE INDEX idx_users

_prefs_region ON users USING gin ((preferences->>'region') jsonb_path_ops); CREATE INDEX idx_users_prefs_theme ON users ((preferences->>'theme'));

-- Time-series partitioning for audit logs CREATE TABLE audit_logs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), actor_id UUID NOT NULL, action TEXT NOT NULL, payload JSONB, created_at TIMESTAMPTZ NOT NULL ) PARTITION BY RANGE (created_at);

CREATE TABLE audit_logs_2024_q1 PARTITION OF audit_logs FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

**TypeScript Type Definitions & Query Contract**
```ts
interface User {
  id: string;
  email: string;
  status: 'active' | 'suspended' | 'deleted';
  last_order_status: string | null;
  preferences: {
    region?: string;
    theme?: 'light' | 'dark';
    notifications?: boolean;
  };
  created_at: Date;
  updated_at: Date;
}

interface Order {
  id: string;
  user_id: string;
  total_cents: number;
  currency: string;
  status: 'pending' | 'paid' | 'refunded' | 'failed';
  created_at: Date;
}

// Hot path query: matches composite index & denormalized column
const getUserDashboard = async (userId: string): Promise<User> => {
  return db.query<User>(`
    SELECT id, email, status, last_order_status, preferences, created_at, updated_at
    FROM users
    WHERE id = $1
  `, [userId]);
};

// Volatile attribute update: avoids DDL churn
const updateUserPreferences = async (userId: string, prefs: Partial<User['preferences']>) => {
  const current = await getUserDashboard(userId);
  const merged = { ...current.preferences, ...prefs };
  
  return db.execute(`
    UPDATE users 
    SET preferences = $1::jsonb, updated_at = now()
    WHERE id = $2
  `, [JSON.stringify(merged), userId]);
};

Architecture Decisions & Rationale

• Generated columns for denormalization: Ensures consistency without application-level sync. Storage cost is minimal; read latency drops significantly for dashboard queries.
• JSONB with GIN/B-tree indexes: Balances schema flexibility with query performance. Avoids EAV anti-patterns while preserving constraint validation.
• Range partitioning for audit/logs: Enables automatic data retention, parallel query execution, and faster maintenance operations (VACUUM, index rebuilds).
• Explicit constraints over application validation: Database-level CHECK and UNIQUE constraints prevent corrupt states at the source, reducing defensive coding and race conditions.

Pitfall Guide

1. Normalizing for Normalization’s Sake

Designing to 3NF without analyzing read patterns creates join-heavy queries that fail under concurrency. Normalization reduces storage but multiplies I/O. Validate against actual query shapes before splitting tables.

2. Over-Indexing

Every index degrades write throughput and increases lock contention. Indexes that are never used in production query plans waste storage and slow INSERT/UPDATE operations. Run EXPLAIN ANALYZE on critical paths; drop unused indexes quarterly.

3. EAV Abuse

Entity-Attribute-Value schemas appear flexible but destroy query performance, bypass constraints, and complicate type safety. Use JSONB with generated columns or partitioned tables instead. Reserve EAV only for metadata systems with strict access patterns.

4. Ignoring Partition Boundaries

Partitioning without aligned query filters causes full partition scans. Always partition on columns used in WHERE clauses. Ensure partition keys match retention policies and query cardinality.

5. Foreign Key Cascades in High-Write Systems

ON DELETE CASCADE triggers recursive locks that stall concurrent writes. Use application-level soft deletes or deferred cleanup jobs for high-throughput systems. Keep foreign keys for integrity but avoid cascade-heavy operations on hot tables.

6. Schema Versioning Without Backward Compatibility

Dropping columns or changing types without an expand/contract migration breaks running instances. Always deploy in phases: add new column → update application to write both → backfill → switch reads → drop old column.

7. Tight Coupling via Implicit Defaults

Relying on database defaults without explicit application handling creates silent data drift. Define defaults in both DDL and TypeScript interfaces. Validate default behavior during migration testing.

Production Best Practices:

• Design queries first, then structure tables to match
• Use constraints as contracts, not afterthoughts
• Migrate with expand/contract pattern; never break backward compatibility
• Validate every index against actual query plans
• Separate hot transactional tables from cold analytical tables
• Version schema changes alongside application code in the same deployment pipeline

Production Bundle

Action Checklist

• Map access patterns: document read/write ratios, filter columns, and sort requirements before writing DDL
• Establish normalized core: use 3NF for transactional integrity and referential consistency
• Apply strategic denormalization: duplicate only indexed filter/sort columns for hot read paths
• Isolate volatile attributes: use JSONB with generated columns and targeted indexes
• Align indexes with queries: create composite indexes following left-prefix rules; validate with EXPLAIN
• Partition large tables: use range or list partitioning aligned to WHERE clauses and retention policies
• Implement expand/contract migrations: never drop or rename columns without backward-compatible phases
• Enforce constraints at database level: CHECK, UNIQUE, and NOT NULL prevent corrupt states at the source

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-read analytics dashboard	Strategic Denormalization + Materialized Views	Eliminates joins; precomputes aggregates; reduces p99 latency	+15% storage, -40% read compute
High-write IoT telemetry	Range-Partitioned Time-Series + Append-Only	Parallel writes; fast retention; minimal index overhead	+8% storage, -60% maintenance cost
Multi-tenant SaaS with custom fields	JSONB Hybrid + Generated Indexes	Schema flexibility without DDL churn; tenant isolation via partitioning	+12% storage, -30% migration effort
Rapid prototyping / MVP	Strict 3NF + Application-Level Validation	Fast iteration; clear relationships; easy rollback	Baseline storage, +20% query latency at scale

Configuration Template

-- Production Schema Template: Hybrid Pattern
-- Apply per-service; adjust partition ranges and indexes per access pattern

BEGIN;

-- 1. Core transactional table
CREATE TABLE IF NOT EXISTS transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),
  currency TEXT NOT NULL DEFAULT 'USD',
  status TEXT NOT NULL CHECK (status IN ('pending', 'completed', 'failed')),
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2. Strategic denormalization for hot path
ALTER TABLE transactions 
  ADD COLUMN tenant_currency_status TEXT GENERATED ALWAYS AS (
    tenant_id || '_' || currency || '_' || status
  ) STORED;

-- 3. Indexes aligned to access patterns
CREATE INDEX idx_transactions_tenant_status ON transactions (tenant_id, status) WHERE status != 'failed';
CREATE INDEX idx_transactions_created_tenant ON transactions (created_at DESC, tenant_id);
CREATE INDEX idx_transactions_metadata_tags ON transactions USING gin ((metadata->'tags') jsonb_path_ops);

-- 4. Partitioning for time-series retention
CREATE TABLE transactions_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  amount_cents INTEGER NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 5. Migration contract: expand/contract ready
-- Phase 1: Add new column with default
-- ALTER TABLE transactions ADD COLUMN new_field TEXT DEFAULT '';
-- Phase 2: Update app to write both
-- Phase 3: Backfill
-- Phase 4: Switch reads
-- Phase 5: DROP COLUMN old_field;

COMMIT;

Quick Start Guide

1. Define access patterns: List top 5 read queries and top 3 write operations. Note filter columns, sort order, and expected concurrency.
2. Draft DDL with hybrid structure: Create normalized core tables, add JSONB columns for variant data, and apply generated columns for hot read paths.
3. Validate query plans: Run EXPLAIN ANALYZE on critical queries. Add composite indexes matching left-prefix rules. Drop unused indexes.
4. Apply migration safely: Use expand/contract pattern. Deploy schema change, update application to write both old/new paths, backfill, switch reads, then remove legacy columns.
5. Monitor & iterate: Track p99 latency, write throughput, and index hit ratios. Adjust partition boundaries and indexes quarterly based on production telemetry.