1# Cascade Backpressure Implementation Plan
2
3## Context
4
5`pg_advisory_xact_lock` consumes 99.5% of DB runtime (p50=15s, p99=203s). FileProvider sends hundreds of concurrent file writes, each triggering a separate cascade with advisory locks. They pile up on the same space, creating a convoy that saturates the DB and makes all queries slow (page loads 42s, auth 12s, "Failed query" errors).
6
7Fix: cap concurrent cascades at 10 per space via KV counter. Return 429 when exceeded. FileProvider backs off via `.serverUnreachable`.
8
9## Step 1: Create KV namespace + bind to workers
10
11- run `npx wrangler kv namespace create SUBLIMATED_CASCADE_THROTTLE`
12- add binding to `src/api.sublimated.com/wrangler.jsonc` in `kv_namespaces`
13- add binding to `src/git.sublimated.com/wrangler.jsonc` in `kv_namespaces`
14- add `SUBLIMATED_CASCADE_THROTTLE?: KVNamespace` to Env in `src/api.sublimated.com/src/helpers.ts` (line 43, alongside other KV bindings)
15- add same to Env in `src/git.sublimated.com/src/index.ts`
16
17## Step 2: Create throttle module
18
19New file: `src/shared/services/write/cascade-throttle.ts`
20
21- `acquireCascadeSlot(kv: KVStore, spaceId: string): Promise<ThrottleResult>` — read counter from KV key `inflight:{spaceId}`, if >= 10 return `{ acquired: false, retryAfter: 1 }`, else increment and return `{ acquired: true }`
22- `releaseCascadeSlot(kv: KVStore, spaceId: string): Promise<void>` — decrement counter (fire-and-forget safe)
23- use `KVStore` interface from `src/shared/auth/types.ts` (existing pattern: `{ get, put, delete }`)
24- TTL 60s on every KV write (safety net if worker crashes mid-cascade)
25- MAX_INFLIGHT = 10
26
27## Step 3: Add throttle guard to API write endpoints
28
29Each handler: acquire slot at start, release in finally. If not acquired, return 429 with `Retry-After: 1` using `raw()` helper.
30
315 files to modify:
32
331. **`src/api.sublimated.com/src/batch.ts`** — `batchUpsert()` (line 16): guard before `batchWriteItems` + `createBatchCascade` at line 57
342. **`src/api.sublimated.com/src/content.ts`** — `updateItemContent()` (line 115): guard at top of function, before CAS upload. covers both branch-save path (line 181, direct cascade) and main path (line 220, deferred cascade via updateItem)
353. **`src/api.sublimated.com/src/content-api.ts`** — folder creation cascade (line 531) and file upload paths: guard before cascade calls
364. **`src/api.sublimated.com/src/notes.ts`** — `saveNote()`: guard before `createBatchCascade` at line 169
375. **`src/git.sublimated.com/src/index.ts`** — git push handler: guard before cascade, return HTTP 429 (plain text, not pack-line) if throttled
38
39Pattern for each handler:
40```
41const kv = ctx.env.SUBLIMATED_CASCADE_THROTTLE;
42if (kv) {
43 const slot = await acquireCascadeSlot(kv, space.id);
44 if (!slot.acquired) {
45 return raw(JSON.stringify({ok:false, error:{type:'rate_limited', message:'Too many concurrent writes'}}), 429, {'content-type':'application/json','retry-after':'1'});
46 }
47}
48try {
49 // ... existing handler logic with cascade ...
50} finally {
51 if (kv) releaseCascadeSlot(kv, space.id).catch(() => {});
52}
53```
54
55For deferred cascades (content.ts main path with `deferCascade: true`): release slot when handler returns, not when deferred cascade completes. Imprecise but safe — advisory lock still serializes at DB level.
56
57## Step 4: Client-side — map .rateLimited to .serverUnreachable
58
59File: `sdk/swift/Sublimated/Sources/SublimatedFileProvider/SublimatedFileProviderExtension.swift`
60
61In `mapError()` (line 745), add case:
62```swift
63case .rateLimited:
64 return NSFileProviderError(.serverUnreachable)
65```
66
67FileProvider already has built-in retry with backoff for `.serverUnreachable`. No manual `signalErrorResolved()` needed — system retries automatically. When the retry succeeds (counter below 10), normal sync resumes.
68
69Also add same mapping in `SublimatedEnumerator.swift` `mapError()` (line 296).
70
71HTTPClient already detects 429 and throws `.rateLimited(retryAfter:)` — no changes needed there.
72
73## Step 5: Finish formatPgError fixes
74
75Complete the remaining catch blocks in web worker page-data files (partially done):
76- `src/sublimated.com/src/lib/page-data/fetch-folder.ts` — 1 remaining (annotation stats)
77- `src/sublimated.com/src/lib/page-data/fetch-annotations.ts` — 1 catch block
78- `src/sublimated.com/src/lib/page-data/fetch-commits.ts` — 5 catch blocks (skip CAS fetch errors, fix DB-related ones)
79- `src/sublimated.com/src/lib/page-data/fetch-content.ts` — 2 catch blocks
80- `src/sublimated.com/src/lib/page-data/fetch-intents.ts` — 4 catch blocks (1 already uses formatPgError)
81
82## Step 6: Unit tests
83
84New file: `src/shared/test/cascade-throttle.test.ts`
85
86- acquire returns true when counter is 0
87- acquire returns false when counter >= 10
88- release decrements correctly
89- concurrent spaces use separate keys
90- use in-memory KVStore mock
91
92## Step 7: E2E test
93
94New file: `src/e2e/test-cascade-throttle.sh`
95
96- create anonymous user + space + file
97- fire 15 concurrent content updates via `curl &`
98- collect HTTP status codes
99- assert at least 1 returns 429 with `Retry-After: 1` header
100- assert some return 200 (those that got through)
101- wait 2s, retry a 429'd write, assert 200
102- cleanup (delete user)
103
104## Verification
105
106after deploying both API and git workers:
1071. PlanetScale insights: `pg_advisory_xact_lock` p50 should drop from 15s to <1s
1082. Cloudflare logs: timing for `resolve-caller` and `folder-cte` should be <500ms
1093. no more "Failed query" errors on unrelated pages
1104. e2e test passes: 15 concurrent writes, some get 429, retries succeed
1115. FileProvider sync still works (just slower when hitting 429 — retries automatically)