c-relay-pg/plans/caching_relay_integration_plan.md

Caching Relay PostgreSQL Inbox Integration Plan

Status: Revised and simplified architecture.

Decision: Keep caching as a separate application. The caching application focuses on polite upstream collection and writes raw event JSON to a minimal PostgreSQL inbox. c-relay-pg removes small priority-ordered batches and handles them through relay-owned event processing. The design deliberately accepts a small crash-loss window between dequeue and canonical storage; live overlap and backfill retries recover those events naturally.

1. Goal

Connect the standalone caching application to c-relay-pg without publishing cached events through a loopback WebSocket and without allowing the caching application to write directly to the canonical events table.

The responsibilities are intentionally narrow:

• Caching application: sensibly and politely acquire the desired events.
• PostgreSQL inbox: temporarily hold structurally plausible event JSON.
• c-relay-pg: remain the sole authority for accepting, storing, and broadcasting events.

This integration is PostgreSQL-only. The relay's normal SQLite operation remains unchanged, but the external caching service is unavailable with that backend.

2. Simplified Architecture

flowchart LR
    ROOT[Configured root npubs] --> CACHE[Caching application]
    UP[Upstream relays] --> CACHE
    CACHE --> INBOX[PostgreSQL caching event inbox]
    INBOX --> POLLER[c-relay-pg bounded poller]
    POLLER --> INGEST[Relay-owned event ingestion]
    INGEST --> EVENTS[Canonical events table]
    INGEST --> CLIENTS[Active subscriptions]

    UI[Caching admin page] --> CFG[Shared caching configuration]
    CFG --> CACHE
    CACHE --> STATUS[Service heartbeat and progress]
    STATUS --> UI

No loopback sink relay pool is used. No direct insert into the canonical event store is allowed from the caching application.

3. Deliberate Simplicity Rules

The first implementation will not include:

• multiple caching workers;
• multiple inbox consumers;
• row leases or claim expiration;
• a queue state machine;
• retry rows;
• a dead-letter queue;
• PostgreSQL LISTEN/NOTIFY;
• persistent relay-health history;
• exactly-once delivery;
• direct process start/stop through the web page;
• upstream NIP-42 authentication;
• support for the SQLite backend.

The design prefers harmless duplicate acquisition over complicated delivery coordination. Event IDs already provide natural deduplication in both the inbox and canonical store.

4. Responsibility Boundaries

4.1 Caching application

The caching application is responsible for:

1. Reading caching configuration from PostgreSQL.
2. Decoding configured root npubs.
3. Resolving the roots' newest kind-3 follow lists.
4. Discovering useful read relays from kind 10002.
5. Computing a reasonable covering relay set.
6. Maintaining live subscriptions for roots and follows.
7. Performing polite, resumable historical backfill.
8. Inserting received event JSON into the inbox.
9. Persisting only the progress needed to resume discovery and backfill.
10. Publishing a lightweight heartbeat and status snapshot.

It is not responsible for:

• deciding whether an event belongs in the canonical relay database;
• applying c-relay-pg publication authorization;
• processing NIP-09 deletions;
• implementing replacement semantics;
• executing relay administrator commands;
• broadcasting to connected relay clients.

The caching callback may perform minimal application-side checks before insert:

• the callback supplied a JSON object;
• an event ID string exists;
• the serialized event is below a configured maximum size.

Cryptographic verification is optional in the fetcher and is not trusted by c-relay-pg. The initial version should omit it unless the existing standalone code already provides it at negligible integration cost.

4.2 PostgreSQL inbox

The inbox is deliberately dumb. It rejects obvious malformed data, deduplicates events currently waiting in the inbox, and establishes live-over-backfill priority. It does not attempt to verify a Nostr hash or Schnorr signature.

4.3 c-relay-pg

c-relay-pg is responsible for:

1. Removing a bounded batch from the inbox.
2. Parsing each event JSON object.
3. Applying authoritative structural, ID, and signature validation.
4. Applying relay-wide event rules such as expiration and PoW where applicable.
5. Bypassing client-session NIP-42 requirements because the source is internal.
6. Preventing imported kind 23456 events from executing administrator commands.
7. Applying duplicate, ephemeral, replaceable, addressable, and NIP-09 behavior.
8. Storing accepted events through its normal database abstraction.
9. Running post-store work and broadcasting newly accepted events from the main libwebsockets thread.
10. Recording simple in-memory and cumulative import counters.

5. Minimal PostgreSQL Schema

Add the following objects to src/pg_schema.sql and the embedded PostgreSQL schema header.

5.1 caching_event_inbox

Suggested logical schema:

CREATE TABLE IF NOT EXISTS caching_event_inbox (
    queue_id BIGSERIAL PRIMARY KEY,
    event_id TEXT NOT NULL UNIQUE,
    event_json JSONB NOT NULL,
    source_relay TEXT,
    source_class TEXT NOT NULL DEFAULT 'backfill',
    priority SMALLINT NOT NULL DEFAULT 1,
    received_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,

    CHECK (jsonb_typeof(event_json) = 'object'),
    CHECK (jsonb_typeof(event_json->'id') = 'string'),
    CHECK (length(event_json->>'id') = 64),
    CHECK (event_id = event_json->>'id'),
    CHECK (jsonb_typeof(event_json->'pubkey') = 'string'),
    CHECK (length(event_json->>'pubkey') = 64),
    CHECK (jsonb_typeof(event_json->'sig') = 'string'),
    CHECK (length(event_json->>'sig') = 128),
    CHECK (jsonb_typeof(event_json->'created_at') = 'number'),
    CHECK (jsonb_typeof(event_json->'kind') = 'number'),
    CHECK (jsonb_typeof(event_json->'tags') = 'array'),
    CHECK (jsonb_typeof(event_json->'content') = 'string'),
    CHECK (source_class IN ('live', 'discovery', 'backfill')),
    CHECK (priority IN (0, 1))
);

CREATE INDEX IF NOT EXISTS idx_caching_inbox_dequeue
    ON caching_event_inbox(priority, received_at, queue_id);

Priority meanings:

• 0: live and discovery metadata;
• 1: historical backfill.

The caching application uses a parameterized insert with ON CONFLICT (event_id) DO NOTHING. It must impose a serialized event-size limit before sending the row. PostgreSQL schema constraints are structural protection, not authoritative Nostr validation.

5.2 caching_service_state

Use one singleton row for inexpensive UI status:

• service version;
• service state: starting, running, degraded, or stopped;
• applied config generation;
• heartbeat timestamp;
• followed author count;
• selected and connected relay counts;
• current backfill window and author cursor;
• events fetched and inbox inserts;
• last error text and timestamp.

This is a status snapshot, not an audit log. The caching process overwrites the same row periodically.

5.3 caching_backfill_progress

Persist only enough state for the caching application to resume politely:

• author pubkey;
• window index;
• immutable window anchor;
• current inclusive until cursor;
• completion flag;
• updated timestamp.

Use a composite primary key on author and window. Do not store individual fetched event IDs here; inbox and canonical event IDs handle deduplication.

Timestamp-only Nostr pagination can saturate when many events share one second. For the first version, increase the query limit up to a fixed safety ceiling when a full page ends at one timestamp. If that ceiling remains saturated, leave the author incomplete, log it, and retry later rather than falsely marking it done.

6. Simple Destructive Dequeue

c-relay-pg uses one PostgreSQL transaction to remove and return a small batch:

WITH selected AS (
    SELECT queue_id
    FROM caching_event_inbox
    ORDER BY priority ASC, received_at ASC, queue_id ASC
    LIMIT $1
    FOR UPDATE
)
DELETE FROM caching_event_inbox AS inbox
USING selected
WHERE inbox.queue_id = selected.queue_id
RETURNING inbox.event_id,
          inbox.event_json,
          inbox.source_relay,
          inbox.source_class,
          inbox.received_at;

Only one c-relay-pg inbox consumer is supported. SKIP LOCKED, leases, and claim owners are therefore unnecessary.

6.1 Accepted trade-off

There is a small loss window if c-relay-pg commits the delete and crashes before storing the returned events. This is accepted deliberately:

• live subscriptions reconnect with overlap;
• backfill revisits incomplete and steady-state windows;
• duplicate reacquisition is safe;
• the reduced implementation complexity is worth the rare temporary loss.

The poller should keep batches small so the loss window and memory footprint are small.

7. c-relay-pg Inbox Consumer

7.1 Polling

• Compile the consumer only for DB_BACKEND_POSTGRES.
• Poll a small batch at a configurable interval.
• Poll quickly while rows are found and back off to a slower interval when empty.
• Always order live/discovery rows before backfill rows.
• Allow only one batch in memory at a time.
• Stop polling immediately during relay shutdown.

Initial conservative defaults should be small, for example a few dozen rows per batch and subsecond-to-multisecond active polling with a longer idle interval. Exact defaults should be selected during integration testing rather than encoded as architectural requirements.

7.2 Ingestion behavior

Do not send dequeued events through a loopback WebSocket. Refactor the existing inbound event handling into a reusable internal ingestion entry point with an explicit source mode:

• CLIENT_EVENT: normal client authentication and response behavior;
• CACHING_INBOX_EVENT: no client session or NIP-42 requirement, no OK response, and no administrator command execution.

The shared path should preserve:

• event ID and signature verification;
• event limits;
• expiration and PoW behavior;
• NIP-09 authorization and deletion behavior;
• ephemeral handling;
• PostgreSQL replacement semantics in postgres_db_insert_event_with_json();
• duplicate detection;
• post-store monitoring;
• main-thread broadcast.

Avoid creating a second implementation of these rules solely for inbox events.

7.3 Main-thread broadcast

Database and cryptographic work may run off the libwebsockets thread, but store_event_post_actions() and active subscription broadcast must be queued to the main thread, following the existing asynchronous completion pattern in process_async_event_completions().

Newly stored events are broadcast. Duplicates and stale replacements are not. Validated ephemeral events are broadcast but not stored.

7.4 Failure behavior

Because rows have already been removed, malformed or invalid events are simply counted and logged at a rate-limited level. They are not requeued.

A temporary canonical database failure should stop further inbox polling until the relay database is healthy. The current in-memory batch may be lost under the accepted simple-delivery model.

8. Caching Application Acquisition Strategy

The external application should spend most of its design effort here.

8.1 Follow graph

• Decode configured root npubs.
• Fetch the newest kind 3 for each root from bootstrap relays.
• Include roots themselves in the author set.
• Parse valid p tags, deduplicate authors, and enforce a configured author cap.
• Refresh periodically and replace live subscriptions when the set changes.

Local-first querying is optional in this architecture. PostgreSQL already holds canonical kind-3 events, so the caching application may query the canonical event store read-only for discovery metadata before contacting upstream relays. It must not write canonical rows.

8.2 NIP-65 relay discovery

• Fetch newest kind 10002 events in bounded author batches.
• Use unmarked or read relay tags; ignore write-only tags.
• Normalize and deduplicate wss:// URLs.
• Enforce per-author and global relay caps.
• Use the greedy covering-set logic from the standalone implementation.
• Retain configured bootstrap relays as fallback.

8.3 Friendly live subscriptions

• Separate root and followed-author subscriptions when their kind lists differ.
• Batch author lists into reasonable filter sizes.
• Reconnect with bounded exponential backoff and jitter.
• Use a small timestamp overlap when resubscribing.
• Insert callback event JSON into the inbox and continue; do not wait for c-relay-pg processing.

8.4 Friendly backfill

• Use recent-first progressive windows.
• Persist an immutable anchor for each active window.
• Query one author at a time from that author's declared outbox relays, with bootstrap fallback.
• Use configurable page size and delay between queries.
• Apply relay-specific backoff after errors or timeouts.
• Keep global and per-relay request rates low.
• Advance progress only after the returned page has been inserted into the inbox or identified as already queued.
• Periodically repeat a bounded widest-window sweep after initial completion.

9. Shared Configuration

Keep administrator intent in the existing config table with category caching and requires_restart = 0.

Suggested keys:

• caching_enabled;
• caching_config_generation;
• caching_root_npubs;
• caching_bootstrap_relays;
• caching_kinds;
• caching_admin_kinds;
• caching_live_enabled;
• caching_live_resubscribe_seconds;
• caching_backfill_enabled;
• caching_backfill_windows;
• caching_backfill_page_size;
• caching_backfill_tick_interval_ms;
• caching_backfill_window_cooldown_seconds;
• caching_follow_graph_refresh_seconds;
• caching_relay_discovery_refresh_seconds;
• caching_max_followed_pubkeys;
• caching_max_upstream_relays;
• caching_max_relays_per_pubkey;
• caching_query_timeout_ms;
• caching_inbox_batch_size;
• caching_inbox_active_poll_ms;
• caching_inbox_idle_poll_ms;
• caching_max_event_json_bytes.

The web UI updates these through the existing encrypted administrator API. After a successful update, increment caching_config_generation. The caching application polls the generation and reloads valid changes. No cross-process command queue is needed.

caching_enabled = false tells the external application to close upstream activity. systemd or the container runtime, not the web page, owns the process lifecycle.

10. Admin UI

10.1 Sidenav placement

In api/index.html, insert Caching immediately after Relay Events and before DM:

1. Statistics
2. Subscriptions
3. Configuration
4. Authorization
5. IP BAN
6. Relay Events
7. Caching
8. DM
9. Database Query

Register cachingSection in switchPage().

10.2 Page layout

Reuse the existing admin UI classes and divide the page into three blocks.

Service status

• enabled configuration;
• service heartbeat and state;
• desired and applied config generation;
• followed author count;
• selected and connected relay counts;
• current backfill window and cursor;
• fetched and inbox-inserted counters;
• last service error.

Relay inbox status

• pending live/discovery count;
• pending backfill count;
• oldest inbox row age;
• relay-consumed, accepted, duplicate, invalid, and failed counters;
• last successful inbox poll.

Configuration

• root npubs and bootstrap relays;
• normal and root kind lists;
• live and backfill toggles;
• backfill windows and pacing;
• discovery refresh intervals;
• author and relay safety caps;
• inbox batch and polling settings;
• Apply Configuration button;
• Reset Backfill Progress button with confirmation.

The UI reads service state from the singleton status row and lightweight inbox aggregates. Poll only while the Caching page is visible and use a modest refresh interval.

11. Process and Database Security

Use separate PostgreSQL roles:

• Caching service role: read caching configuration and permitted canonical discovery metadata; insert/select its own status and progress; insert into the inbox; no insert/update/delete permission on canonical events.
• c-relay-pg role: normal relay permissions plus dequeue permission on the inbox and read access to caching status.

Use parameterized SQL throughout. Restrict public upstreams to normalized wss:// URLs. Apply a database statement timeout to caching queries so the fetcher cannot hold resources indefinitely.

12. Lifecycle

12.1 Caching service

• Starts and stops independently under systemd or a container runtime.
• On startup, loads configuration and progress, updates heartbeat state, then begins discovery/live/backfill.
• On shutdown, closes subscriptions, saves current progress, marks status stopped, and disconnects from PostgreSQL.
• Its failure does not stop c-relay-pg; cached acquisition merely pauses.

12.2 c-relay-pg

• Starts the inbox poller only for PostgreSQL builds after database, writer pool, and WebSocket systems are ready.
• Stops new polls before shutting down the writer pool or WebSocket context.
• Drains already-created main-thread completions before destroying those dependencies.
• A missing inbox table should be logged as caching unavailable, not terminate the relay, unless schema migration policy requires otherwise.

13. Implementation Sequence

1. Add caching_event_inbox, caching_service_state, and caching_backfill_progress to the PostgreSQL schema and embedded schema.
2. Add PostgreSQL database abstraction functions for bounded destructive dequeue and lightweight inbox counts.
3. Refactor c-relay-pg inbound event processing into a shared source-aware ingestion entry point without changing normal client behavior.
4. Add the PostgreSQL-only inbox poller, bounded in-memory batch, and main-thread post-action/broadcast completion path.
5. Add caching configuration defaults and validation to c-relay-pg.
6. Adapt the standalone caching application to read PostgreSQL configuration, insert raw event JSON into the inbox, and update heartbeat/status.
7. Correct the standalone backfill implementation so limited queries paginate and do not falsely complete an author/window.
8. Persist simple per-author/window progress and implement polite retry/backoff.
9. Add the Caching page after Relay Events and before DM, including service, inbox, configuration, and reset-progress controls.
10. Add PostgreSQL role/grant documentation and systemd/container deployment examples for the separate caching process.
11. Test live priority, duplicate acquisition, invalid inbox rows, replacement, deletion, ephemeral broadcast, process restarts, destructive-dequeue crash behavior, upstream outages, and graceful shutdown.
12. Build and validate c-relay-pg only through make_and_restart_relay.sh, using --preserve-database where test state must survive.

14. Acceptance Criteria

• The caching application cannot write canonical event rows.
• Received upstream events require only minimal fetcher checks before inbox insertion.
• Inbox constraints reject structurally implausible rows and deduplicate pending event IDs.
• c-relay-pg removes small batches ordered live/discovery before backfill.
• c-relay-pg remains the authoritative ID/signature validator and storage owner.
• Imported events never execute relay administrator commands or require a client NIP-42 session.
• Newly accepted events are broadcast to active subscriptions from the main libwebsockets thread.
• Duplicate and stale replacement events are not rebroadcast.
• The caching service behaves politely through bounded filters, paced backfill, targeted outbox queries, and retry backoff.
• Backfill progress survives caching-service restarts and does not falsely mark saturated timestamps complete.
• A caching-service failure does not stop the relay.
• The accepted destructive-dequeue crash window is documented and recoverable through live overlap and repeated backfill.
• The Caching UI appears after Relay Events and before DM and distinguishes external service health from relay inbox consumption.