The database that
does everything.

-- JSONB: document storage with index
CREATE INDEX idx_orders_meta ON orders USING gin (metadata jsonb_path_ops);

-- Containment query — uses the index
SELECT * FROM orders WHERE metadata @> '{"region": "ZA"}';

-- Path query
SELECT metadata ->> 'region' AS region, count(*) FROM orders GROUP BY 1;

-- Materialized view for a dashboard — refreshable without locking
CREATE MATERIALIZED VIEW daily_sales AS
SELECT
  date_trunc('day', created_at) AS day,
  sum(total)                     AS revenue,
  count(*)                       AS orders
FROM orders
WHERE status = 'completed'
GROUP BY 1;

-- Refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

-- LISTEN/NOTIFY: real-time events without a message queue
LISTEN order_updates;
NOTIFY order_updates, '{"order_id": 42, "status": "shipped"}';

-- Window function: running total by day
SELECT
  day,
  revenue,
  sum(revenue) OVER (ORDER BY day) AS running_total
FROM daily_sales
ORDER BY day;

-- Install pgvector (once, per database)
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with a vector column (1536-dim for text-embedding-3-small)
CREATE TABLE documents (
  id        bigserial  primary key,
  content   text,
  embedding vector(1536)
);

-- HNSW index for fast approximate nearest-neighbour search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Semantic search: find the 5 most similar documents
SELECT id, content, embedding <=> '[0.1, 0.2, ...]' AS distance
FROM documents
ORDER BY distance
LIMIT 5;

-- TimescaleDB: hypertable for time-series data
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE sensor_readings (
  time        timestamptz  NOT NULL,
  sensor_id   int,
  temperature numeric(5,2)
);

-- Convert to hypertable (auto-partitions by time)
SELECT create_hypertable('sensor_readings', 'time');

# Connection string pattern
postgresql://user:password@host:5432/dbname?sslmode=require

# With PgBouncer (connection pooling) in front
postgresql://user:password@pgbouncer-host:6432/dbname?sslmode=require

# Cloudflare Hyperdrive connection string (from a Worker)
# env.HYPERDRIVE.connectionString returns a pooled string automatically

# Backup strategy

# Small databases (dev, staging): logical backup
pg_dump -Fc -h host -U user dbname > backup_$(date +%Y%m%d).dump
pg_restore -d newdb backup_20260416.dump

# Production: physical backup + WAL archiving
# pg_basebackup takes a binary snapshot
pg_basebackup -h host -U replicator -Ft -z -Xs -P -D /backups/base

# WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'gsutil cp %p gs://my-bucket/wal/%f'

# Point-in-time recovery: restore base + replay WAL to a specific timestamp

-- Row-level security for POPIA data-subject access control
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Policy: each session can only see rows where customer_id matches the session variable
CREATE POLICY customer_isolation ON customer_data
  USING (customer_id = current_setting('app.current_customer_id')::bigint);

-- Set before each query in the application layer
SET app.current_customer_id = '42';
SELECT * FROM customer_data; -- returns only customer 42's rows

-- ZAR money: always NUMERIC, never FLOAT
CREATE TABLE invoices (
  id         bigserial primary key,
  amount_zar numeric(12,2) NOT NULL,  -- correct
  amount_bad float                       -- never do this
);