#2147 Feed grant projection stamps a site route that does not exist

Summary

swamp.score_grants.href is stamped by the grant projections, and the feed projection builds a site route path in SQL that the router does not serve. It has been wrong since the line was first written and stamps a new wrong value every time a feed post is approved.

infrastructure/clickhouse/init/03-grant-projections.sql:147:

concat('/feed/', JSONExtractString(e.properties, 'post_id')) AS href

The feed detail route is routes/feed/p/[id].tsx — /feed/p/<id>. There is no /feed/<id> route, so the stamped path 404s. Post ids are UUIDs (lib/domain/feed/post.ts), so the identifier is correct; only the prefix is wrong. Every other surface that links to a feed post — the /feed list, feed.xml, the profile Writings section, the submit redirect — uses /feed/p/<id>.

The combat log's rendering of these grants is already fixed: the read normalizes the path (lib/feed-post-path.ts, applied in the combatLog row mapper), so nothing is user-visible today. This issue is about the stamping, which is still producing legacy values.

Steps to reproduce

1. Approve a feed post (or emit a feed_post_approved event).
2. Read the resulting grant:

SELECT href FROM swamp.score_grants
WHERE grant_id = concat('content:feed-post:', '<post_id>');

1. The stored value is /feed/<post_id>. Requesting that path returns 404; /feed/p/<post_id> returns 200.

Why it matters beyond the wrong string

The interesting part is not the missing segment, it is where the decision lives. A URL path is a routing concern, and this one is recorded in a materialized view:

• The router cannot invalidate it. Renaming a route is a TypeScript change that sweeps routes/, islands/ and components/, and never reaches a .sql file. Nothing in the type system, the test suite, or the import boundary checker connects the two.
• A stored MV runs its stored definition forever. Editing the .sql in this repo does not reach the running cluster, so correcting a one-character path requires a DROP+CREATE migration against production ClickHouse with the content-hash bump. That is a heavy mechanism for a routing typo.
• The ledger is append-only, so a corrected projection heals nothing already stamped. Every grant written before the fix keeps the legacy value permanently, which is why the read-side normalizer is permanent rather than a shim, and why both forms will coexist in the table forever once this is fixed.

Affected

Three expressions in this repo, all identical:

• infrastructure/clickhouse/init/03-grant-projections.sql:147
• infrastructure/clickhouse/MIGRATION-score-campaign-window.sql:119
• infrastructure/clickhouse/score-grants-backfill.sql:60

Plus the stored production MV, which is what actually stamps and is not reached by the above: score_grants_from_feed_mv (mirrored in giga-swamp at extensions/files/clickhouse-production/schema-score-grants-mvs.sql).

The other seven href expressions in the grant projections (/lab/<n>, /u/<name>) are valid routes. Feed is the only broken one, but it is broken for the same structural reason any of them could be.

Suggested direction

Two options, in preference order.

1. Stamp the permalink from the app. feedPostApprovedProps (lib/app/feed/announce-feed-post.ts) already carries post_id, post_title and post_url; add the canonical on-site path, built by feedPostPath() from lib/feed-post-path.ts, and have the MV copy the property instead of constructing a path. The URL is then built where the router is, a route rename is a TypeScript change that actually reaches it, and ClickHouse stops encoding site routing. Worth a coalesce fallback in the MV so the app deploy and the MV recreate can land in either order without a gap that stamps an empty href.

2. Fix the literal — concat('/feed/p/', …) in all three files plus the production MV. Smallest diff, but leaves the routing decision in SQL, so the next feed route change breaks it again the same way and takes as long to notice.

Either way the production MV needs recreating for new grants to stamp correctly, and neither touches existing rows — by design, grants are never rewritten.