c-relay-pg/plans/backfill_relay_by_relay_plan.md

Backfill v2: Relay-by-Relay Per-Author Until-Cursor Drain

Overview

Refine the per-author until-cursor backfill to query each relay one at a time with per-relay cursor tracking, and pick up new follows immediately when the admin's kind-3 contact list changes.

Problem with the current implementation

The current backfill (caching/src/backfill.c) calls nostr_relay_pool_query_sync with all relay URLs at once. This function:

1. Sends the same REQ to all connected relays
2. Collects and deduplicates events from all relays into one merged set
3. Waits until ALL relays send EOSE, or the timeout (now 30s) expires
4. Returns the merged set — but does not tell the caller whether it exited via all-EOSE or timeout

If one relay is slow (e.g. nos.lol streaming 500+ events), the timeout can expire before that relay sends EOSE. The result is a partial event set, but the backfill code can't distinguish "partial due to timeout" from "complete because the author only has 225 events." This causes premature completion marking.

Additionally, the current design has a single until_cursor per author in caching_followed_pubkeys, which doesn't track per-relay progress. And new follows are only discovered on the 10-minute follow-graph refresh cycle.

Solution

1. Per-relay cursor tracking

Add a new table to track the backfill cursor for each author on each relay independently:

CREATE TABLE IF NOT EXISTS caching_backfill_relay_progress (
    author_pubkey   TEXT NOT NULL,
    relay_url       TEXT NOT NULL,
    until_cursor    BIGINT NOT NULL DEFAULT 0,
    complete        BOOLEAN NOT NULL DEFAULT FALSE,
    events_fetched  INTEGER NOT NULL DEFAULT 0,
    updated_at      BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    PRIMARY KEY (author_pubkey, relay_url)
);

CREATE INDEX IF NOT EXISTS idx_caching_relay_progress_incomplete
    ON caching_backfill_relay_progress(author_pubkey)
    WHERE complete = FALSE;

• until_cursor: the until timestamp for the next query to this specific relay for this author. 0 means "not started" (treat as now).
• complete: set true when this relay returns < page_size events for the current cursor range (this relay is drained for this author).
• events_fetched: cumulative count for diagnostics.

The existing caching_followed_pubkeys.backfill_complete becomes a derived value: an author is complete when all their relay progress rows are complete (or when they have no relay progress rows at all — no relays to query).

2. Relay-by-relay querying

Instead of one query_sync call with N relays, make N query_sync calls with 1 relay each. Each call waits for that single relay's EOSE (or timeout). No merge/dedup needed — publish every event directly to the inbox; the database handles deduplication (ON CONFLICT DO NOTHING).

3. Immediate new-follow detection

When the admin publishes a new kind-3 contact list, the live subscriber's on_event callback receives it in real-time. We detect this and trigger an immediate follow-graph refresh, which:

• Resolves the new follow set
• Syncs new pubkeys to caching_followed_pubkeys
• Creates relay progress rows for new follows (cursor=0, complete=false)
• The backfill loop picks them up on the next tick

Algorithm

Per backfill tick:

1. Pick next author that has at least one incomplete relay progress row
   (round-robin from caching_followed_pubkeys WHERE backfill_complete=FALSE)
2. Get incomplete relays for this author:
   SELECT relay_url, until_cursor FROM caching_backfill_relay_progress
   WHERE author_pubkey = $1 AND complete = FALSE
3. For each incomplete relay (one at a time):
   a. cursor = until_cursor (0 = now)
   b. query_sync(pool, &relay_url, 1, filter, &ev_count, 30000)
      filter = {authors: [pk], kinds: [configured], since: 0,
                until: cursor, limit: 500}
   c. Publish every event directly to inbox (cr_sink_publish)
   d. Find oldest_ts in this batch
   e. If ev_count == 0:
      - Mark this relay complete for this author
        (UPDATE caching_backfill_relay_progress SET complete=TRUE)
   f. If ev_count >= page_size (saturated):
      - Advance this relay's cursor: cursor = oldest_ts - 1
        (UPDATE caching_backfill_relay_progress SET until_cursor = oldest-1)
   g. If ev_count < page_size but > 0:
      - This relay is drained for this cursor range
      - Advance cursor to oldest_ts - 1 AND mark complete
        (the next query at this lower cursor would return 0 anyway,
         but we mark complete to avoid an extra round-trip)
4. After querying all incomplete relays for this author:
   Check if ALL relays for this author are now complete:
   SELECT COUNT(*) WHERE author_pubkey=$1 AND complete=FALSE
   If 0: mark author complete in caching_followed_pubkeys

Why per-relay cursors are better

• Relay A has 500+ events, relay B has 50: Relay A gets paginated across multiple ticks (cursor walks back 500 at a time). Relay B is drained in one query and marked complete. The author stays incomplete until relay A is also drained. With a single shared cursor, we'd advance the cursor based on the oldest across both relays, potentially skipping events on relay A.

• Resume after restart: each relay's cursor is persisted independently. On restart, we resume each relay exactly where it left off.

• Relay goes offline: if a relay is unreachable, its progress row stays incomplete. The author stays incomplete. When the relay comes back, we resume querying it. No events are lost.

Completion criteria

An author is marked backfill_complete = TRUE in caching_followed_pubkeys when ALL their relay progress rows in caching_backfill_relay_progress are marked complete = TRUE. This means every known relay has been queried down to the beginning of the author's history.

New follow detection

In caching/src/live_subscriber.c, the live_on_event callback currently just publishes events. We add a check:

static void live_on_event(cJSON *event, const char *relay_url, void *user_data) {
    cr_live_ctx_t *ctx = (cr_live_ctx_t *)user_data;
    ctx->live->events_received++;
    cr_sink_publish(ctx->sink, event);

    /* Detect admin kind-3 (contact list) changes → trigger follow refresh */
    cJSON *kind = cJSON_GetObjectItem(event, "kind");
    cJSON *pubkey = cJSON_GetObjectItem(event, "pubkey");
    if (kind && cJSON_IsNumber(kind) && kind->valuedouble == 3 &&
        pubkey && cJSON_IsString(pubkey)) {
        if (cr_follow_is_root(ctx->cfg, cJSON_GetStringValue(pubkey))) {
            ctx->live->follow_graph_changed = 1;  /* signal to main loop */
        }
    }
}

The main loop checks this flag each iteration:

if (live.follow_graph_changed) {
    live.follow_graph_changed = 0;
    /* Immediate follow-graph refresh */
    cr_pubkey_set_t new_followed;
    cr_pubkey_set_init(&new_followed);
    if (cr_follow_resolve(&cfg, upstream, &new_followed) == 0) {
        /* Sync to DB — new follows get relay progress rows automatically */
        pg_inbox_sync_followed_pubkeys(...);
        /* Create relay progress rows for new follows */
        pg_inbox_init_relay_progress_for_new_follows(&new_followed, &relay_map);
        /* Resubscribe live with new follow set */
        cr_live_resubscribe(&live, &cfg, upstream, &new_followed, &sink);
        cr_pubkey_set_free(&followed);
        followed = new_followed;
    }
}

Relay progress row creation

When a new follow is discovered, we need to create relay progress rows for it. The relays to query are determined by the relay discovery phase (NIP-65 outbox relays + bootstrap relays). New function:

int pg_inbox_init_relay_progress_for_author(const char *pk,
                                             const char **relay_urls,
                                             int relay_count);

For each relay URL, insert a row with until_cursor=0, complete=FALSE (if not already present). This is called:

• After follow-graph resolution for new follows
• After relay discovery for newly discovered outbox relays

Database schema changes

New table: caching_backfill_relay_progress

(see SQL above)

caching_followed_pubkeys — unchanged

The until_cursor and backfill_complete columns remain. until_cursor becomes less important (per-relay cursors are the source of truth), but we keep it for backward compatibility and as a quick "has this author started" flag. backfill_complete is updated when all relay progress rows are complete.

New PG inbox functions

caching/src/pg_inbox.c + .h

/* Initialize relay progress rows for an author. Creates one row per relay
 * with until_cursor=0, complete=FALSE. Skips rows that already exist. */
int pg_inbox_init_relay_progress_for_author(const char *pk,
                                             const char **relay_urls,
                                             int relay_count);

/* Pick the next author that has at least one incomplete relay progress row.
 * Round-robin via round_cursor. Returns 0 on success, -1 if none. */
int pg_inbox_pick_next_author_with_incomplete_relays(char *out_pk, int pk_len,
                                                       int *round_cursor);

/* Get incomplete relays for an author. Returns a JSON array of
 * {relay_url, until_cursor} objects. Caller frees. */
cJSON *pg_inbox_get_incomplete_relays(const char *pk);

/* Update relay progress: advance cursor and/or mark complete. */
int pg_inbox_update_relay_progress(const char *pk, const char *relay_url,
                                    long until_cursor, int complete,
                                    int events_this_page);

/* Check if all relays for an author are complete. Returns 1 if all complete,
 * 0 if some incomplete, -1 on error. */
int pg_inbox_check_author_relays_complete(const char *pk);

/* Mark author complete in caching_followed_pubkeys if all relay progress
 * rows are complete. Called after each relay update. */
int pg_inbox_check_and_mark_author_complete(const char *pk);

Changes

1. src/pg_schema.sql + src/pg_schema.h

Add the caching_backfill_relay_progress table and index.

2. caching/src/pg_inbox.c + .h

Add the new relay progress functions listed above.

3. caching/src/backfill.c — rewrite cr_backfill_tick

Replace the single query_author call with a relay-by-relay loop using per-relay cursors from the database.

4. caching/src/live_subscriber.c — detect admin kind-3 changes

Add follow_graph_changed flag to cr_live_t. Set it in live_on_event when a root npub publishes a kind-3.

5. caching/src/live_subscriber.h — add flag to struct

Add int follow_graph_changed; to cr_live_t.

6. caching/src/main.c — handle follow_graph_changed signal

Check live.follow_graph_changed each main loop iteration. If set, trigger immediate follow-graph refresh + relay progress initialization for new follows.

7. caching/src/main.c — create relay progress rows after relay discovery

After cr_relay_discovery_run(), call pg_inbox_init_relay_progress_for_author for each followed pubkey with its discovered outbox relays + bootstrap relays.

Implementation order

1. Schema: add caching_backfill_relay_progress table
2. PG inbox functions: relay progress CRUD
3. Backfill rewrite: relay-by-relay loop with per-relay cursors
4. Live subscriber: kind-3 detection + follow_graph_changed flag
5. Main loop: handle follow_graph_changed, create relay progress rows
6. Build and test

Testing

1. Rebuild: cd caching && make
2. Reset backfill: restart relay with --reset-backfill
3. Monitor relay progress:

   SELECT author_pubkey, relay_url, until_cursor, complete, events_fetched
   FROM caching_backfill_relay_progress
   ORDER BY author_pubkey, relay_url;
   

4. Monitor author completion:

   SELECT pubkey, backfill_complete
   FROM caching_followed_pubkeys ORDER BY pubkey;
   

5. Compare coverage: nak req -k 1 -a <pk> ws://localhost:7777 vs upstream
6. Test new-follow detection: follow a new pubkey from the admin account, verify it appears in caching_followed_pubkeys and gets backfilled within seconds (not 10 minutes)