feat: proxy: add DB cache

[joanestebanr]

Goal

Add a DB-backed cache for the bridge tracker's activity and tracking state (today both live only in memory — ActivityCache in bridgetracker/activity.go and memoryRegistry in bridgetracker/registry.go), so state survives restarts and is queryable directly.

This is meant to:

• Reduce calls to external services: both caches already avoid re-asking for something already known within a run (a bridge already scanned, a claim already confirmed, a tracking snapshot already resolved), but every restart starts from zero and re-issues the full set of bridge-service/agglayer calls again. A DB-backed cache keeps that already-resolved state across restarts.
• Increase response speed: serving GET /activity/... and GET /tracker/... from a local DB read instead of re-scanning bridge services / re-resolving tracking steps on every cold start.
• Keep timestamps: lastAccess (idle eviction, both caches) and terminalSince (terminal-entry retention, memoryRegistry) are currently reset to zero on every restart, since they only exist in memory — losing them means every entry looks freshly seen/non-terminal again right after a restart, which throws off idle/retention pruning. Persisting them lets pruning behave correctly across restarts instead of just within a single process lifetime.

[joanestebanr]

Case 1: tracker state (tracked_bridge)

One row per TrackingID (domain.TrackingID{NetworkID, TxHash}, tracking_data.go:12).

CREATE TABLE tracked_bridge (
    network_id        INTEGER NOT NULL,
    tx_hash            VARCHAR NOT NULL,

    schema_version     INTEGER NOT NULL,
    claim_status        VARCHAR NOT NULL,      -- pending | readyToClaim | claimed | error

    origin_block_number BIGINT,                -- BridgeInfo.BlockNumber; NULL until Info resolves
    origin_block_hash   VARCHAR,                -- see "reorg awareness" below

    updated_at          BIGINT NOT NULL,        -- last time this row was written
    last_access         BIGINT NOT NULL,        -- last time this row was read (idle eviction anchor)
    terminal_since       BIGINT,                -- NULL until TrackingStatus becomes terminal (retention anchor)

    data                BLOB NOT NULL,          -- JSON: TrackingBridgeTx (Error/Info/StartDate/Timeout) + allSteps

    PRIMARY KEY (network_id, tx_hash)
);
CREATE INDEX idx_tracked_bridge_claim_status ON tracked_bridge (claim_status);
CREATE INDEX idx_tracked_bridge_last_access  ON tracked_bridge (last_access);

Mapping to what was asked:

• updated_at: as above — stamped on every write, mirrors bridgeEntry.lastAccess/terminalSince's style (registry.go:12-24) but for "last changed" rather than "last read" (last_access covers that separately, since it's what today's PruneIdle evicts by).

• Reorg awareness — suggestion: BridgeInfo (bridge_info.go:11) already stores BlockNumber (the origin block where the creating tx's BridgeEvent was found) but not its hash — today that's fine because Info is only ever set once the tx's finality already reached the configured level (config.go:73-78) and is then never re-checked (tracking_bridge_tx.go:36-40). But DefaultL1BlockFinality/DefaultL2BlockFinality are LatestBlock (config.go:28-32) — not reorg-safe by itself — and a DB cache adds a new failure mode: a much longer time can pass between "Info resolved" and "row reloaded/reused" than within one continuous run (restart, cold cache, etc.), during which the origin chain may have reorged.

  • Add origin_block_hash: the hash of origin_block_number at the moment Info was captured. It's already available for free — BridgeEventSource.FindBridge reads it off the receipt log it's already inspecting (l.BlockHash, sources/bridge_event.go:122) — it's just not carried into BridgeInfo today.
  • On load (or on a periodic sweep), compare origin_block_hash against the chain's current hash at origin_block_number; a mismatch means the row was captured on a block since reorged out — discard data and re-resolve from scratch, exactly as if the bridge had never been seen. This is the same idea reorgdetector already uses for its own tracked_block(num, hash) (reorgdetector0001.sql), just scoped to this one cached fact instead of a full block range.
  • This needs a small domain-level addition too, not just a DB column: BridgeInfo gains a BlockHash common.Hash field, populated where BlockNumber already is.

• schema_version: stamped with the version of the domain model that produced data. On read, if it doesn't match what the running binary knows how to decode, the row is treated as a miss (discard, re-resolve) rather than attempting to interpret stale/incompatible JSON — this is a cache, not a source of truth, so "discard and recompute" is always a safe fallback. Bumped whenever TrackingBridgeTx/BridgeStepPath's shape changes in a way that would break decoding an older row.

• claim_status: denormalized copy of TrackingData.ClaimStatus() (tracking_data.go) so pending/readyToClaim/claimed/error can be filtered/indexed directly (e.g. by the activity endpoint) without deserializing data first.

• data: the full blob — TrackingBridgeTx (Error, Info, StartDate, Timeout) plus allSteps []BridgeStepPath. Everything else on TrackingData (TrackingStatus(), StepIndex(), etc.) stays derived in memory on read, exactly as it is today — nothing else needs to be stored (see tracking_data.go:31-34).

Not persisted: subscribers (active WebSocket channels) and the trigger signal (registry.go:15-19,45-48) — both are in-process-only by nature and stay purely in memory even after this change.

[joanestebanr]

Case 2: activity state (activity_address)

One row per from_address — matches the granularity ActivityCache already uses (activity.go:42-55), and the only granularity GetActivity ever queries by (there's no cross-address activity query today, so nothing is gained by normalizing entries into their own table — see the trade-off note at the end).

CREATE TABLE activity_address (
    from_address   VARCHAR NOT NULL,

    schema_version INTEGER NOT NULL,

    updated_at     BIGINT NOT NULL,   -- last write
    last_access    BIGINT NOT NULL,   -- last read (idle eviction anchor, mirrors ActivityCache.addrCache sweep, activity.go:186-208)

    scan_state     BLOB NOT NULL,     -- JSON: map[network_id] -> {last_page, last_error, last_error_at, last_success_at}
    entries        BLOB NOT NULL,     -- JSON: map[global_index] -> ActivityEntry (minus Tracking)

    PRIMARY KEY (from_address)
);

Mapping to what was asked:

• Common fields: same as case 1 — schema_version, updated_at, last_access.

• Per-network page cursor: today fetchNewBridgesFrom (sources/activity.go:108-133) always starts at page 1, relying on the already-cached known set to stop early once it reaches a bridge it has already seen — cheap in the common case, since only genuinely-new pages get fetched. The gap is a fetch that fails partway through a long run of new pages: right now that's just lost, and the next call restarts at page 1. scan_state[network_id].last_page closes that: on a failure, resume from there next time instead of re-walking every already-consumed new page.

• Error state with timestamp, without losing prior data: scan_state[network_id] also carries last_error + last_error_at — this is exactly domain.ActivityWarning (activity.go:75) plus a timestamp, made durable across calls instead of only existing for the one GetActivity response that produced it. It's deliberately a sibling of entries, never something that clears or blocks it: a network erroring now must never touch bridges already cached from a previous successful scan of that same network (mirrors upsert's per-item failure handling, activity.go:234-310, which already leaves entries untouched on a per-bridge failure — this just extends the same principle to a per-network scan failure).

• scan_state / entries as two blobs: agreed — both are naturally address-scoped maps (network_id -> .../global_index -> ...) with no field either one needs indexed on its own (nothing queries "which addresses have network 5 in error" or "which addresses hold bridge X" directly), so splitting them into normalized tables would add join/write complexity for no query benefit. Kept as two separate columns rather than one combined blob so a scan-state update (frequent, small) never requires rewriting the (potentially large) entries blob, and vice versa.

• Tracker info comes from the other table: ActivityEntry.Tracking (domain/activity.go:31-69) is not stored in entries — it's a live view of tracked_bridge (case 1), looked up by TrackingID{NetworkID: item.NetworkID, TxHash: ...} exactly as refresh already does through a.supervised.Get(id, true) (activity.go:283-291). Persisting it here too would mean two copies of the same tracking snapshot going stale independently.

Trade-off flagged for confirmation: this design means no DB-level "give me every pending bridge across all addresses" query is possible without scanning and deserializing every row — same limitation matchesFilter already has in memory today (activity.go:155-170, applied after fetching one address's cache). If that kind of cross-address query turns out to be needed later, entries would need to become its own table (one row per bridge, claim_status indexed — same shape as case 1's tracked_bridge) rather than a blob. Flagging it now since it's cheaper to decide before writing the migration than after.

[joanestebanr]

Case 2, revised: normalize entries into its own table

Right — per-address entries as a single blob was fine for reconstructing today's exact in-memory shape, but it blocks the two things that actually matter for a cache: filtering by claim status without deserializing every address's blob, and filtering by the bridge's own age. Splitting it into a proper table, one row per bridge:

CREATE TABLE activity_address (
    from_address    VARCHAR NOT NULL,

    schema_version  INTEGER NOT NULL,

    updated_at      BIGINT NOT NULL,   -- last write
    last_access     BIGINT NOT NULL,   -- last read (idle eviction anchor)

    scan_state      BLOB NOT NULL,     -- JSON: map[network_id] -> {last_page, last_error, last_error_at, last_success_at}

    PRIMARY KEY (from_address)
);

CREATE TABLE activity_bridge (
    global_index         VARCHAR NOT NULL,
    from_address         VARCHAR NOT NULL REFERENCES activity_address(from_address) ON DELETE CASCADE,

    origin_network_id    INTEGER NOT NULL,   -- ScannedBridge.NetworkID: the bridge-service network that reported it
    destination_network  INTEGER NOT NULL,   -- Bridge.DestinationNetwork
    tx_hash              VARCHAR NOT NULL,    -- + origin_network_id: the TrackingID to join tracked_bridge by

    claim_status          VARCHAR NOT NULL,   -- unclaimed | claimed | error   (on-chain isClaimed())
    tracker_claim_status   VARCHAR NOT NULL,   -- pending | readyToClaim | claimed | error

    block_timestamp       BIGINT NOT NULL,     -- Bridge.BlockTimestamp: when the bridge itself was created on-chain
    created_at            BIGINT NOT NULL,     -- when this entry was first cached
    updated_at            BIGINT NOT NULL,     -- when claim/tracking state was last (re)computed

    data                 BLOB NOT NULL,       -- JSON: Bridge, Claim (nullable), Errors — minus Tracking (still sourced live from tracked_bridge)

    PRIMARY KEY (global_index)
);
CREATE INDEX idx_activity_bridge_from_address           ON activity_bridge (from_address, tracker_claim_status);
CREATE INDEX idx_activity_bridge_from_address_timestamp ON activity_bridge (from_address, block_timestamp);

• One row per bridge, keyed by global_index alone (not (from_address, global_index)): a global_index is unique system-wide by construction (it encodes origin network + deposit count), and a given bridge only ever has one sender, so there's no scenario where the same global_index needs two rows for two different from_address. from_address is still a plain column + FK, not part of the key.
• ON DELETE CASCADE: activity_address already carries idle eviction (last_access); deleting an idled address's row now cascades to drop every one of its cached bridges in one statement, instead of a separate sweep over activity_bridge. This relies on _foreign_keys=on, already set in db.NewSQLiteDB's DSN.
• Fast claim-status queries: tracker_claim_status (and claim_status) are now real columns — WHERE from_address = ? AND tracker_claim_status = 'readyToClaim' needs no deserialization at all, unlike the blob design.
• Filtering by age ("bridges from 1 day/1 week ago"): added block_timestamp — the bridge's own creation time on the origin chain (BridgeResponse.BlockTimestamp), not created_at (when our cache happened to first see it, which can lag behind for backfilled history). That's the field a "bridges older than 1 week" query should filter on; created_at/updated_at stay for cache bookkeeping, not bridge age.
• data: now just the two opaque, already-resolved payloads (Bridge, Claim) plus Errors — no longer needs to also carry claim/tracker status now that those are columns.
• Everything from the previous comment still holds: scan_state stays a per-address blob (still no query needs network_id indexed on its own), and Tracking is still never persisted here — always a live join against tracked_bridge by (origin_network_id, tx_hash).

This makes activity_bridge structurally the same shape as case 1's tracked_bridge (typed key/status/time columns + one opaque blob for the rest) — worth keeping that symmetry in mind once we get to the shared migration/store code.