TECH — ID-166 RSS feed auth

TECH — ID-166 RSS feed auth

{166.3} of the id-166 spec chain. Behaviour is PRODUCT.md; grounding is RESEARCH.md. Invariant numbers below (PI-N) refer to PRODUCT.md §Behavior.

Context

Code-intelligence orientation, and a measured warning about it. gitnexus_query (“RSS feed generation workspace articles XML”) returns both handlers as standalone definitions — app/api/intelligence/workspaces/[id]/rss/route.ts:GET (26–109) and .../rss/filtered/route.ts:GET (29–109) — each in zero execution flows. gitnexus_context on RssFeedPanel (components/intelligence/rss-feed-panel.tsx:111-152) returns 1 incoming call (WorkspaceOverviewPage), 1 outgoing (FeedRow), and processes: [].

The graph shows no edge between the panel and either route, and that is a false zero — the panel constructs both URLs at :121-122 via template literals, which GitNexus cannot see; route_map/api_impact independently report consumers: [] for both. Do not scope this change from GitNexus. All consumer facts below come from grep plus file reads (RESEARCH.md §What exists today).

Current state of the surfaces this touches:

• app/api/intelligence/workspaces/[id]/rss/route.ts and .../rss/filtered/route.ts — near-identical 111-line twins. Bare export async function GET, no defineRoute, no middleware, no auth call. createServiceClient() at :30/:33. Workspace lookup gated on application_types.key='intelligence' at :40-45/:43-48. Article isolation is only .eq('workspace_id', …) at :57/:60. Response headers at :99-105 carry Cache-Control: public, max-age=900, s-maxage=900.
• components/intelligence/rss-feed-panel.tsx — baseUrl at :116-119, passedUrl :121, filteredUrl :122; props are {workspaceId, workspaceName} (:9-12), no data fetching. Copy to change at :54, :101-105, :145-150.
• lib/supabase/server.ts:60-72 — createServiceClient() uses the service-role key and bypasses RLS.
• lib/auth/client.ts:86-113 — getAuthorisedClient(), cookie-only; failures via authFailureResponse(auth) (:124-147). Exemplar: app/api/oauth/revoke/route.ts:22-23.
• lib/validation.ts:95 parseSearchParams; :117-120 splits comma-separated values into arrays — the OQ-5 hazard.
• Migration exemplar: supabase/migrations/20260716113306_id147_form_attachments.sql (:73-81). Note its SELECT … USING (true) + GRANT ALL TO authenticated shape is exactly what this table must not copy (RESEARCH.md I2).
• Query keys: lib/query/query-keys.ts:193-225 — the intelligence.* namespace, with sources/articles/prompts already keyed by workspaceId.

Proposed changes

1. Schema — one table, no Data-API surface

New migration creating intelligence_feed_tokens. The name deliberately says what it scopes (the intelligence feed) rather than the workspace, so the eventual legacy-tier rename is cheap — PRODUCT.md OQ-3 is open and the owner should confirm before this lands.

Columns: id uuid pk, workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, token_hash text NOT NULL UNIQUE, token_prefix text NOT NULL, token_start text NOT NULL, label text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), created_by uuid, revoked_at timestamptz, last_used_at timestamptz.

• token_hash is a plain unsalted SHA-256 hex digest, UNIQUE with the implicit B-tree index — not a hash index, which cannot enforce uniqueness. Justified by entropy alone (NIST SP 800-63B §5.1.2.2: look-up secrets ≥112 bits SHALL be hashed with an approved one-way function; only sub-112-bit secrets require a KDF). Do not restate “it enables fast lookup” as a co-reason — that is a consequence, and stating it as a justification invites the same reasoning on a low-entropy secret later.
• token_prefix + token_start exist purely to render cfeed_…a9F2 in the revoke list (PI-18). They are labels, never an auth path, never indexed for lookup.
• FK covering index on workspace_id (advisor convention, per the exemplar :68-69).
• Partial index WHERE revoked_at IS NULL for the active-token list.
• ALTER TABLE … OWNER TO postgres, COMMENT ON TABLE, RLS explicitly enabled (the ensure_rls event trigger at 20260726120000_ensure_rls_event_trigger.sql:44-49 is the backstop, not the mechanism).

Grants: none. No GRANT to anon or authenticated. Access fails at 42501 before RLS is consulted — the same posture already measured for anon on feed_articles. This is the concrete form of RESEARCH.md I2, and it resolves the conflict between the repo’s GRANT ALL TO authenticated house style and the security-privileges least-privilege rule in favour of the latter, for this table only.

Not added to SURFACE_TABLES (scripts/generate-api-views.ts:121) — a credential table never reaches PostgREST. This settles RESEARCH.md OQ-2 as option 1.

2. The access pattern this forces — authorise with the cookie client, act with the service client

Because the table has no authenticated grant, the operator routes cannot read it with the cookie client. The pattern is therefore:

1. getAuthorisedClient(['admin','editor']) → authorise the human (PI-26; role gate open, see PRODUCT.md). Failures via authFailureResponse(auth).
2. Then use createServiceClient() to touch intelligence_feed_tokens.

This avoids OQ-2’s .schema('public') override entirely — RESEARCH.md records that the S4 audit found zero existing uses of it, and this design needs none, since the service client reaches public directly rather than through the api view layer. Confirm that against lib/supabase/schema.ts DB_OPTION during implementation; if the service client also routes to api, option 1 needs the override after all and the choice should be re-put to the owner rather than worked around silently.

Consequence worth stating plainly: RLS on this table guards nothing on either path (service client bypasses it; no other role holds a grant). Policies are written for defence-in-depth against a future grant, not as the control. The control is the route.

3. Token mint, carrier and verification

• lib/intelligence/feed-token.ts (new, ~30 lines): mintFeedToken() → cfeed_ + 32 random bytes base64url (256 bits) via node:crypto randomBytes; hashFeedToken(raw) → createHash('sha256').update(raw).digest('hex'), following the existing idiom at lib/upload/folder-drop.ts:384.
• Hash in the application, never pgcrypto. digest() would put the plaintext token into the SQL statement, where it reaches log_statement, pg_stat_statements and Supabase’s Postgres logs — the exact leak being avoided.
• No crypto.timingSafeEqual, and this is deliberate. Verification hashes the candidate and probes the unique index. The attacker supplies a value they already control; a Postgres B-tree comparison is not cryptographically constant-time and never will be, so a compare-in-app design would need timingSafeEqual — the digest-lookup design removes the requirement rather than satisfying it. Do not add it as belt-and-braces; it would signal a threat model this design does not have.
• No CRC32 checksum. Its documented purpose is offline secret scanning, value that accrues only to tokens leaking into scanned public repos. This token’s exposure surface is OPML files, reader databases, widget vendor configs and CDN logs — none scanned. The cfeed_ prefix alone buys the operationally useful part.

Carrier: query parameter ?token=, with the path form held as the documented fallback. base64url’s alphabet (A–Z a–z 0–9 - _) contains no comma, which closes the parseSearchParams hazard at lib/validation.ts:117-120 by alphabet choice rather than by restructuring the routes. This choice is contingent on PRODUCT.md OQ-1, which is still UNDECIDABLE: if the subscribe-and-observe test shows any target reader normalising, reordering or stripping query parameters, switch to a path segment (.../rss/t/<token>). DR-134 requires that test before the carrier is fixed; do not implement past this point without it.

Verification in both route handlers, inserted after await context.params and before createServiceClient() — a verifyFeedToken(workspaceId, rawToken) helper returning a discriminated result. RESEARCH.md I3 is the argument for pairing it with route-level tests that prove the 401: verifyCronAuth exists and three of five cron routes hand-roll the same comparison inline anyway, so convention alone does not hold here.

Ordering matters for PI-6: token validity is checked before workspace existence, so an invalid token cannot use the 404/401 split to probe which workspace UUIDs exist.

Do not wrap either route in defineRoute — shouldValidateResponseBody (lib/api/define-route.ts:146-151) validates only 2xx application/json, so on application/rss+xml the wrapper is a pure no-op (RESEARCH.md I8).

4. CDN cache-tag purge — the requirement that makes revocation real (PI-11, PI-12)

Both feed responses gain Vercel-Cache-Tag: feed-token-<id> alongside the existing Cache-Control. On revoke, the route purges that tag.

• Use dangerouslyDeleteByTag, NOT invalidateByTag. Vercel’s own description: “When you invalidate a cache tag… the next request serves the stale content instantly while revalidation happens in the background” — i.e. invalidate serves the still-authorised body one more time, which is precisely the failure PI-12 forbids.
• Limits to respect: 256 chars per tag, 128 tags per response. One tag per response here.
• This adds a dependency on @vercel/functions. Verify it is present before planning around it; if absent, the REST /invalidate-by-tag endpoint or vercel cache invalidate are the fallbacks, and the delete-vs-invalidate distinction still applies.

Open, and it decides whether this is belt-and-braces or the only control (RESEARCH.md OQ-8): does Vercel Routing Middleware execute on a CDN cache HIT? Vercel’s log documentation lists “Routing Middleware Invocation” and “Vercel CDN Cache” as separate resources, which hints it does not. If middleware does not run on a hit, no middleware-based gate can protect a cached response at all and the purge becomes mandatory. Resolve before choosing a gate location.

5. last_used_at (PI-19)

Hour-coarsened, written in the same statement so there is no read-then-write race and the engine skips the write:

UPDATE intelligence_feed_tokens
   SET last_used_at = now()
 WHERE id = $1
   AND (last_used_at IS NULL OR last_used_at < now() - interval '1 hour');

Coarsen because it is free, not because volume demands it — 96 requests/token/day is nothing, and RESEARCH.md corrects that premise. The real defect is correctness: with s-maxage=900 most polls are cache hits that never invoke the handler, so this column records only misses. PI-19 requires the UI to label it approximate.

6. Routes and operator surface

• POST /api/intelligence/workspaces/[id]/feed-tokens — issue. Requires label. Returns the raw token exactly once; every later read returns only prefix/start/label/dates (PI-17, PI-18).
• GET /api/intelligence/workspaces/[id]/feed-tokens — list, never returning token_hash or a working URL.
• POST /api/intelligence/workspaces/[id]/feed-tokens/[tokenId]/revoke — sets revoked_at, then purges the cache tag. Revocation is not complete until the purge returns; a failed purge must surface as a failed revoke, not a silent success (PI-12).
• components/intelligence/rss-feed-panel.tsx — gains the token list, issue and revoke. Data via TanStack Query under a new intelligence.feedTokens(workspaceId) key beside the existing sources/articles keys at lib/query/query-keys.ts:193-225. Do not copy connected-apps-section.tsx’s raw fetch() — copy its list/revoke/toast shape only (RESEARCH.md I7).
• Copy at :54, :101-105, :145-150 is rewritten per PI-28/PI-29: the URL is still confidential, but because it is the credential, not because there is none.
• Warm Meridian semantic tokens; never colour alone for state (PI-31, PI-32).

Risks and mitigations

Risk	Mitigation
Revoked token keeps serving from CDN for ≤15 min. Silent — the operator sees it gone while the URL works. The primary failure mode of this feature.	Cache-tag purge with dangerouslyDeleteByTag; failed purge fails the revoke. Verified by a deployed-instance test (below), because it cannot fail locally.
Token mangled in transit — parseSearchParams comma-splitting, or reader query normalisation.	base64url alphabet closes the first. The second is UNDECIDABLE and gates implementation: settle OQ-1 by test before fixing the carrier.
s-maxage=900 is a bare literal in two files with three behaviours hanging off it — cacheability, revocation latency, last_used_at fidelity. Someone tuning feed freshness silently changes the revocation window.	Extract to a named constant with a comment naming all three dependants, and pin it with a test.
Operator revokes a token believing it unused, on an under-reporting field.	PI-19’s explicit “approximate — feeds are edge-cached” label.
Existing e2e tests break on merge.	e2e/tests/intelligence-workflow.spec.ts:~160 and :192 assert status === 200 unauthenticated and must be updated in the same change; 13 route tests and 9 panel tests likewise (the panel tests assert the copy being replaced, so they are rewritten, not extended).
feed_articles and its whole read path are USING (true) — RLS backstops nothing if a handler bug drops the .eq('workspace_id', …).	Out of scope (RESEARCH.md I2), but the token table must not extend the pattern. Route-level tests are the guard.

Testing and validation

Each PI-N maps to a concrete check.

Route unit tests (extending the 13 existing tests across both route files):

• PI-1 valid token → 200 + <rss>/<channel>; PI-2 no token → 401, and assert the body is not a well-formed empty feed; PI-3 unknown / malformed / revoked → 401, bodies indistinguishable; PI-4 valid token for workspace A against workspace B → 401.
• PI-6 ordering: an invalid token against a non-existent workspace returns 401, not 404 — the assertion that proves existence cannot be probed.
• PI-7 valid token, zero articles → 200 with zero <item>; PI-8 DB error → 500, and explicitly not an empty 200 (a regression guard on the existing :62-70 posture).
• PI-10 if <atom:link rel="self"> is emitted, it carries the token or is absent.
• PI-13 revoking token A leaves token B working.

Negative controls, not optional. For PI-2 and PI-4, first assert the test fails against the pre-change handler — S547’s clearest lesson was a pair of assertions that could never go red because they bound different subjects. An auth test that passes before auth exists is worthless.

Token unit tests: mint produces the cfeed_ prefix and ≥256 bits; two mints never collide; hashFeedToken is stable and the raw token never appears in any persisted column; a token round-trips through parseSearchParams unchanged (the OQ-5 guard — assert against a deliberately comma-containing string too, so the guard fails if the alphabet is ever widened).

Operator route tests: PI-16 issue without a label → rejected. PI-17 the raw token appears in the issue response and in no subsequent list response. PI-21 issuing does not revoke existing tokens. PI-24 revoking an already-revoked token fails cleanly. PI-25/PI-26 role gates, including the negative case for each role.

Component tests (rewriting the 9 in rss-feed-panel.test.tsx): PI-18 the list shows label + dates + truncated fragment and never a full URL; PI-19 the approximate label is present; PI-22 empty state; PI-23 controls disable while in flight; PI-27 controls absent — not disabled — without the role; PI-32 state distinguishable without colour; PI-33 focus moves to the display-once surface after issue.

Deployed-instance verification — the only place two of these can be proven:

1. PI-12 revocation. Subscribe, confirm 200, revoke, replay the URL, assert 401 within the window. This cannot fail locally because there is no CDN; running it only locally would produce a green suite that proves nothing.
2. The Authorization-excluded-from-cache rule that RESEARCH.md relies on to correct DR-134 is documentation-derived. One curl against a preview deployment, checking x-vercel-cache, confirms it. Cheap, and it closes the last inference in the chain.

Manual, and required before the carrier is fixed (DR-134): subscribe with a real tokenised URL in at least Feedly, Inoreader and Miniflux; confirm the feed loads and the stored URL is byte-identical. This settles PRODUCT.md OQ-1. Also check OQ-1b — whether the Feedly-added feed becomes visible through Feedly’s search or discovery surfaces. If it does, a tokenised URL in Feedly is a public capability URL and this design needs revisiting before it ships.

Follow-ups

• RESEARCH.md I1 — 20 policies across the five facet tables target PUBLIC (no TO clause), the live id-347 anti-pattern with no CI gate. Bounded by zero anon grants; out of scope here; owner-flagged for its own task.
• OQ-4 — narrowing proxy.ts’s isApiRoute exemption. Deferred by recommendation; it touches all 147 route files, each of which already self-gates.
• proxy.ts:101-103 calls supabase.auth.getUser() — a network round-trip — on every matched request including these feeds, and the result is unused for /api/*. Feed polling pays it today.
• DR-INTENT-1 (RESEARCH.md) — amend DR-134’s stated rationale: its CDN bypass argument is refuted, the ruling stands on feed-reader capability instead, and its Consequences should gain the cache-purge requirement plus two corrected figures.