c-relay-pg/plans/eliminate_g_db_plan.md

Plan: Eliminate g_db from the Main Thread

Problem Statement

All SQLite calls go through db_ops.c, but db_active_connection() falls back to g_db when g_thread_db is NULL. Since the main thread never sets g_thread_db, every db_* call from lws-main executes synchronous SQLite on the main thread. The thread pool workers have their own connections and work correctly — the problem is the fallback path.

Current perf data shows lws-main at 67.6% avg CPU, dominated by SQLite symbols (sqlite3BtreeTableMoveto, sqlite3VdbeExec, pcache1Fetch).

Goal

Remove g_db as a runtime connection used by the main thread. After this change:

• The main thread has no SQLite connection and cannot execute synchronous queries
• All DB work routes through thread pool workers (which have their own connections)
• g_db is only used during startup/shutdown (before/after the event loop)
• Any accidental db_* call from lws-main returns an error instead of silently blocking

Architecture After Change

lws-main thread:
  - WebSocket protocol handling
  - Message parsing/routing
  - Completion queue draining
  - NO SQLite access during event loop

db-read-0 thread:
  - REQ queries
  - COUNT queries
  - Config cache miss reads
  - Auth rule checks
  - Monitoring/stats queries

db-write thread:
  - EVENT inserts
  - Subscription logging
  - Config updates
  - Auth rule modifications

event-worker thread:
  - Signature validation
  - Duplicate check
  - Store via db-write

Categorized g_db Call Sites

Category A: Startup-only — keep using g_db

These run before the event loop starts. They are fine.

Function	File	Purpose
db_init()	db_ops.c:27	Open database
db_exec_sql() for PRAGMAs	main.c:854-882	WAL, mmap, cache setup
db_table_exists()	main.c:750	Schema check
db_get_schema_version_dup()	main.c:754	Migration check
db_exec_sql() for schema	main.c:842	Create tables
populate_all_config_values_atomic()	config.c:4290	First-time config
populate_default_config_values()	config.c:1657	Default config
add_pubkeys_to_config_table()	config.c:1778	Pubkey storage
apply_cli_overrides_atomic()	config.c:4211	CLI overrides
db_store_relay_private_key_hex()	config.c:495	Key storage
db_populate_event_tags_from_existing()	main.c:1156	Tag migration
cleanup_all_subscriptions_on_startup()	subscriptions.c:1092	Orphan cleanup

Category B: Shutdown-only — keep using g_db

Function	File	Purpose
db_exec_sql() WAL checkpoint	main.c:900	Clean shutdown
db_close()	main.c:904	Close connection

Category C: Runtime hot path — must move off main thread

These are called during the event loop from lws-main context.

Function	File	Called from	Fix strategy
db_get_config_value_dup()	db_ops.c:777	config cache miss	Already cached with 5s TTL; pre-warm cache at startup so misses are rare
store_event() for kind 14/1059	main.c:983	websockets.c sync path	Route through async event worker
store_event_post_actions()	main.c	completion handler	Already runs on main; its DB calls need routing
db_log_subscription_created()	db_ops.c:145	subscriptions.c	Fire-and-forget via write queue
db_log_subscription_closed()	db_ops.c:165	subscriptions.c	Fire-and-forget via write queue
db_log_subscription_disconnected()	db_ops.c:193	subscriptions.c	Fire-and-forget via write queue
db_update_subscription_events_sent()	db_ops.c:225	subscriptions.c	Fire-and-forget via write queue
db_cleanup_orphaned_subscriptions()	db_ops.c:244	subscriptions.c	Move to startup only
generate_and_post_status_event()	websockets.c:3408	periodic timer	Submit to write worker
ip_ban_cleanup() / ip_ban_log_stats()	websockets.c:3196	periodic timer	Submit to write worker
db_get_total_event_count_ll()	db_ops.c:589	api.c monitoring	Submit to read worker
db_get_event_count_since()	db_ops.c:606	api.c monitoring	Submit to read worker
db_get_event_kind_distribution_rows()	db_ops.c:625	api.c monitoring	Submit to read worker
db_get_top_pubkeys_rows()	db_ops.c:663	api.c monitoring	Submit to read worker
db_get_subscription_details_rows()	db_ops.c:697	api.c monitoring	Submit to read worker
db_get_all_config_rows()	db_ops.c:745	api.c config query	Submit to read worker
db_execute_readonly_query_json()	db_ops.c:442	api.c SQL query	Submit to read worker
db_event_id_exists()	db_ops.c:1041	main.c event check	Already moved to event worker
db_retrieve_event_by_id()	db_ops.c:1056	main.c	Submit to read worker
db_get_event_pubkey()	db_ops.c:262	NIP-09 deletion	Submit to read worker
db_delete_event_by_id()	db_ops.c:285	NIP-09 deletion	Submit to write worker
db_delete_older_replaceable_events()	db_ops.c:305	store_event_core	Already on write worker
db_store_config_event()	db_ops.c:888	config.c	Submit to write worker
db_add_auth_rule()	db_ops.c:1226	config.c admin	Submit to write worker
db_remove_auth_rule()	db_ops.c:1242	config.c admin	Submit to write worker
db_delete_wot_whitelist_rules()	db_ops.c:1258	config.c WoT	Submit to write worker
db_count_wot_whitelist_rules()	db_ops.c:1266	config.c	Cache or read worker
db_store_event_tags_cjson()	db_ops.c:1140	main.c post-actions	Already on write worker path
db_get_config_row_count()	db_ops.c:1121	config.c diagnostics	Cache or read worker
db_set_config_value_full()	db_ops.c:796	config.c	Submit to write worker
db_update_config_value_only()	db_ops.c:821	config.c	Submit to write worker
db_upsert_config_value()	db_ops.c:838	api.c	Submit to write worker
db_exec_sql() for transactions	config.c	admin events	Submit to write worker
db_count_with_sql()	db_ops.c:415	config.c admin	Submit to read worker
is_config_table_ready()	config.c:4595	config.c hybrid	Cache result at startup
generate_config_event_from_table()	config.c:4933	config.c	Cache or read worker

Category D: Auth rule checks — already use separate connections

These functions in db_ops.c open their own temporary read-only connections:

Function	Line	Notes
db_is_pubkey_blacklisted()	347	Opens own connection
db_is_hash_blacklisted()	362	Opens own connection
db_is_pubkey_whitelisted()	377	Opens own connection
db_count_active_whitelist_rules()	392	Opens own connection

These are already safe — they don't use g_db. No change needed.

Implementation Strategy

Phase 1: Pre-warm caches and eliminate cache-miss DB reads

1. Pre-warm the config cache at startup by loading all config values into the in-memory cache before the event loop starts
2. Pre-warm the NIP-11 cache at startup
3. Pre-warm the auth rules fast cache at startup
4. This eliminates the most frequent cache-miss db_get_config_value_dup() calls

Phase 2: Route subscription logging through write worker

1. Add a THREAD_POOL_JOB_FIRE_AND_FORGET job type to thread_pool
2. Create async wrappers: db_log_subscription_created_async(), etc.
3. These submit SQL to the write worker queue and return immediately
4. No completion callback needed — fire and forget

Phase 3: Route special-kind EVENT store through async worker

1. Remove the event_is_async_eligible() exclusion for kinds 14, 1059, 23456
2. For kind 23456: process admin command in completion handler on main thread after store
3. For kind 14/1059: process NIP-17 DM in completion handler after store

Phase 4: Route periodic timer DB work through workers

1. generate_and_post_status_event() — submit event creation to write worker
2. ip_ban_cleanup() / ip_ban_log_stats() — submit to write worker
3. Monitoring queries — submit to read worker with completion callback

Phase 5: Null out g_db before event loop

1. After startup is complete and thread pool is initialized, set g_db = NULL
2. Change db_active_connection() to assert/warn if both g_thread_db and g_db are NULL
3. Any accidental main-thread DB call will now fail loudly instead of silently blocking
4. Restore g_db briefly for shutdown checkpoint

Implementation Order

1. Phase 1 — lowest risk, immediate benefit from eliminating cache misses
2. Phase 2 — straightforward fire-and-forget pattern
3. Phase 3 — moderate complexity, needs careful completion handler design
4. Phase 4 — moderate complexity, periodic tasks need async patterns
5. Phase 5 — the final enforcement step, only safe after phases 1-4

Risk Assessment

• Phase 1: Very low risk — just pre-warming existing caches
• Phase 2: Low risk — subscription logging is non-critical, fire-and-forget is safe
• Phase 3: Medium risk — admin/DM event processing has complex state; needs careful testing
• Phase 4: Medium risk — periodic tasks have side effects that need to complete
• Phase 5: High risk if done prematurely — must verify ALL runtime paths are covered first

Expected Impact

After all phases:

• lws-main CPU should drop from ~67% to near 0% SQLite overhead
• All SQLite work happens on db-read and db-write threads
• Main thread only does WebSocket I/O, JSON parsing, and completion queue draining
• Thread model becomes: lws-main = pure I/O, workers = all DB