rtp-proxy-ADR-011 - SqlNetworkStateBinding (DB-as-Bus, Phase 2b Default)¶
Status: Accepted (2026-05-18) Date: 2026-05-18 (originally Proposed; accepted same day with Phase 2e-SQL slice landing) Refines: leafrtp-ADR-022, rtp-proxy-ADR-005 Depends on: rtp-proxy-ADR-001, rtp-proxy-ADR-002, rtp-proxy-ADR-010
Context¶
MULTI_SERVER_PLAN.md and ADR-005 frame Redis as the preferred Phase 2 transport, with Postgres / generic-SQL bindings listed as parallel options (ADR-007, ADR-009). The original Phase 2 ordering implied Phase 2b shipped against InMemoryNetworkStateBinding and Phase 2e turned on Redis.
Two observations reorder this:
- The project already has a mature
AbstractSQLDatabaseAccessorfamily (H2 / SQLite / MySQL / PostgreSQL) with HikariCP pooling. Most networks that run RTP across multiple backends already share a SQL database for region storage. Standing up an additional Redis instance just to enable network mode is a deployment tax those networks should not have to pay to ship Phase 2b. - The SPI surface in
rtp-proxy-common(NetworkTransport,ReservationClient, heartbeats, tokens) was deliberately shaped to admit any binding. A SQL-backed binding is a peer of Redis-backed, not a degraded substitute.
Treating Redis as a latency optimisation rather than a transport gate keeps Phase 2b shippable without the Lettuce + Lua dependency chain, while preserving ADR-005's role as the high-performance binding for networks that want it.
Decision¶
SqlNetworkStateBinding implements NetworkTransport, package io.github.dailystruggle.rtp.proxy.common.transport.sql. It is the Phase 2b default transport when network.enabled: true. ADR-005's Redis binding remains the Phase 2e opt-in for networks that need sub-poll-interval latency.
Sketch table layout¶
The exact DDL is finalised when the binding lands in code; the shape below is a target for the implementation turn and is non-binding:
| Table | Purpose | Primary key |
|---|---|---|
rtp_network_backends |
One row per backend, mirrors BackendHeartbeat fields. UPSERTed every heartbeat.intervalMs. |
(serverId) |
rtp_network_proxies |
One row per proxy, mirrors ProxyHeartbeat. UPSERTed every heartbeat.intervalMs. |
(proxyId) |
rtp_network_tokens |
One row per reservation token, fields mirror the Redis HASH columns minus hmac/schemaVersion (HMAC stays on the wire per ADR-005 amendment 2026-05-18; SQL binding relies on DB connection auth + TLS instead). |
(tokenId) |
rtp_network_wait_queue |
UUID FIFO of players awaiting a coordinate (REQ-RTP-NET-008). | (seq) AUTOINCREMENT, playerId UNIQUE |
rtp_network_control |
Single row: schemaVersion, killSwitch, lastChangedEpochMs. |
singleton |
SPI method mapping (1:1)¶
readSnapshot()->SELECT * FROM rtp_network_backends; SELECT * FROM rtp_network_proxies; SELECT killSwitch FROM rtp_network_control;claim(serverId, playerId, ttl)->INSERT INTO rtp_network_tokens (...) RETURNING tokenId;thenUPDATE rtp_network_tokens SET state='CLAIMED', claimedBy=? WHERE tokenId=? AND state='PENDING' RETURNING tokenId(atomic viaUPDATE ... WHERE; portable across MySQL / Postgres / SQLite without vendor-specific syntax).release(tokenId, reason)->UPDATE rtp_network_tokens SET state='RELEASED', releaseReason=? WHERE tokenId=?.publishBackendHeartbeat(...)/publishProxyHeartbeat(...)-> single-row UPSERT.subscribeBackendHeartbeats(consumer)-> backgroundScheduledExecutorServicepollingrtp_network_backendseveryheartbeat.intervalMs / 2, diffing against the last snapshot, invoking the consumer per changed row. Subscription cancel stops the poll task.close()-> stop the poll task, return any ownedPENDINGtokens toRELEASED, release HikariCP connections.
Orphan reanimation¶
Equivalent to ADR-005 section Orphan Reanimation. A periodic sweeper on the proxy runs every claimReanimateMs:
UPDATE rtp_network_tokens
SET state = 'PENDING', claimedBy = NULL, claimedAt = NULL
WHERE state = 'CLAIMED'
AND claimedAt + ? < NOW()
AND claimedBy NOT IN (SELECT proxyId FROM rtp_network_proxies WHERE lastSeenEpochMs > ?)
Sweeper lock uses an advisory SELECT ... FOR UPDATE SKIP LOCKED on rtp_network_control so only one proxy sweeps at a time (Postgres / MySQL 8+); SQLite degrades to a best-effort retry on busy errors.
HikariCP pool¶
The binding shares AbstractSQLDatabaseAccessor's pool rather than owning its own. Network-mode traffic is additive to existing plugin DB traffic; documentation guidance bumps maximumPoolSize by +2 when network mode is enabled. Rationale: one less moving part, one less connection pool to monitor, and the same accessor type already serves H2 / SQLite / MySQL / Postgres uniformly.
Engine support matrix¶
| Engine | Phase 2b support | Notes |
|---|---|---|
| PostgreSQL >= 13 | Recommended | SELECT ... FOR UPDATE SKIP LOCKED, LISTEN/NOTIFY (future optimisation), proven HikariCP integration. |
| MySQL >= 8.0 | Recommended | SELECT ... FOR UPDATE SKIP LOCKED since 8.0. |
| MariaDB >= 10.6 | Supported | Same locking primitives as MySQL 8. |
| H2 | Dev/test only | Embedded; not realistic for multi-backend networks. |
| SQLite | Dev/test only | Single-writer; works for 1-2-backend home setups but not production proxy-fronted networks. Documented as "dev/test only" in admin docs. |
Config implications¶
network.yml gains transport.type: sql as the default:
transport:
type: sql # was: in-memory; redis is now opt-in
url: "" # falls through to the existing rtp database accessor URL if blank
poolSize: 4 # advisory; binding shares AbstractSQLDatabaseAccessor's pool in v1
connectTimeoutMs: 2000
readTimeoutMs: 5000
When transport.url is blank the binding piggy-backs on the existing database.url from config.yml, so a network that already shares a SQL database for region storage gets network-mode functionality with zero new config.
Replay resistance without HMAC¶
ADR-010's HMAC envelope is a wire-layer concern for Redis. The SQL binding leans on DB connection authentication + TLS for replay resistance; the replay-nonce / schemaVersion discussion in ADR-010 is N/A for this binding. Admin docs (docs/admin/proxies/TRANSPORTS.md) shall document this distinction explicitly so operators do not expect HMAC-grade wire protection from the SQL transport.
Open questions¶
- Heartbeat polling at scale. A 1s poll on
rtp_network_backendsfrom N backends isNSELECTs/sec network-wide. Acceptable forN <= 32; networks larger than that should consider PostgresLISTEN/NOTIFY(a follow-up ADR if it bites) or migrate to Redis. - HikariCP pool exhaustion under burst. If network mode dramatically increases concurrent queries, the +2 documentation bump may be insufficient. Mitigated by
connectTimeoutMs(binding fails individual calls fast rather than blocking the proxy event loop).
Alternatives Considered¶
| Alternative | Why Rejected |
|---|---|
| Keep Redis as Phase 2b default | Forces every Phase 2b operator to stand up Redis even if they already run MySQL/Postgres; large deployment tax for a "network mode default-on" milestone. |
Ship InMemoryNetworkStateBinding as Phase 2b default |
Operationally useless: only valid for single-JVM tests. Cannot satisfy any cross-server scenario. |
Build a separate PostgresNetworkStateBinding (per ADR-007) and a separate MySqlNetworkStateBinding |
Doubles binding surface for negligible benefit; portable SQL covers both engines via HikariCP. ADR-007 / ADR-009 are folded into this binding and can be re-split later if engine-specific optimisations justify it. |
Consequences¶
- Positive: Phase 2b ships against the most common existing operator footprint (shared SQL); zero new infra required; Redis becomes a documented latency upsell rather than a gate.
- Negative: Heartbeat fan-out is polling rather than pub/sub (30-200ms typical LAN latency vs sub-ms Redis). Documented as the trade-off for the simpler deployment.
- Neutral: ADR-005 unchanged;
RedisNetworkStateBindingremains the recommended high-performance transport.
References¶
- ADR-036 (umbrella);
MULTI_SERVER_PLAN.mdPhase 2. - ADR-005 (
RedisNetworkStateBinding); ADR-007 (Postgres binding, superseded by this); ADR-009 (generic SQL binding, superseded by this). - ADR-010 (security hardening; HMAC is N/A for SQL binding per section Replay resistance).
- REQ-RTP-NET-007, -008, -009, -011, -012, -014.
docs/dev/scratch/PROPOSAL-sql-binding-first.mdrev 2.