c-relay-pg/plans/forward_catchup_plan.md

Forward Catch-Up Plan: Bridging the Gap When Caching Resumes

Problem

When caching is turned off (or the caching service stops), events posted by followed authors during the downtime are missed. The current backfill walks backward from until_cursor toward the beginning of time — it does not cover events newer than the cursor. The live subscriber uses since = time(NULL), so it only catches events from the moment it connects. Events in the gap between "last event we know about" and "caching resumed" are lost.

Schema Change: last_event_at column

Add a last_event_at BIGINT NOT NULL DEFAULT 0 column to caching_followed_pubkeys. This records the created_at of the most recent event we've ever seen for this author — not a proxy like updated_at (which records when we last touched the row, which could be a zero-event progress write or a follow-graph refresh).

since = last_event_at + 1, until = now is exact: it catches every event the relay doesn't yet have, with no assumptions about when the last event occurred relative to when we last checked.

Population

• Backfill: when publishing a page of events, compute max(created_at) across the page (we already compute min(created_at) for the cursor advance — add a parallel max). Update last_event_at = GREATEST(last_event_at, page_max_created_at).
• Live subscriber: when an event is received and published, update last_event_at = GREATEST(last_event_at, event.created_at).
• One-time seed from events table: on schema upgrade, set last_event_at = COALESCE((SELECT MAX(created_at) FROM events WHERE pubkey = caching_followed_pubkeys.pubkey), 0) for all existing rows. This seeds the column from the relay's own data.

Migration

Added to src/pg_schema.sql and src/pg_schema.h:

ALTER TABLE caching_followed_pubkeys
    ADD COLUMN IF NOT EXISTS last_event_at BIGINT NOT NULL DEFAULT 0;

-- One-time seed from the events table.
UPDATE caching_followed_pubkeys fp
   SET last_event_at = COALESCE(
       (SELECT MAX(e.created_at) FROM events e WHERE e.pubkey = fp.pubkey),
       0
   )
 WHERE fp.last_event_at = 0;

No schema version bump needed — ALTER TABLE ADD COLUMN IF NOT EXISTS is idempotent and the seed UPDATE is guarded by WHERE last_event_at = 0.

Current Architecture

Tables

caching_followed_pubkeys (per-author state):

Column	Purpose
pubkey	PK
until_cursor	Unix timestamp; backfill queries until = this, walks backward
backfill_complete	TRUE when drained to the beginning of time
events_fetched	Cumulative count
last_seen	Updated on follow-graph refresh
updated_at	Updated on every backfill progress write and completion mark
last_event_at	NEW: created_at of the most recent event we've seen for this author

caching_backfill_relay_progress (per-author-per-relay state):

Column	Purpose
author_pubkey, relay_url	Composite PK
until_cursor	Per-relay backward-walk cursor
complete	TRUE when this relay is drained for this author
updated_at	Updated on every relay progress write

Backfill flow (caching/src/backfill.c)

1. Pick next incomplete author (round-robin)
2. For each incomplete relay for that author:
   • Query authors=[pk], until=until_cursor, limit=page_size
   • Publish events to the relay via the sink
   • Advance until_cursor to oldest_event_created_at - 1
   • If < page_size events + EOSE: mark relay complete = TRUE
3. If all relays complete: mark author backfill_complete = TRUE

Restart behavior

• Normal restart (no --restart): cursor and completion state preserved. Backfill resumes the backward walk from stored cursors. Gap not covered.
• --restart: all cursors reset to 0, all completion flags cleared. Full re-drain from now backward. Re-fetches everything but covers the gap incidentally (since it starts from now).

Live subscriber (caching/src/live_subscriber.c)

Uses since = time(NULL) — only catches events from the moment it connects. No gap-bridging.

The Gap

Time ──────────────────────────────────────────────────────►
       │                                      │           │
       last_event_at                          caching     now
       (most recent event                     turned      (caching
        we know about)                        off         resumed)
                                              │
                                              └── events posted here are missed

For completed authors (backfill_complete = TRUE): the backward walk is done. last_event_at tells us the most recent event we have. Events posted after last_event_at are in the gap.

For incomplete authors: the backward walk is still in progress. The cursor is somewhere in the past, walking backward. Events newer than the cursor are not fetched by backfill. The live subscriber covers events from now forward. The gap is between last_event_at and now.

Solution: Forward Catch-Up Phase

Add a forward catch-up phase that runs once when the caching service starts (or when backfill is re-enabled), before the normal backward-drain backfill loop begins.

Logic

For every followed author (both complete and incomplete):

1. Read last_event_at from caching_followed_pubkeys.
2. If last_event_at = 0, skip (no events known yet — the backward drain will handle it).
3. Query one or two outbox relays: authors=[pk], since=last_event_at + 1, until=now, limit=page_size
4. Publish all returned events to the relay via the sink.
5. Update last_event_at to the max created_at seen (or now if no events were returned, to avoid re-querying the same empty window next time).

This is safe for both complete and incomplete authors:

• Complete authors: the backward drain is done, so the forward catch-up is the only thing needed.
• Incomplete authors: the backward drain walks below until_cursor, so events above until_cursor up to last_event_at were already fetched during the initial drain (when until_cursor started at now). The forward catch-up fills from last_event_at + 1 to now — the gap that formed while caching was off.

When to run

• On caching service startup (not --restart, which does a full reset).
• The caching service process starts when caching_enabled is turned on, so this covers the "caching was off, now it's on" case.

Implementation

1. Schema: add last_event_at column

In src/pg_schema.sql and src/pg_schema.h:

ALTER TABLE caching_followed_pubkeys
    ADD COLUMN IF NOT EXISTS last_event_at BIGINT NOT NULL DEFAULT 0;

UPDATE caching_followed_pubkeys fp
   SET last_event_at = COALESCE(
       (SELECT MAX(e.created_at) FROM events e WHERE e.pubkey = fp.pubkey),
       0
   )
 WHERE fp.last_event_at = 0;

2. New function: pg_inbox_get_authors_for_catchup()

In caching/src/pg_inbox.c:

/* Returns a cJSON array of {pubkey, last_event_at} objects for all
 * followed authors where last_event_at > 0. Caller must cJSON_Delete().
 * Returns NULL on error. */
cJSON* pg_inbox_get_authors_for_catchup(void);

SQL:

SELECT pubkey, last_event_at
  FROM caching_followed_pubkeys
 WHERE last_event_at > 0
 ORDER BY last_event_at ASC

3. New function: pg_inbox_update_last_event_at()

/* Update last_event_at for a pubkey to the max of current and new value. */
int pg_inbox_update_last_event_at(const char *pk, long event_created_at);

SQL:

UPDATE caching_followed_pubkeys
   SET last_event_at = GREATEST(last_event_at, $2::BIGINT)
 WHERE pubkey = $1

4. New function: cr_forward_catchup()

In a new file caching/src/forward_catchup.c:

/* Run forward catch-up for all followed authors.
 * For each author with last_event_at > 0, query events from
 * last_event_at + 1 to now and publish them to the sink.
 * Returns 0 on success, -1 on error. */
int cr_forward_catchup(cr_config_t *cfg,
                        nostr_relay_pool_t *upstream,
                        cr_sink_t *sink);

Flow:

1. Call pg_inbox_get_authors_for_catchup() to get the list.
2. For each author: a. Get the author's outbox relays from caching_backfill_relay_progress (any relay, since we just need one good source). b. Query authors=[pk], since=last_event_at + 1, until=now, limit=page_size on one relay. c. Publish all returned events to the sink. d. If events were returned, update last_event_at to the max created_at in the batch. If no events, update last_event_at to now (so we don't re-query the same empty window).
3. Log: "forward catch-up: N authors checked, M events published".

5. Update backfill to maintain last_event_at

In caching/src/backfill.c, in the page-publishing loop (around line 375):

• Add a find_newest_created_at() helper (parallel to the existing find_oldest_created_at()).
• After publishing a page, call pg_inbox_update_last_event_at(pk, newest_created_at).

6. Update live subscriber to maintain last_event_at

In caching/src/live_subscriber.c, in the event-received callback:

• Extract created_at from the event.
• Call pg_inbox_update_last_event_at(pubkey, created_at).

7. Call from main.c

In caching/src/main.c, after relay discovery and followed-set sync, before the main loop:

/* Forward catch-up: bridge the gap for all followed authors. */
if (cfg->backfill.enabled && pg_conn && !restart) {
    DEBUG_INFO("forward catch-up: checking for missed events");
    cr_forward_catchup(&cfg, upstream, &sink);
}

This runs once at startup. It's not throttled — it's a one-time pass.

Edge cases

• --restart flag: full reset already starts from now, so forward catch-up is skipped. The last_event_at seed from the events table will set it to the most recent known event, and the backward drain from now will cover everything.
• last_event_at = 0: author has no known events. Skip — the backward drain handles it.
• Very large gap (caching off for months): the forward catch-up query may return many events. Use limit = page_size and paginate if needed. The relay's own dedup (unique index on event ID) handles duplicates.
• Relay doesn't support since: most Nostr relays support since/ until (NIP-01). If ignored, the relay returns all events — dedup handles it.

Files to change

File	Change
src/pg_schema.sql	ALTER TABLE add last_event_at + seed from events
src/pg_schema.h	Mirror the above
caching/src/forward_catchup.c	New: cr_forward_catchup()
caching/src/forward_catchup.h	New: declaration
caching/src/pg_inbox.c	New: pg_inbox_get_authors_for_catchup(), pg_inbox_update_last_event_at()
caching/src/pg_inbox.h	New: declarations
caching/src/backfill.c	Add find_newest_created_at(), call pg_inbox_update_last_event_at() after each page
caching/src/live_subscriber.c	Call pg_inbox_update_last_event_at() on event receipt
caching/src/main.c	Call cr_forward_catchup() at startup
caching/Makefile	Add forward_catchup.c to sources

Sequencing

graph TD
    A[Caching service starts] --> B{Is --restart?}
    B -- Yes --> C[Reset all progress, full re-drain from now]
    B -- No --> D[Forward catch-up: all authors with last_event_at > 0]
    D --> E[Normal backward-drain backfill loop]
    E --> F[Live subscriber: since = now, ongoing]
    F --> G[Live subscriber updates last_event_at on each event]
    E --> H[Backfill updates last_event_at on each page]