Querymaster Postgresql

---
name: querymaster-postgresql
description: "Child skill of querymaster for PostgreSQL (standard, Azure Database, Aurora): psycopg2 and Node connection patterns, read-only session and query best practices. Activates when the resolved engine is PostgreSQL."
---

# QueryMaster — PostgreSQL Engine Skill

> Child skill of `querymaster`. Activated when engine is PostgreSQL.
> Covers: Azure Database for PostgreSQL, standard PostgreSQL, Aurora PostgreSQL.
>
> **Knowledge sources**: 31 production skills (~4,100 lines), 94 SQL fix scripts across 79 tickets.
> Last harvested: 2026-03-18

## Connection Patterns

### Python (psycopg2)
```python
import psycopg2
conn = psycopg2.connect(
    host=env["DB_HOST"],
    port=env.get("DB_PORT", 5432),
    user=env["DB_USER"],
    password=env["DB_PASSWORD"],
    dbname=env["DB_NAME"],
    sslmode=env.get("DB_SSLMODE", "require"),
    connect_timeout=10,
)
conn.set_session(readonly=True)  # when --readonly
```

### Node.js (pg)
```javascript
const { Pool } = require('pg');
const pool = new Pool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT || 5432,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { rejectUnauthorized: true },
    max: 5,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
});
```

### Pool Sizing Rule
```
max_pool_size = (core_count * 2) + effective_spindle_count
Azure B1ms (1 vCPU): max = 3
Azure D2s_v3 (2 vCPU): max = 5
```

### connections.json entry
```json
{
  "prod_client": {
    "engine": "postgresql",
    "env_prefix": "DB",
    "env_file": "~/path/to/client-a/.env",
    "defaults": { "port": 5432, "sslmode": "require" }
  }
}
```

---

## Best Practices (Embedded from 31 production client skills)

### Query Generation Rules

1. **Always qualify with schema** — `schema.table` not just `table`
2. **Naming conventions**: `snake_case` for everything. Index: `idx_{table}_{cols}`, unique: `uidx_`, PK: `pk_{table}`, FK: `fk_{child}_{parent}`, check: `chk_{table}_{col}`
3. **Timestamps**: Always `timestamptz`, never `timestamp without time zone`. Default: `now()` not `CURRENT_TIMESTAMP`
4. **Primary keys**: Every table MUST have a PK. Prefer `bigint GENERATED BY DEFAULT AS IDENTITY`
5. **EXPLAIN ANALYZE** — Always suggest for performance questions, but warn it EXECUTES the query

### Schema Discovery Queries

```sql
-- List all schemas
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schema_name;

-- Tables with row counts and sizes
SELECT schemaname, relname AS table_name, n_live_tup AS estimated_rows,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

-- Tables without primary keys
SELECT t.tablename
FROM pg_tables t
LEFT JOIN (SELECT tc.table_name FROM information_schema.table_constraints tc
    WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = 'public'
) pk ON pk.table_name = t.tablename
WHERE t.schemaname = 'public' AND pk.table_name IS NULL
ORDER BY t.tablename;

-- Index usage stats
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

-- Active connections
SELECT pid, usename, application_name, client_addr, state, query_start, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start;

-- Foreign key relationships
SELECT tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS references_table, ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
ORDER BY tc.table_name;
```

---

## Autovacuum & Bloat Management

PostgreSQL uses MVCC — every UPDATE creates a new row version, DELETE marks rows dead. Dead tuples accumulate as **bloat** until VACUUM reclaims space.

### Monitor dead tuples and vacuum activity
```sql
SELECT schemaname, relname, n_live_tup, n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;
```

### Autovacuum trigger formula
`dead_tuples > threshold + scale_factor × n_live_tup`

For 1M-row table: default (0.20) = vacuum at 200,050 dead tuples. Tuned (0.05) = 50,050.

### Tune autovacuum for high-churn tables
```sql
ALTER TABLE public."AuditLog" SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- vacuum at 5% dead (default 20%)
    autovacuum_analyze_scale_factor = 0.02   -- analyze at 2% changes (default 10%)
);
```

### Decision matrix

| Dead tuple % | Action |
|-------------|--------|
| < 10% | Normal — autovacuum handling it |
| 10-30% | Lower `autovacuum_vacuum_scale_factor` |
| > 30% | Immediate manual `VACUUM`; then tune autovacuum |
| After bulk DELETE/UPDATE | Run `VACUUM ANALYZE` manually |

### Reindex for index bloat
```sql
REINDEX INDEX CONCURRENTLY public."ix_auditlog_createddate";

-- Check for invalid indexes (failed concurrent operations)
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;
```

**Gotchas**: Never disable autovacuum (transaction ID wraparound catastrophe). `VACUUM FULL` acquires `AccessExclusiveLock` — blocks all access. `VACUUM` reclaims space for reuse but does NOT return it to the OS.

---

## Fillfactor & HOT Update Tuning

Fillfactor controls how full each 8KB page is filled during INSERT, leaving room for in-page HOT updates.

### Analyze workload
```sql
SELECT relname, n_tup_ins AS inserts, n_tup_upd AS updates,
    ROUND(100.0 * n_tup_upd / NULLIF(n_tup_ins + n_tup_upd + n_tup_del, 0), 1) AS update_pct
FROM pg_stat_user_tables WHERE schemaname = 'public' ORDER BY n_tup_upd DESC;
```

| Update % | Fillfactor | Rationale |
|----------|------------|-----------|
| < 10% | 100 (default) | Mostly INSERT-only, maximize density |
| 10-50% | 90 | Balance between density and HOT updates |
| > 50% | 80-85 | Heavy UPDATE workload, maximize HOT success |
| Append-only (logs) | 100 | Never updated |

### Validate HOT effectiveness
```sql
SELECT relname, n_tup_hot_upd, n_tup_upd,
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY n_tup_upd DESC;
```
Target: HOT update percentage > 90% for update-heavy tables.

---

## EXPLAIN ANALYZE Validation

### Usage
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT "HospitalId", COUNT(*) FROM public."AppointmentInfo"
WHERE "StatusId" = 1 GROUP BY "HospitalId";
```

### Key metrics

| Metric | Red Flag |
|--------|----------|
| `Seq Scan` on table > 10K rows with WHERE | Missing index |
| `Rows Removed by Filter` >> `actual rows` | Wrong/missing index |
| `shared read` high | I/O problem, missing cache |
| `actual rows` vs `rows` large mismatch | Stale stats — run ANALYZE |
| `Nested Loop` with `loops=100000` | Bad JOIN order, missing index on inner |
| `Sort Method: external merge` | Increase `work_mem` or add sort-matching index |

### Before/after comparison pattern
```sql
-- Before: Seq Scan, 45ms, 8234 buffers
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public."Recording" WHERE "HospitalId" = 42;
-- After adding index: Index Scan, 0.8ms, 12 buffers
```

**Gotcha**: Always `ANALYZE` the table before running EXPLAIN if data was recently loaded. Wrap destructive statements: `BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;`

---

## pg_stat_statements Observability

### Enable (Azure Flexible Server)
```text
shared_preload_libraries = pg_stat_statements   -- requires restart
pg_stat_statements.track = all
pg_stat_statements.max = 5000
```
```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

### Top queries by total time
```sql
SELECT calls, ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms, rows, LEFT(query, 120) AS query_preview
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
```

### Top queries by I/O
```sql
SELECT calls, shared_blks_read + shared_blks_hit AS total_blks,
    ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS cache_hit_pct,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms, LEFT(query, 120) AS query_preview
FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 10;
```

### Action thresholds

| Metric | Threshold |
|--------|-----------|
| `total_exec_time` | Top query > 50% of total |
| `mean_exec_time` | > 100ms for OLTP queries |
| `calls` | > 10K calls/hour |
| `rows` | > 10K rows per call (missing pagination?) |
| `cache_hit_pct` | < 95% = under-provisioned memory |

**Gotcha**: The `query` column normalizes literals to `$1, $2, ...` — you cannot see actual parameter values. `pg_stat_statements_reset()` clears ALL stats (no per-query reset).

---

## Index Creation (CONCURRENTLY)

### Always use CONCURRENTLY on production
```sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_appointmentinfo_hospitalid
    ON public."AppointmentInfo" ("HospitalId");
```

### Lock comparison

| Mode | Lock | Blocks Writes? |
|------|------|----------------|
| `CREATE INDEX` | `SHARE` | Yes |
| `CREATE INDEX CONCURRENTLY` | `SHARE UPDATE EXCLUSIVE` | No |

### Covering index (Index-Only Scans)
```sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_vwr_animalid_covering
    ON analytics."events" ("entity_id")
    INCLUDE ("AnimalName", "Species", "Breed");
```

### Partitioned table workaround
`CONCURRENTLY` is NOT supported on partitioned parents. Workaround:
1. `CREATE INDEX ON ONLY parent_table (col)` — marks parent index as invalid
2. `CREATE INDEX CONCURRENTLY ON each_partition (col)` — non-blocking per child
3. `ALTER INDEX parent_idx ATTACH PARTITION child_idx` — links them
4. Once all attached, parent index becomes valid automatically

**Gotchas**: Cannot run inside a transaction or DO block. Failed builds leave INVALID indexes — check `pg_index WHERE NOT indisvalid`. `IF NOT EXISTS` does NOT rebuild invalid indexes. Concurrent builds take ~2x longer (two table scans).

---

## Connection Pooling & Timeout Safety

### Server parameters
```text
statement_timeout = 30000             -- 30s for app queries
idle_in_transaction_session_timeout = 60000   -- kill idle-in-transaction after 60s
log_min_duration_statement = 250      -- log queries > 250ms
```

### Temporary override for migrations
```sql
BEGIN;
    SET LOCAL statement_timeout = '5min';  -- only within this transaction
    ALTER TABLE public."LargeTable" ADD COLUMN "IsActive" boolean NOT NULL DEFAULT true;
COMMIT;
```

### Monitor connections
```sql
SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state ORDER BY COUNT(*) DESC;

SELECT usename, application_name, state, NOW() - query_start AS duration,
    LEFT(query, 80) AS query_preview
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;
```

| Scenario | statement_timeout |
|----------|------------------|
| Web API queries | 30s |
| Migration scripts | 5min (SET LOCAL) |
| Bulk data loads | 10min (SET LOCAL) |
| pg_cron jobs | Default (30s) |

**Gotcha**: `statement_timeout` applies to the entire DO block, not individual statements within it. `SET LOCAL` only works inside a transaction. PgBouncer in transaction mode breaks `SET` — use `SET LOCAL`.

---

## Timestamp Standardization (timestamptz)

### Find non-timestamptz columns
```sql
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'timestamp without time zone'
ORDER BY table_name, ordinal_position;
```

### Convert with UTC reinterpretation
```sql
ALTER TABLE public."MyTable"
    ALTER COLUMN "CreatedDate" TYPE timestamptz
    USING "CreatedDate" AT TIME ZONE 'UTC';
```

### Handle dependent views
Drop views before ALTER, recreate after. Use `pg_get_viewdef()` to save definitions.

| Scenario | Use |
|----------|-----|
| New "when" column | `timestamptz NOT NULL DEFAULT now()` |
| Date only (no time) | `date` |
| Duration | `interval` |
| Legacy `timestamp` | Convert to `timestamptz` |

**Gotcha**: `ALTER COLUMN TYPE` rewrites the table — locks it. Without `AT TIME ZONE 'UTC'`, PostgreSQL assumes session timezone.

---

## Range Partitioning for Growth Tables

### When to partition

| Signal | Partition? |
|--------|-----------|
| Table > 10M rows and growing | Yes |
| Table > 1M rows, date-filtered queries | Yes |
| < 1M rows | No — overhead not justified |
| No date column in WHERE | No — pruning cannot help |
| Need to archive/purge old data | Yes — DROP partition = instant |

### Create partitioned table
```sql
CREATE TABLE public."AuditLog" (
    "AuditLogId" bigint GENERATED BY DEFAULT AS IDENTITY,
    "Action" text NOT NULL,
    "CreatedAt" timestamptz NOT NULL DEFAULT NOW(),
    "Payload" jsonb
) PARTITION BY RANGE ("CreatedAt");

CREATE TABLE public."AuditLog_2026_01" PARTITION OF public."AuditLog"
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
```

### Automate with pg_cron
```sql
SELECT cron.schedule('create_auditlog_partition', '0 3 25 * *',
    $$ DO $$ DECLARE
        v_start date := date_trunc('month', NOW() + INTERVAL '1 month');
        v_end   date := v_start + INTERVAL '1 month';
        v_name  text := 'AuditLog_' || to_char(v_start, 'YYYY_MM');
    BEGIN
        EXECUTE format('CREATE TABLE IF NOT EXISTS public.%I PARTITION OF public."AuditLog"
            FOR VALUES FROM (%L) TO (%L)', v_name, v_start, v_end);
    END $$; $$);
```

### Archive old partitions
```sql
ALTER TABLE public."AuditLog" DETACH PARTITION public."AuditLog_2023_01";
ALTER TABLE public."AuditLog_2023_01" SET SCHEMA archive;  -- or DROP TABLE
```

**Critical gotchas**:
- PK must include partition key column — `(Id, CreatedAt)` not just `(Id)`
- FK NOT VALID limitation: FK on partitioned tables cannot use NOT VALID — scans ALL partitions
- Unique indexes must include partition key
- If no partition exists for inserted row's date, INSERT **fails** — create in advance
- No retroactive `ALTER TABLE ... PARTITION BY` — requires data migration

---

## Data Retention Policy Lifecycle

Five-phase approach: Size Analysis → Rule Definition → Maintenance Procedure → Schedule → Validate.

### Phase 1: Size analysis
```sql
SELECT pg_size_pretty(pg_total_relation_size('"ShiftAuditLog"')) AS total_size,
    (SELECT COUNT(*) FROM public."ShiftAuditLog") AS row_count,
    MIN("CreatedDate") AS oldest_row, MAX("CreatedDate") AS newest_row
FROM public."ShiftAuditLog";
```

### Phase 3: Maintenance procedure
```sql
CREATE OR REPLACE PROCEDURE maintenance.purge_shift_audit_log(p_retention_days int DEFAULT 90)
LANGUAGE plpgsql AS $$
DECLARE v_cutoff timestamptz; v_deleted bigint;
BEGIN
    v_cutoff := now() - (p_retention_days || ' days')::interval;
    DELETE FROM public."ShiftAuditLog" WHERE "CreatedDate" < v_cutoff;
    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    RAISE NOTICE 'Purged % rows older than %', v_deleted, v_cutoff;
END; $$;
```

### Phase 4: Schedule
```sql
SELECT cron.schedule('purge-shift-audit-log', '0 3 * * 0',
    $$CALL maintenance.purge_shift_audit_log(90)$$);
```

**Always** index the retention column before scheduling purge jobs. Large deletes cause bloat — schedule VACUUM after.

---

## pg_cron Scheduled Maintenance

```sql
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule
SELECT cron.schedule('purge-expired-tokens', '0 * * * *',
    $$CALL maintenance.purge_expired_tokens()$$);

-- Validate execution
SELECT jobid, start_time, end_time, status, return_message
FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

-- Rollback
SELECT cron.unschedule('purge-expired-tokens');
```

**Gotchas**: Runs in `postgres` database by default — use `cron.schedule_in_database()` for others. Requires `shared_preload_libraries` on Azure.

---

## Primary Key Coverage & Identity Columns

### Audit missing PKs
```sql
SELECT t.tablename FROM pg_tables t
LEFT JOIN (SELECT tc.table_name FROM information_schema.table_constraints tc
    WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = 'public'
) pk ON pk.table_name = t.tablename
WHERE t.schemaname = 'public' AND pk.table_name IS NULL ORDER BY t.tablename;
```

### Add PK with identity
```sql
ALTER TABLE public."EmailConfig" ADD COLUMN "Id" bigint GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE public."EmailConfig" ADD CONSTRAINT pk_emailconfig PRIMARY KEY ("Id");
```

### BIGINT vs UUID

| Type | Use When |
|------|----------|
| `bigint IDENTITY` | Single-database, sequential (default recommendation) |
| `uuid v4` | Distributed systems, external IDs |
| `uuid v7` | Distributed + temporal ordering |

Use `GENERATED BY DEFAULT` (allows explicit INSERT) over `GENERATED ALWAYS` for backward compatibility.

---

## Serial to Identity Conversion

```sql
-- 1. Check current state
SELECT column_name, column_default, is_identity FROM information_schema.columns
WHERE table_name = 'feature_flagss' AND column_name = 'FlagId';

-- 2. Remove old default
ALTER TABLE feature_flags."feature_flagss" ALTER COLUMN "FlagId" DROP DEFAULT;

-- 3. Drop orphaned sequence
DROP SEQUENCE IF EXISTS feature_flags."feature_flagss_FlagId_seq";

-- 4. Add identity
ALTER TABLE feature_flags."feature_flagss" ALTER COLUMN "FlagId"
    ADD GENERATED BY DEFAULT AS IDENTITY;

-- 5. CRITICAL: Sync sequence to max value
SELECT setval(pg_get_serial_sequence('feature_flags."feature_flagss"', 'FlagId'),
    COALESCE((SELECT MAX("FlagId") FROM feature_flags."feature_flagss"), 1));
```

**Gotcha**: Must sync sequence after conversion — skip this and next INSERT gets duplicate key error.

---

## Foreign Key Constraints

### Standard pattern: NOT VALID + VALIDATE
```sql
-- Step 1: Add without scanning existing rows (instant, lightweight lock)
ALTER TABLE public."AppointmentInfo"
    ADD CONSTRAINT fk_appointmentinfo_hospitalid
    FOREIGN KEY ("HospitalId") REFERENCES public."Hospital"("HospitalId")
    NOT VALID;

-- Step 2: Validate existing rows (non-blocking, concurrent reads/writes OK)
ALTER TABLE public."AppointmentInfo"
    VALIDATE CONSTRAINT fk_appointmentinfo_hospitalid;
```

### Lock behavior
- `ADD FK` acquires `SHARE ROW EXCLUSIVE` on both child AND parent
- `NOT VALID` skips row scan — instant creation
- `VALIDATE CONSTRAINT` acquires lighter `SHARE UPDATE EXCLUSIVE` (allows concurrent reads AND writes)

### Pre-requisites
1. No orphans exist (see Orphan Detection below)
2. Parent column has PK or UNIQUE constraint
3. Data types match between child and parent columns

**Gotchas**: Always add supporting index on FK columns — PostgreSQL does NOT auto-create them. On partitioned tables, FK cannot use NOT VALID.

---

## Orphan Detection & FK Rollout

### Detect orphans
```sql
SELECT c."HospitalId", COUNT(*) AS orphan_count
FROM public."AppointmentInfo" c
LEFT JOIN public."Hospital" p ON p."HospitalId" = c."HospitalId"
WHERE p."HospitalId" IS NULL AND c."HospitalId" IS NOT NULL
GROUP BY c."HospitalId" ORDER BY orphan_count DESC;
```

### Remediation strategies

| Strategy | When to Use |
|----------|-------------|
| Delete orphans | Child rows valueless without parent |
| NULL out FK column | Child rows valuable, FK is optional |
| Create missing parents | Parent accidentally deleted |
| Skip FK | Orphans too numerous or business-critical |

Always scan for orphans BEFORE attempting `ADD CONSTRAINT`.

---

## Stored Procedure Hardening

### FK-aware delete ordering
```sql
-- WRONG: delete parent before children → FK violation
DELETE FROM public."Users" WHERE "UserId" = p_user_id;
DELETE FROM public."UserRoles" WHERE "UserId" = p_user_id;  -- too late!

-- RIGHT: children first, then parent
DELETE FROM public."UserRoles" WHERE "UserId" = p_user_id;
DELETE FROM public."UserSessions" WHERE "UserId" = p_user_id;
DELETE FROM public."Users" WHERE "UserId" = p_user_id;  -- safe now
```

### Explicit error handling
```sql
IF NOT EXISTS (SELECT 1 FROM public."Users" WHERE "UserId" = p_user_id) THEN
    RAISE EXCEPTION 'User % not found', p_user_id;
END IF;

DELETE FROM public."Users" WHERE "UserId" = p_user_id;
GET DIAGNOSTICS v_count = ROW_COUNT;

IF v_count <> 1 THEN
    RAISE EXCEPTION 'Expected to delete 1 user, deleted %', v_count;
END IF;
```

### Deterministic behavior
Always use explicit `ORDER BY` in SELECT results — never rely on implicit ordering.

---

## Security Roles & Least-Privilege

### Three standard roles
```sql
CREATE ROLE app_owner NOLOGIN;    -- Schema management, migrations
CREATE ROLE app_user NOLOGIN;     -- Application runtime (DML only)
CREATE ROLE app_readonly NOLOGIN; -- Dashboards, reporting (SELECT only)

GRANT app_readonly TO app_user;
GRANT app_user TO app_owner;
```

### Schema-level grants
```sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

GRANT ALL ON SCHEMA public TO app_owner;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
```

### Default privileges for future objects
```sql
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_user;
```

### Audit permissions
```sql
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public' ORDER BY grantee, table_name;
```

**Azure notes**: Admin user has `azure_pg_admin` (NOT true superuser). Password rotation: use Azure Key Vault. `DROP OWNED BY <role>` drops ALL objects — extreme caution.

---

## DDL Generation Rules

### 1. Idempotent Design (ALWAYS)
```sql
-- Column renames
DO $$ BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns
        WHERE table_name = 'Applicant' AND column_name = 'SpeialtyRequirement') THEN
        ALTER TABLE public."Applicant" RENAME COLUMN "SpeialtyRequirement" TO "SpecialtyRequirement";
        RAISE NOTICE 'Renamed column';
    ELSE
        RAISE NOTICE 'Skipped (already renamed)';
    END IF;
END $$;

-- Index creation
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_my_index ON public."MyTable" ("MyColumn");

-- FK constraints
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_my_constraint') THEN
        ALTER TABLE public."MyTable" ADD CONSTRAINT fk_my_constraint
            FOREIGN KEY ("MyColumn") REFERENCES public."OtherTable"("Id");
    END IF;
END $$;
```

### 2. Atomic 3-Phase DDL Scripts
```text
Phase 1: Pre-check — gap analysis, dry-run gate (RAISE EXCEPTION if dry_run = true)
Phase 2: Execute — all changes inside ONE DO block (atomic rollback on failure)
Phase 3: Post-check — verify every expected outcome, report confirmed/failed counts
```
`CREATE INDEX CONCURRENTLY` cannot run inside Phase 2 (needs its own phase outside DO block).

### 3. Backward-Compatible Schema Changes
- Rename columns in table but preserve procedure parameter names
- Application continues working without code changes
- Document the "debt" for future parameter name cleanup

### 4. Safety Guards
- **Never generate** `DROP TABLE` without `IF EXISTS`
- **Never generate** `DELETE FROM` without `WHERE` clause
- **Never generate** `TRUNCATE` without explicit user request + double confirmation
- **Always suggest** `BEGIN; ... ROLLBACK;` wrapper for destructive operations in dry-run
- **Timeout**: `SET statement_timeout = '30s';` prepended to long-running queries
- **FK with NOT VALID first**, then `VALIDATE CONSTRAINT` separately
- **Columns**: Never add NOT NULL without DEFAULT on existing tables (full rewrite)

---

## Common Prompt → SQL Mappings

| User says | Generated SQL |
|-----------|--------------|
| "table sizes" | `pg_stat_user_tables` + `pg_total_relation_size` |
| "slow queries" | `pg_stat_statements ORDER BY mean_exec_time DESC` |
| "missing indexes" | Seq-scan heavy tables from `pg_stat_user_tables` |
| "locks" / "blocking" | `pg_locks JOIN pg_stat_activity` |
| "vacuum status" | `pg_stat_user_tables` last_autovacuum/last_vacuum |
| "connections" | `pg_stat_activity` grouped by state |
| "foreign keys" | `information_schema.table_constraints + key_column_usage` |
| "duplicate rows" | `GROUP BY all columns HAVING COUNT(*) > 1` |
| "table structure" | `information_schema.columns WHERE table_name = ...` |
| "dead tuples" / "bloat" | `pg_stat_user_tables WHERE n_dead_tup > 1000` |
| "orphan records" | `LEFT JOIN parent WHERE parent.id IS NULL` |
| "missing PKs" | `pg_tables LEFT JOIN table_constraints` |
| "timestamp types" | `information_schema.columns WHERE data_type = 'timestamp without time zone'` |
| "permissions" / "grants" | `information_schema.table_privileges` |
| "index bloat" | `pgstattuple` extension or REINDEX CONCURRENTLY |
| "partition info" | `pg_catalog.pg_inherits` + `pg_class` |
| "HOT updates" | `pg_stat_user_tables.n_tup_hot_upd` |
| "serial columns" | `information_schema.columns WHERE column_default LIKE 'nextval%'` |

---

## Error Handling

| Error | Cause | Action |
|-------|-------|--------|
| `connection refused` | Wrong host/port or server down | Verify env vars, check server status |
| `FATAL: password authentication failed` | Wrong credentials | Check .env DB_PASSWORD |
| `SSL SYSCALL error` | Network interruption | Retry with backoff |
| `canceling statement due to statement timeout` | Query too slow | Suggest EXPLAIN ANALYZE, increase --timeout |
| `permission denied for table` | Insufficient privileges | Show current role, suggest GRANT |
| `column "x" does not exist` | Wrong column name or table | Check `information_schema.columns` |
| `relation "x" does not exist` | Wrong table name or schema | Verify with schema-qualified name |
| `violates foreign key constraint` | Orphan data or FK ordering | Check orphans, fix delete order |
| `duplicate key value violates unique` | Duplicate row or sequence desync | Check for dupes, resync sequence with `setval` |
| `deadlock detected` | Concurrent conflicting transactions | Retry with backoff, review lock ordering |
| `could not obtain lock` | DDL waiting for active queries | Check `pg_stat_activity`, retry in maintenance window |

---

## Lessons Learned

> This section is auto-populated when queries fail. Each entry captures the error pattern, root cause, and fix for future reference.

| Date | Error Pattern | Root Cause | Fix |
|------|--------------|-----------|-----|
| 2026-03-17 | `no such column: direction` in Optuna SQLite DB | Column is in `study_directions` table, not `studies` | JOIN `study_directions` on `study_id` |

<!-- New lessons are appended here by the self-improvement pipeline -->