c-relay-pg/plans/postgres_implementation_plan.md

# C-Relay-PG: PostgreSQL Implementation Plan

## Current State Assessment

### What We Have
- **PostgreSQL 18.3** installed and running on localhost:5432
- **libpq development headers** available via pkg-config (`/usr/include/postgresql`, `/usr/lib/x86_64-linux-gnu`)
- **`db_ops.h`** abstraction layer with 40+ functions already defined
- **`db_ops.c`** — single 1,398-line file implementing all functions against SQLite
- **`ip_ban.c`** — already uses `db_ops` API (not raw SQLite) for persistence
- **`config.c`** — uses `db_ops` API for all database access

### SQLite Coupling Points (Must Fix)

| Location | Issue |
|----------|-------|
| `src/main.c:11` | `#include <sqlite3.h>` |
| `src/main.c:51` | `sqlite3* g_db = NULL;` — global handle owned by main.c |
| `src/main.c:897` | `if (g_db)` — direct handle check |
| `src/main.c:2228` | `if (!g_db)` — direct handle check |
| `src/main.c:2358` | `g_db = NULL;` — strict mode nullification |
| `src/main.c:743` | Log message references `sqlite3_open()` |
| `src/main.c:854-885` | SQLite PRAGMAs (WAL, mmap_size, cache_size) |
| `src/main.c:795-824` | SQLite-specific DDL in migration code |
| `src/main.c:900` | `PRAGMA wal_checkpoint(TRUNCATE)` |
| `src/config.c:4727` | `sqlite_master` query |
| `src/config.c:4388` | `INSERT OR REPLACE` SQL |
| `src/db_ops.c:13` | `extern sqlite3* g_db;` |
| `src/db_ops.c:871` | `strftime('%s', 'now')` — SQLite-specific |
| `src/db_ops.c:889,909,956` | `INSERT OR REPLACE` — SQLite-specific |
| `src/db_ops.c:1341` | `sqlite_master` — SQLite-specific |
| `src/db_ops.c:1387-1395` | `sqlite3_wal_checkpoint_v2()` — SQLite-only |
| `src/ip_ban.c:122,205` | `strftime('%s', 'now')` and `INSERT OR REPLACE` in SQL strings |
| `src/sql_schema.h` | Entire embedded schema is SQLite DDL |
| `src/subscriptions.c:1187-1197` | Commented-out raw SQLite code |

### What Already Works Through `db_ops`
- `ip_ban.c` — uses `db_exec_sql()`, `db_prepare()`, `db_step_stmt()`, etc.
- `config.c` — uses `db_get_config_value_dup()`, `db_set_config_value_full()`, etc.
- `subscriptions.c` — uses `db_log_subscription_*()` functions
- `api.c` — uses `db_execute_readonly_query_json()`, stat helpers
- `nip009.c` — uses `db_delete_event_by_id()`, `db_get_event_pubkey()`

---

## Implementation Phases

### Phase 0: PostgreSQL Setup & Smoke Test

Create the PostgreSQL role, database, and verify connectivity from C.

```
Tasks:
  0.1  Create PostgreSQL role "crelay" with password
  0.2  Create database "crelay" owned by role "crelay"
  0.3  Write a minimal C test program that connects via libpq and runs SELECT 1
  0.4  Verify the test compiles and links with: gcc test_pg.c -lpq -o test_pg
```

### Phase 1: Internalize g_db — Move Database Ownership Into db_ops

**Goal**: Eliminate `extern sqlite3* g_db` from main.c. The `db_ops` module must own its connection handle internally. This is the critical prerequisite — without it, swapping backends is impossible.

```mermaid
flowchart LR
    subgraph Before
        MAIN[main.c owns sqlite3* g_db] --> DBOPS[db_ops.c uses extern g_db]
    end
    subgraph After
        MAIN2[main.c calls db_init/db_close] --> DBOPS2[db_ops.c owns static g_db internally]
    end
```

```
Tasks:
  1.1  Move `sqlite3* g_db` from main.c into db_ops.c as a static variable
  1.2  Move `char g_database_path[512]` from config.c into db_ops.c as static
  1.3  Remove `#include <sqlite3.h>` from main.c
  1.4  Replace all `g_db` references in main.c with db_ops API calls:
       - `if (g_db)` → `if (db_is_available())`
       - `g_db = NULL` → `db_close()` (or new `db_detach_main()`)
  1.5  Replace SQLite PRAGMA calls in main.c init_database() with new db_ops functions:
       - `db_set_journal_mode_wal()`
       - `db_set_mmap_size(long size)`
       - `db_set_cache_size(int kb)`
  1.6  Move schema migration logic from main.c into db_ops (new `db_apply_schema()`)
  1.7  Replace `sqlite_master` query in config.c:4727 with `db_table_exists()`
  1.8  Replace `INSERT OR REPLACE` in config.c:4388 with `db_prepare()`/`db_step_stmt()`
  1.9  Build and run existing test suite to confirm no regressions
```

### Phase 2: Rename db_ops.c → db_ops_sqlite.c, Create Dispatcher

**Goal**: Split the single `db_ops.c` into a backend-specific file and a thin dispatcher, preparing the architecture for a second backend.

```
New file layout:
  src/db_ops.h              — unchanged public API
  src/db_ops_sqlite.c       — renamed from db_ops.c (all SQLite implementation)
  src/db_ops_postgres.c     — new file (stub initially)
  src/db_ops.c              — thin dispatcher that forwards to active backend
```

```
Tasks:
  2.1  Rename src/db_ops.c → src/db_ops_sqlite.c
  2.2  Prefix all function names in db_ops_sqlite.c with `sqlite_` (e.g., `sqlite_db_init()`)
  2.3  Create src/db_ops.c dispatcher with compile-time backend selection:
       - #ifdef DB_BACKEND_POSTGRES → call postgres_* functions
       - #else → call sqlite_* functions (default)
  2.4  Create src/db_ops_postgres.c with stub implementations that return DB_ERROR
  2.5  Update Makefile with DB_BACKEND variable:
       - `DB_BACKEND ?= sqlite`
       - Conditional source file inclusion and -lpq linking
  2.6  Build with DB_BACKEND=sqlite — verify identical behavior
  2.7  Build with DB_BACKEND=postgres — verify it compiles (stubs return errors)
```

### Phase 3: PostgreSQL Schema

**Goal**: Create the PostgreSQL schema file and apply it to the database.

```
Tasks:
  3.1  Create src/pg_schema.sql with PostgreSQL DDL from the plan:
       - events table with JSONB tags column
       - GIN index on tags
       - config, auth_rules, relay_seckey, subscriptions, ip_bans tables
       - Materialized views for dashboard
  3.2  Create src/pg_schema.h — embedded schema as C string (like sql_schema.h)
  3.3  Add `postgres_db_apply_schema()` function in db_ops_postgres.c
  3.4  Apply schema to the crelay database and verify with psql
```

### Phase 4: Implement db_ops_postgres.c — Core Functions

**Goal**: Implement the PostgreSQL backend for all `db_ops.h` functions using libpq.

This is the largest phase. Functions are grouped by dependency order.

#### 4A: Connection Management
```
Tasks:
  4A.1  Implement postgres_db_init() — PQconnectdb() with connection string
  4A.2  Implement postgres_db_close() — PQfinish()
  4A.3  Implement postgres_db_is_available() — PQstatus() check
  4A.4  Implement postgres_db_last_error() — PQerrorMessage()
  4A.5  Implement postgres_db_get_database_path() — return connection string
  4A.6  Implement thread-local connection pool:
        - postgres_db_set_thread_connection()
        - postgres_db_clear_thread_connection()
        - postgres_db_open_worker_connection() — new PQconnectdb per worker
        - postgres_db_close_worker_connection() — PQfinish per worker
```

#### 4B: Statement API (db_stmt_t wrapper for PGresult)
```
Tasks:
  4B.1  Define postgres db_stmt_t struct wrapping PGresult + cursor state
  4B.2  Implement postgres_db_prepare() — PQprepare() with auto-generated name
  4B.3  Implement postgres_db_bind_text_param/int_param/int64_param
        Note: libpq uses $1,$2 params, not ?. Need parameter array approach.
  4B.4  Implement postgres_db_step_stmt() — PQexecPrepared() + row iteration
  4B.5  Implement postgres_db_column_text_value/int_value/int64_value/double_value
  4B.6  Implement postgres_db_reset_stmt() and postgres_db_finalize_stmt()
  4B.7  Implement postgres_db_exec_sql() — PQexec() for DDL/DML
```

**Key design decision**: libpq doesn't have a step-based cursor like SQLite. Two approaches:
1. **PQexecPrepared** returns all rows at once — iterate with row index
2. **DECLARE CURSOR / FETCH** for streaming large result sets

For this relay's workload (most queries return <1000 rows), approach 1 is simpler and sufficient.

#### 4C: Config & Key Storage
```
Tasks:
  4C.1  Implement postgres_db_get_all_config_rows()
  4C.2  Implement postgres_db_get_config_value_dup()
  4C.3  Implement postgres_db_set_config_value_full()
        — Use INSERT ... ON CONFLICT (key) DO UPDATE instead of INSERT OR REPLACE
  4C.4  Implement postgres_db_update_config_value_only()
        — Use EXTRACT(EPOCH FROM NOW()) instead of strftime('%s','now')
  4C.5  Implement postgres_db_upsert_config_value()
  4C.6  Implement postgres_db_store_relay_private_key_hex()
  4C.7  Implement postgres_db_get_relay_private_key_hex_dup()
  4C.8  Implement postgres_db_store_config_event()
  4C.9  Implement postgres_db_get_config_row_count()
```

#### 4D: Event Storage & Retrieval
```
Tasks:
  4D.1  Implement postgres_db_insert_event_with_json()
        — INSERT ... ON CONFLICT (id) DO NOTHING
        — Map extended_errcode to DB_CONSTRAINT for duplicates
  4D.2  Implement postgres_db_event_id_exists()
  4D.3  Implement postgres_db_retrieve_event_by_id()
  4D.4  Implement postgres_db_get_event_pubkey()
  4D.5  Implement postgres_db_get_event_time_bounds()
  4D.6  Implement postgres_db_get_latest_event_pubkey_for_kind_dup()
```

#### 4E: Event Deletion (NIP-09)
```
Tasks:
  4E.1  Implement postgres_db_delete_event_by_id()
  4E.2  Implement postgres_db_delete_events_by_address()
```

#### 4F: Tag Operations
```
Tasks:
  4F.1  Implement postgres_db_store_event_tags_cjson()
        — Note: With JSONB+GIN, this may become a no-op for PostgreSQL
        — Tags are already stored in the events.tags JSONB column
  4F.2  Implement postgres_db_populate_event_tags_from_existing()
        — No-op for PostgreSQL (no separate event_tags table needed)
```

#### 4G: Auth Rules
```
Tasks:
  4G.1  Implement postgres_db_is_pubkey_blacklisted()
  4G.2  Implement postgres_db_is_hash_blacklisted()
  4G.3  Implement postgres_db_is_pubkey_whitelisted()
  4G.4  Implement postgres_db_count_active_whitelist_rules()
  4G.5  Implement postgres_db_add_auth_rule()
  4G.6  Implement postgres_db_remove_auth_rule()
  4G.7  Implement postgres_db_delete_wot_whitelist_rules()
  4G.8  Implement postgres_db_count_wot_whitelist_rules()
```

#### 4H: Subscription Logging
```
Tasks:
  4H.1  Implement postgres_db_log_subscription_created()
        — Use INSERT ... ON CONFLICT DO UPDATE
  4H.2  Implement postgres_db_log_subscription_closed()
        — Use EXTRACT(EPOCH FROM NOW()) for timestamps
  4H.3  Implement postgres_db_log_subscription_disconnected()
  4H.4  Implement postgres_db_update_subscription_events_sent()
  4H.5  Implement postgres_db_cleanup_orphaned_subscriptions()
```

#### 4I: Monitoring & Stats
```
Tasks:
  4I.1  Implement postgres_db_get_total_event_count_ll()
  4I.2  Implement postgres_db_get_event_count_since()
  4I.3  Implement postgres_db_get_event_kind_distribution_rows()
  4I.4  Implement postgres_db_get_top_pubkeys_rows()
  4I.5  Implement postgres_db_get_subscription_details_rows()
  4I.6  Implement postgres_db_count_with_sql()
  4I.7  Implement postgres_db_execute_readonly_query_json()
        — Map PG column types instead of SQLITE_INTEGER/SQLITE_TEXT/etc.
```

#### 4J: Schema & DDL Helpers
```
Tasks:
  4J.1  Implement postgres_db_table_exists()
        — Use information_schema.tables instead of sqlite_master
  4J.2  Implement postgres_db_get_schema_version_dup()
  4J.3  Implement postgres_db_wal_checkpoint_passive() — no-op for PostgreSQL
  4J.4  Implement postgres_db_wal_checkpoint_truncate() — no-op for PostgreSQL
```

### Phase 5: Fix SQLite-Specific SQL in Callers

**Goal**: Ensure SQL strings passed through `db_prepare()` / `db_exec_sql()` from outside `db_ops` are portable.

```
Tasks:
  5.1  Audit ip_ban.c SQL strings:
       — Replace `strftime('%s', 'now')` with `EXTRACT(EPOCH FROM NOW())::BIGINT`
         or pass timestamp as bind parameter from C (time(NULL))
       — Replace `INSERT OR REPLACE` with `INSERT ... ON CONFLICT DO UPDATE`
  5.2  Audit config.c SQL strings:
       — Replace `sqlite_master` query with db_table_exists()
       — Replace `INSERT OR REPLACE` with ON CONFLICT syntax
  5.3  Audit main.c init_database():
       — Move all PRAGMA calls behind db_ops backend-specific init
       — Move schema migration behind db_apply_schema()
  5.4  Strategy decision: use C-side timestamps (time(NULL)) as bind params
       instead of database-side timestamp functions for portability
```

### Phase 6: CLI Connection String Support

**Goal**: Accept PostgreSQL connection parameters from the command line.

```
Tasks:
  6.1  Add --db-host, --db-port, --db-name, --db-user, --db-password CLI options
  6.2  Add --db-connstring option for full libpq connection string
  6.3  Construct connection string from individual params if --db-connstring not given
  6.4  Pass connection string to db_init() (works for both backends)
  6.5  Update make_and_restart_relay.sh to support PostgreSQL connection params
  6.6  Update AGENTS.md with new CLI options
```

### Phase 7: Integration Testing

**Goal**: Verify the PostgreSQL backend passes all existing tests.

```
Tasks:
  7.1  Build with DB_BACKEND=postgres
  7.2  Start relay with PostgreSQL connection string
  7.3  Run tests/run_nip_tests.sh — verify NIP compliance
  7.4  Run tests/run_all_tests.sh — verify full test suite
  7.5  Run tests/performance_benchmarks.sh — compare with SQLite baseline
  7.6  Test first-time startup flow (key generation, schema creation)
  7.7  Test config event storage and retrieval
  7.8  Test auth rules (whitelist/blacklist)
  7.9  Test IP ban persistence across restarts
  7.10 Test thread pool worker connections with PostgreSQL
```

### Phase 8: Query Builder — JSONB Tag Queries

**Goal**: Replace the `event_tags` JOIN queries with PostgreSQL JSONB containment queries for tag filtering in REQ handling.

```
Tasks:
  8.1  Identify where REQ filter → SQL translation happens (websockets.c / subscriptions.c)
  8.2  Create backend-aware query builder or use #ifdef for tag filter SQL:
       — SQLite: `id IN (SELECT event_id FROM event_tags WHERE tag_name=? AND tag_value IN (?))`
       — PostgreSQL: `tags @> '[["p", "value"]]'::jsonb`
  8.3  Test tag-filtered REQ queries against PostgreSQL
  8.4  Verify GIN index is being used (EXPLAIN ANALYZE)
```

---

## SQL Dialect Mapping Reference

| SQLite | PostgreSQL |
|--------|-----------|
| `INSERT OR REPLACE INTO t ...` | `INSERT INTO t ... ON CONFLICT (pk) DO UPDATE SET ...` |
| `strftime('%s', 'now')` | `EXTRACT(EPOCH FROM NOW())::BIGINT` |
| `SELECT 1 FROM sqlite_master WHERE type='table' AND name=?` | `SELECT 1 FROM information_schema.tables WHERE table_name=?` |
| `PRAGMA journal_mode=WAL` | N/A (PostgreSQL handles WAL internally) |
| `PRAGMA mmap_size=N` | N/A |
| `PRAGMA cache_size=-N` | `SET shared_buffers` (server-level, not per-connection) |
| `sqlite3_wal_checkpoint_v2()` | N/A (no-op) |
| `sqlite3_changes()` | `PQcmdTuples(result)` |
| `sqlite3_extended_errcode()` | `PQresultErrorField(result, PG_DIAG_SQLSTATE)` |
| `?` bind parameter | `$1`, `$2`, ... positional parameters |
| `JSON` column type | `JSONB` column type |
| `json_each()` for tag queries | `@>` containment operator with GIN index |
| `INTEGER PRIMARY KEY` auto-increment | `SERIAL` or `BIGSERIAL` |

---

## Architecture After Implementation

```mermaid
flowchart TD
    subgraph Application Layer
        MAIN[main.c] --> DBOPS_H[db_ops.h - public API]
        CONFIG[config.c] --> DBOPS_H
        IPBAN[ip_ban.c] --> DBOPS_H
        SUBS[subscriptions.c] --> DBOPS_H
        API[api.c] --> DBOPS_H
        NIP09[nip009.c] --> DBOPS_H
        WS[websockets.c] --> DBOPS_H
    end

    subgraph Database Abstraction
        DBOPS_H --> DISPATCH[db_ops.c - dispatcher]
        DISPATCH -->|DB_BACKEND_SQLITE| SQLITE[db_ops_sqlite.c]
        DISPATCH -->|DB_BACKEND_POSTGRES| PG[db_ops_postgres.c]
    end

    subgraph Backends
        SQLITE --> SQLITE3[libsqlite3]
        PG --> LIBPQ[libpq]
        LIBPQ --> PGSERVER[PostgreSQL Server]
    end
```

---

## Risk Mitigation

| Risk | Mitigation |
|------|-----------|
| Breaking SQLite backend during refactor | Phase 1-2 are pure refactors — test suite must pass after each |
| libpq statement API mismatch | Use PQexecParams with row-index iteration, not cursor-based |
| SQL dialect differences in caller code | Phase 5 audits all SQL outside db_ops; use C timestamps for portability |
| Connection failures | Implement reconnection with exponential backoff in postgres_db_init |
| Thread safety | Each worker thread gets its own PGconn via db_open_worker_connection |
| Performance regression | Phase 7 benchmarks compare SQLite vs PostgreSQL on same workload |

---

## Build Commands

```bash
# SQLite backend (default, unchanged behavior)
make

# PostgreSQL backend
make DB_BACKEND=postgres

# Run with PostgreSQL
./build/c_relay_pg_x86 --db-connstring "host=localhost dbname=crelay user=crelay password=secret"
```