Plan: lumibase-pageview-counter + Extension Signing & Auto-install

[khuepm]

Context

The user wants a visitor/pageview counting capability for LumiBase, and used it as the trigger to build two adjacent platform features they've been wanting:

1. A pageview counter — configurable per-site: pick the user table for attribution and pick the counting approach. The user explicitly asked for best-practice counting and accepted extending LumiBase core where needed.
2. Extension signing — a toolchain to build official LumiBase extensions with a cryptographic signature, verified at every load path (not just marketplace install).
3. Auto-install + enabled-by-default for official lumibase-* extensions, each toggleable off.

Counting at scale is a write-amplification problem; best practice is atomic counters (Redis INCRBY / Cloudflare Durable Object) with periodic batch-flush to a durable rollup, HyperLogLog for approximate uniques, and DB rollup as the always-available default (sources, Cloudflare DO counters). LumiBase's runtime today has no atomic increment (CacheProvider is get/set/delete only; KV can't INCR; the Redis client doesn't surface INCR; the only Durable Object is realtime SiteRoom), so the runtime must be extended. Signing infra is half-built: Ed25519 verify exists but only in the marketplace install handler, is skippable when signature columns are null, and the isOfficial/auto-install concepts don't exist at all.

Decisions locked with the user: support all 4 counting strategies (DB rollup = default, Redis/DO hot counter, CDC event, HLL uniques), selectable per site. Attribution = configurable user table + anonymous fallback. Signing = full scope: sign toolchain, verify everywhere fail-closed for official, publisher-key DB registry, auto-install lumibase-*. CDC strategy: panel reads daily rollup, ClickHouse stays external (no ClickHouse read client). Flush cadence: every 5 min for hot-counter/DO. Consent: add an analytics category and gate user-attributed hits on it.

This is large. It splits into two independently-shippable tracks (A = counter, B = signing). Recommend landing Track A first, then Track B, in separate commits per [[commit-conventions]].

---

Guardrails (from project rules + memory)

• IDs nanoid(); every domain table has site_id; every query filters .where(eq(t.siteId, siteId)).
• Migrations are hand-written SQL + _journal.json edit ([[migrations-are-hand-written]]), not drizzle-kit generate.
• Parallel branches collide on migration numbers ([[parallel-feature-branches-migration-numbering]]) — renumber 0007+ on merge; the two migrations below (publisher keys, pageviews) each need their own tag.
• DO on free plan needs new_sqlite_classes ([[do-sqlite-classes-free-plan]]).
• Response format { data, meta? } / { errors }. Strict TS, import type, no any.
• Run recursive turbo run typecheck before commit ([[typecheck-recursive-vs-per-package]], [[typecheck-before-push]]); commit with --no-verify + targeted checks ([[precommit-runs-full-suite]]); tests need Node 24 ([[tests-need-node-24]]).
• Definition of Done: update the Setup Impact Registry ([[setup-impact-registry]]) at .kiro/specs/admin-setup-wizard/setup-impact.md.
• Cherry-picked/half-built code carries gaps — audit each verify call site ([[cherry-picked-features-carry-gaps]], [[security-agent-false-positives]]).

---

Track A — Pageview counter

Shape: a built-in CMS module apps/cms/src/modules/pageviews/ (the engine — needs runtime changes, a DO, and a scheduled job that extensions cannot access) plus a thin panel UI extension extensions/lumibase-pageview-counter/ for the Studio dashboard (clone of the existing extensions/analytics-panel, but fetching real stats). The sandbox capability proxy exposes no counter/DO access, so the engine cannot be an extension.

A1. Runtime: atomic counter

• packages/runtime/src/interfaces/cache.ts — add to CacheProvider:
  increment(key: string, by?: number, opts?: { ttl?: number }): Promise<number> (returns post-increment value; by default 1; ttl set only on first create). Add a separate optional UniqueCounterProvider { addUnique(key, member, opts?): Promise<void>; countUnique(key): Promise<number> } (HLL is not universal; service does 'addUnique' in provider capability check, else DB fallback).
• packages/runtime/src/adapters/docker/cache.ts — implement increment via ioredis incrby + expire(key, ttl, 'NX'); addUnique/countUnique via PFADD/PFCOUNT. Divergence from get/set: on error rethrow (a wrong count is worse than a caught error the caller can fall back on) — document in a comment.
• apps/cms/src/pageviews/counter-do.ts (new) — Cloudflare PageviewCounter extends DurableObject, one instance per site via idFromName(siteId) (mirrors apps/cms/src/realtime/site-room.ts). SQLite storage (ctx.storage.sql): counters(key PK, value) with INSERT ... ON CONFLICT DO UPDATE SET value = value + :by RETURNING value (atomic in single-threaded DO); uniques(key, member, PK(key,member)) for exact uniques; fetch() handles POST /incr, POST /pfadd, GET /drain?prefix= (read-and-reset). DO lives in the app (imports cloudflare:workers), not the Node-safe runtime pkg.
• packages/runtime/src/adapters/cloudflare/counter.ts (new) — forwarder that calls the DO stub; structural DurableObjectNamespaceLike type (like realtime) to avoid workers-types in the runtime pkg.
• packages/runtime/src/adapters/cloudflare/cache.ts + .../cloudflare/index.ts — CloudflareCacheProvider takes optional injected PAGEVIEW_COUNTER namespace; increment/addUnique/countUnique forward to DO; absent binding → typed CounterUnavailableError so service falls back to db-rollup. Add PAGEVIEW_COUNTER? to CloudflareEnv + factory.
• apps/cms/wrangler.toml — add [[durable_objects.bindings]] name="PAGEVIEW_COUNTER" class_name="PageviewCounter" and a new [[migrations]] tag="v2" new_sqlite_classes=["PageviewCounter"] (append-only; do NOT edit the existing v1/SiteRoom block). Repeat both in every env profile (staging/production/dev/demo). Add a */5 * * * * cron trigger for the flush (per-env crons).
• apps/cms/src/cloudflare.ts — export { PageviewCounter }; wire flush into scheduled() via ctx.waitUntil(...).

A2. Database

• packages/database/src/schema/pageviews.ts (new) — three lumibase_-prefixed tables, id nanoid, site_id FK → lumibase_sites ON DELETE cascade:
  • lumibase_pageview_events (raw hits): path, userId NULL, sessionHash NULL, referrer, userAgent, countryCode, occurredAt; index (siteId, occurredAt).
  • lumibase_pageview_daily (rollup): day date, path, views int, uniques int, updatedAt; unique (siteId, day, path) for onConflictDoUpdate upsert.
  • lumibase_pageview_uniques (daily distinct / CF DB-fallback): day, visitorHash; unique (siteId, day, visitorHash) for onConflictDoNothing.
• packages/database/src/schema/index.ts — export * from './pageviews'.
• packages/database/drizzle/00NN_pageviews.sql (new, hand-written) + meta/_journal.json entry. Number after the publisher-keys migration if both land together.

A3. Module apps/cms/src/modules/pageviews/ (mirror modules/audit layout)

• strategy.ts — interface PageviewStrategy { name; recordHit(siteId, ctx: HitContext); getStats(siteId, range) }.
• strategies/db-rollup.ts (default), hot-counter.ts (uses A1 increment), cdc-event.ts (emits a CdcChangeEvent-shaped record via runtime.queue; also writes the daily rollup so the Studio panel reads local rollup — ClickHouse external only, no read client), hll.ts (Docker PFADD/PFCOUNT; CF DO SQLite set or DB COUNT DISTINCT).
• service.ts PageviewService({ db, runtime, settings }) — picks strategy from pageviews.strategy (default db-rollup on unset/invalid). Attribution: userId from auth if present, else sessionHash = sha256(ip + userAgent + siteId + dailySalt) (never store raw IP); reads pageviews.userTable. Consent gate: if authenticated, check the new analytics consent (A5) via ConsentService; withdrawn → record anonymized only.
• bot-filter.ts — pure/testable: UA denylist, honor DNT/Sec-GPC.
• routes.ts — POST /hit (public, mounted like deliverRouter outside the auth sub-app; runs bot-filter + withRateLimit() from middleware/rate-limit.ts keyed by IP; returns 204; fail-open to db-rollup); GET /stats (authenticated, mounted inside the api sub-app; returns { data }).
• scheduled.ts — runScheduledPageviewFlush(db, runtime, log?): rolls up events → daily; drains Redis/DO counters → daily upsert; never throws (best-effort).
• __tests__/.

A4. Wiring & settings

• apps/cms/src/index.ts — import + mount public /api/v1/pageviews (hit) at top level with rate-limit; mount authenticated stats router on api.
• apps/cms/src/serve.ts — add runScheduledPageviewFlush to a */5 node-cron (separate from the hourly rotation cron).
• packages/shared/src/schemas/pageviews-settings.ts (new, Zod) — keys (scope module): pageviews.enabled (default true), pageviews.strategy enum default db-rollup, pageviews.userTable default lumibase_users, pageviews.hashSalt?, pageviews.respectConsent default true, pageviews.flushIntervalS default 300, pageviews.botFilter default true. Read/write via existing routes/settings.ts; service caches parsed value briefly to avoid a settings read per hit.

A5. Consent

• Add an analytics value to the ConsentType enum in @lumibase/shared schemas (+ migration if the DB constrains it). Gate user-attributed hits on it in service.ts.

A6. Panel extension extensions/lumibase-pageview-counter/

Clone extensions/analytics-panel layout (lumibase-extension.json type panel, capabilities: [], package.json, vite.config.ts, src/index.tsx, src/component.tsx); component calls authenticated GET /api/v1/pageviews/stats instead of demo data. Manifest name lumibase-pageview-counter, autoInstall: true, enabledByDefault: true (Track B).

---

Track B — Signing, verify-everywhere, auto-install

B1. Shared verifier

• packages/shared/src/extensions/signature.ts (new, WebCrypto only) — move sha256→sha256Hex, verifyEd25519Signature→verifyEd25519 out of marketplace.ts. Add verifyBundle(bundleBytes, sig, keyResolver): VerifyResult with reasons missing-fields | hash-mismatch | unknown-key | revoked-key | bad-signature | unsupported-alg | ok. rsa-pss-sha256 → unsupported-alg (only Ed25519 implemented — downgrade guard, don't silently pass). Message signed = raw bundle bytes (matches current marketplace verify). Never throws.
• apps/cms/src/services/extension-verifier.ts (new) — ExtensionVerifierService({ db, env }): resolveKey(keyId) merges env MARKETPLACE_PUBLIC_KEYS + lumibase_publisher_keys (DB overrides env for official/revoked); verifyByMetadata({bundleUrl,...}) (SSRF-guarded fetch via services/ssrf-guard + EXTENSION_BUNDLE_ORIGINS, then verifyBundle); deriveIsOfficial(name, verifyResult) = name.startsWith('lumibase-') && result.ok && resolvedKey.official (server-side ONLY, never from manifest); enforce(row, policy) fail-closed decision.

B2. Signing CLI

• packages/extension-cli/ (new workspace pkg @lumibase/extension-cli, bin lumibase-ext, deps = Node stdlib + @lumibase/shared). Commands: keygen (Ed25519 → SPKI .pub.pem + PKCS8 .key.pem, prints env JSON fragment + SQL row), sign (hash bundle bytes, sign raw bytes, emit sidecar <bundle>.sig.json { bundleSha256, signature, publisherKeyId, signatureAlg:'ed25519' }), verify. Official private key lives only in CI secret/KMS; convention keyId lumibase-official-v1. CI signing workflow = follow-up.

B3. Publisher key registry

• packages/database/drizzle/00NN_publisher_keys.sql (new) + meta/_journal.json — lumibase_publisher_keys(id, key_id UNIQUE, public_key_pem, publisher, official bool, revoked bool, created_at), idempotent IF NOT EXISTS.
• packages/database/src/schema/platform.ts — add publisherKeys table + barrel export.

B4. Manifest / schema / columns

• packages/shared/src/schemas/extension-manifest.ts — add autoInstall (default false), enabledByDefault (default false); isOfficial accepted but documented advisory-only, ignored server-side.
• packages/extension-sdk/src/index.ts — mirror autoInstall?/enabledByDefault? on the interface (types-only).
• lumibase_extensions columns (migration, ADD COLUMN IF NOT EXISTS, defaulted): is_official (server-derived), auto_install, enabled_by_default, verified_at.

B5. Verify integration (fail-closed for official)

• apps/cms/src/extensions/sandbox.ts — inject verifier/verify callback; extend SandboxLoadOptions with signature metadata + isOfficial/requireSignature; in load() after trusted-origin check and before import/cache: if isOfficial || requireSignature verify, on !ok log + return null. This is the last-line gate covering rows inserted outside marketplace.
• apps/cms/src/routes/extensions.ts — CRUD POST/PATCH: verifyByMetadata; lumibase-* → require official (400 else); set server-derived is_official/verified_at; ignore client official claim; honor enabled_by_default; re-verify + evict sandbox cache on bundleUrl/version change; block enabling an official ext whose last verify failed. Dynamic mount: pass signature metadata; SIGNATURE_REQUIRED 403 when official + unverifiable.
• apps/cms/src/routes/marketplace.ts — replace the skippable verify block with mandatory verifier.enforce; SSRF-guard the bundle fetch; enabled from enabled_by_default; verified badge reads persisted is_official/verified_at; /publish verifies + rejects lumibase-* signed by non-official key; /submit rejects lumibase-* (RESERVED_NAMESPACE).
• apps/cms/src/services/item-service.ts + apps/cms/src/extensions/hook-dispatcher.ts — pass signature metadata + is_official into sandbox.load so hooks are gated too.

B6. Auto-install / enable-by-default

• apps/cms/src/modules/setup/official-extensions.ts (new) — typed registry of official lumibase-* (incl. lumibase-pageview-counter).
• apps/cms/src/modules/setup/service.ts — add step 9d after constitution seed, before the initialized flip: for each autoInstall entry, find published marketplace row, verify + official, insert site-scoped row (is_official:true, enabled = enabledByDefault); idempotent; fail-soft (skip on missing row, never block setup). Seed the official public key row into lumibase_publisher_keys.
• apps/cms/src/services/official-extension-reconciler.ts (new) — reconcile(siteId) for other site-creation paths. Invariant: once a row exists, never flip enabled back on (distinguishes never-installed from admin-disabled).
• Toggle-off = existing enabled column via PATCH /extensions/:id { enabled:false }.
• packages/database/scripts/seed-dev.ts — seed official key row for local dev.

---

Definition of Done

• Setup Impact Registry (.kiro/specs/admin-setup-wizard/setup-impact.md): new rows for (a) pageviews (settings keys, new tables, panel auto-install), (b) signing (official key seed + auto-installed exts, new extensions.signaturePolicy setting default require, lumibase_publisher_keys table, additive migrations + one-time reconcile as an upgrade step).
• Update docs/en/api/hono-api-spec.md for changed /publish, /install, and new /pageviews/* contracts; CHANGELOG upgrade notes.

Verification

• Unit (Node 24): bot-filter UA/DNT matrix; sessionHash determinism + no raw IP; settings Zod defaults; strategy fallback; signature.ts full reason matrix (tamper→hash-mismatch, wrong key→bad-signature, revoked→revoked-key, rsa→unsupported-alg); deriveIsOfficial truth table.
• DO test (@cloudflare/vitest-pool-workers, durable-objects skill): concurrent incr to one instance sums correctly; drain-and-reset; pfadd dedup.
• Docker adapter: increment/addUnique/countUnique against Redis; EXPIRE NX sets TTL once.
• Integration (local Postgres, Node 24 — spin up local PG per [[worktree-push-and-test-env]]): migrations apply; POST /hit → flush → GET /stats correct views/uniques; multi-tenant isolation (two siteIds, no leakage); consent-withdrawn hit stores no userId/sessionHash; marketplace install rejects official-without-signature (closes the skip hole); sandbox returns null on official bad-sig; auto-install idempotent + admin-disabled not re-enabled.
• End-to-end in preview: run the CMS dev server, beacon a hit, confirm the lumibase-pageview-counter panel renders real counts in Studio.
• turbo run typecheck (recursive) + targeted vitest before commit; commit --no-verify, split per track/scope, author Javier, no Claude co-author.

Suggested commit sequence

1. runtime increment + adapters + DO + wrangler binding (Track A infra)
2. pageviews schema + migration
3. pageviews module + settings + consent analytics
4. panel extension
5. shared signature verifier + refactor marketplace
6. publisher_keys table + extension columns + manifest flags
7. verify-everywhere integration (sandbox/extensions/marketplace/hooks)
8. extension-cli
9. auto-install (setup step 9d + reconciler + seed)
10. Setup Impact Registry + docs + CHANGELOG

[khuepm]

Start: #261