Certificate

# ===== Award criteria =====
# Pass: >= 70%; Distinction >= 85%.
#
# 1. Schema design                (20)
# 2. Indexes                       (15)
# 3. Complex queries (joins, CTEs, window)   (15)
# 4. Performance + EXPLAIN         (15)
# 5. Backups + PITR                (10)
# 6. Replication + HA              (10)
# 7. Security (roles + RLS)        (10)
# 8. Communication                 (5)

# ===== Project brief =====
# Design + operate a Postgres database supporting a small app:
# - 4+ tables with realistic relationships + constraints
# - Real dataset (10k+ rows) seeded via scripts
# - Composite + partial + functional indexes where appropriate
# - 6+ queries showing JOIN + GROUP BY + window function + CTE + JSON
# - EXPLAIN output + index decisions documented
# - Primary + replica via docker-compose
# - Backup runbook + tested restore (logical + physical)
# - Roles: ddl_admin, app_writer, app_reader, backup
# - Row-Level Security demo (multi-tenant)
# - Migration story (Flyway / golang-migrate / sqlx-migrate)

# ===== Suggested datasets =====
# - Ride sharing
# - Forum
# - Inventory
# - Bookstore
# - Time-series telemetry

# ===== Sample structure =====
# pg-portfolio/
#   schema/
#     001_init.sql
#     002_orders.sql
#   seeds/
#     users.sql (10k)
#     orders.sql (50k)
#   queries/
#     top-customers.sql + EXPLAIN
#     monthly-revenue.sql
#     rolling-30d.sql
#   ops/
#     backup.sh
#     restore.sh
#     replication-compose.yml
#   roles.sql
#   rls.sql
#   README.md

# ===== Marking sheet (example) =====
# 1. Schema           18/20   TIMESTAMPTZ, NUMERIC, UUID, FK, CHECK
# 2. Indexes          14/15   composite + functional + partial
# 3. Queries          14/15   CTE, window, DISTINCT ON
# 4. Performance      13/15   EXPLAIN ANALYZE + reasoning
# 5. Backups + PITR    9/10   logical + WAL archive; tested restore
# 6. Replication       9/10   primary + replica with sync state
# 7. Security          9/10   least-privilege roles + RLS policy
# 8. Communication     5/5    README + ER diagram + runbook
# Total: 91/100 -> Distinction

# ===== Patterns to internalise =====
# - TIMESTAMPTZ + NUMERIC + JSONB + UUID
# - Composite indexes that match left-anchored queries
# - VACUUM / autovacuum tuning on hot write tables
# - WAL archiving for PITR
# - Replicas for read scale; pgBouncer for connection pooling
# - One role per service responsibility; RLS for multi-tenant

# ===== Pitfalls =====
# - DOUBLE PRECISION for money
# - VARCHAR(n) magic numbers
# - Backups that were never tested
# - RLS policies that don't fire (verify with EXPLAIN)
# - JSON paths without expression indexes -> seq scan

-- /certificate/postgresql