Anonymous Session Store

Server-side record of a visitor's guided-selection activity (issue #114), keyed by a first-party cookie. This is the durable half of session state — the stepper's own reload persistence stays in client sessionStorage (see guided-selection-stepper.md); this store exists so sessions survive across visits and can later feed resume (#115), lifecycle/purge (#116), and external association (Eloqua, #104).

Analytics source of truth (snapshot-first, #348)

Per the recorded decision #357 (snapshot-first hybrid), this client-authoritative snapshot — not a separate event table — is the backbone of v1 behavioral analytics. Every downstream metric (the drop-off funnel #350, lead intelligence #351, demand #353, track effectiveness #354, experimentation #355) is computed by summarizing these rows. There is deliberately no interaction-grain event table in v1; the time-on-question / back-navigation event stream is split out to #358 (v2).

Two properties make the snapshot trustworthy enough to carry analytics, both hardened by #348:

• Per-answer granularity with an abandonment flush. The client mirrors session state on every answer, and — critically — flushes the pending snapshot on abandonment (pagehide / visibilitychange:hidden / SPA teardown), so an abandoned session's latest snapshot reflects its true last-answered factor. That is the load-bearing assumption for the drop-off funnel; see Write path.
• Write-time bot/internal classification. Each session row carries a derived client_class (human | bot | internal) so non-visitor traffic can be filtered out of trustworthy numbers (#349) — derived from the request User-Agent, which is never itself stored (see Anonymity is a store property). The query-time complement — the shared genuine-human filter, the funnel-stage definitions, and the pre-aggregated rollup that consume this signal — is the Behavioral Metrics Dictionary.

Identity model

Property	Value
Cookie	ns_session, first-party, HttpOnly, SameSite=Lax, Secure outside dev
Value	Opaque server-minted UUID — carries no meaning, encodes nothing
Lifetime	180 days, rolling (refreshed on every guided-selection request)
Scope	Set only on paths that carry selection activity: /select, /select/*, /api/session
Login	None — identity is anonymous by construction

The server hook (src/hooks.server.ts) resolves the identity once per request and stashes it on event.locals.sessionId. A missing or malformed cookie value (anything that isn't a UUID) is replaced with a fresh id — never trusted, never resurrected.

No row is written at identity time. A selection_sessions row exists only once selection activity upserts it through PUT /api/session, so crawlers and bounce visits never create database rows.

Schema — selection_sessions

Defined in src/lib/server/db/schema.ts (created in drizzle/0011_warm_lake.sql; track_slug added in 0020, client_class in 0033).

Column	Type	Meaning
id	text PK	The ns_session UUID
created_at	text	First activity write (ISO 8601)
updated_at	text	Last activity write — the expiry/purge basis for #116
track_slug	text	Attribution slug (#153): the flow the session ran on, 'default' for bare /select; insert-only, immutable
answers_json	text	Sanitized answers map: factorId → optionId | null (null = explicitly skipped)
recommendation_json	text	Snapshot, ids only: {recommendedSystemId, alternativeSystemIds, ruleId}
systems_viewed_json	text	JSON array of system ids the results surfaced, shortlist order
completed_at	text	First time the visitor reached results; write-once (never un-completes), NULL = in progress
client_class	text	Derived bot/internal signal (#348): human | bot | internal, insert-only & immutable; NULL = legacy row created before the signal existed

Anonymity is a store property

There are deliberately no PII columns — no email, name, IP, or user agent — and the write path enforces that structurally rather than by policy:

• sanitizeSessionPayload (src/lib/server/sessions.ts) reduces every client payload to id-shaped tokens (/^[a-zA-Z0-9][a-zA-Z0-9_-]{0,99}$/). Free text — an email address, a phone number — is rejected entry-by-entry; unknown keys are dropped; collections are capped (100 answers, 50 systems).
• A schema test (sessions.spec.ts) pins the table's column list to the allowlist above, so adding a column is a conscious, reviewed act.
• client_class (#348) is on the allowlist because it is a derived, non-PII bucket: classifyClient (src/lib/server/client-classification.ts) reduces the request User-Agent to human | bot | internal and the UA itself is discarded — only the bucket is persisted. A one-way UA hash was deliberately rejected (still a fingerprinting vector); a bucket keeps the store PII-free by construction while giving #349 the traffic filter it needs. An internal-marker cookie (ns_internal) lets staff self-flag demo/QA traffic without any identity.

Anything that does identify a person belongs in an external association table that references selection_sessions.id via its own additive migration — realized by the Eloqua contact association (#104, eloqua_session_contacts — see eloqua-integration.md). This store never learns about those tables (association-friendly by design).

Write path

stepper (SelectionFlow.svelte)
  └─ createSessionMirror: debounce(400ms) + flush on abandonment
     └─ PUT /api/session   { answers, recommendation, systemsViewed, completed }
       └─ hooks.server.ts        → event.locals.sessionId (mint/refresh ns_session)
       └─ classifyClient         → client_class (from UA + ns_internal cookie; UA discarded)
       └─ sanitizeSessionPayload → ids only, or 400
       └─ upsertSession          → INSERT … ON CONFLICT DO UPDATE (updated_at refreshed,
                                    completed_at COALESCE — sticky once set;
                                    track_slug + client_class insert-only, immutable)

The mirror is best-effort by design: a failed or slow write never affects the flow's UX (AC: "without changing the existing client UX"), and an untouched fresh visit posts nothing. This endpoint returns only { ok: true } — session state is read back through the /select page load, not the API.

Consent-gated (#347). The snapshot write above runs only with Iubenda measurement consent, read server-side from the _iub_cs-<siteId> cookie (fail closed when absent/unreadable/unconfigured). Cross-session recognition (#106) runs only with marketing consent. This is the lawful-basis boundary — see privacy-consent.md for the two-tier model, retention windows, and right-to-delete.

Per-answer granularity & abandonment flush (#348). The mirror re-runs on every answer change and coalesces rapid answers behind a 400ms trailing debounce, so at most one write settles per pause. The debounce/flush logic lives in the pure createSessionMirror coordinator (src/lib/session-mirror.ts, unit-tested in session-mirror.spec.ts) so the guarantee is testable without a DOM. Crucially, the coordinator's flush() is wired to pagehide, visibilitychange:hidden, and effect teardown — so an answer given inside the last debounce window before the visitor leaves is flushed, not dropped. This is what lets an abandoned session's latest snapshot name the visitor's true last-answered factor, the load-bearing input to the drop-off funnel (#350).

Read path (resume, #115)

The /select server load reads the visitor's session (getSession), parses answers_json with the same tolerance as client storage (stale factor/option ids vanish), and hands the stepper a resume view — or null when there is no cookie, no row, an all-stale answer set, or any read failure (degrade, don't error). The stepper's pure resumeOffer precedence then decides what to offer; see guided-selection-stepper.md for the returning-visitor UX.

Lifecycle, retention & privacy (#116)

TTL

Sessions live SESSION_TTL_DAYS days from last activity (updated_at), default 90 (wrangler.jsonc vars; resolveTtlDays falls back to the default on a missing or nonsense value — misconfiguration can neither disable expiry nor expire everything). Expiry is enforced in two places:

• On read — getSession treats a record past its TTL as absent, so an expired session behaves exactly like no session (no resume offer, no error).
• On schedule — a daily Cron Trigger (03:17 UTC) runs purgeExpiredSessions, deleting every record whose last activity predates the cutoff. The trigger lives on a cron-only companion worker (workers/session-purge/) bound to the same D1 database — the SvelteKit adapter owns the app worker's entry, so the scheduled handler gets its own deployable. Deploy it with npm run deploy:purge; exercise it locally with npm run purge:dev and curl "http://localhost:<port>/__scheduled?cron=17+3+*+*+*". The same worker also carries the every-30-min Eloqua retry-queue schedule (#105), routed by controller.cron — see eloqua-integration.md.

Purge guarantees

• Idempotent — strictly-older-than-cutoff deletion; a rerun deletes nothing new.
• Format-proof — comparison via SQLite datetime(), so D1 CURRENT_TIMESTAMP and ISO timestamps order correctly.
• Cascading — integration tables referencing selection_sessions.id MUST declare ON DELETE CASCADE; the purge then removes their rows in the same statement. This is the association contract's deletion half: attach to a session and you inherit its retention.
• Observable — every run appends a summary to the admin audit trail (actor system:cron, action purge: count, TTL, cutoff) and logs a JSON line (event: "session-purge") visible in wrangler tail; a thrown failure surfaces as a failed cron invocation in the Cloudflare dashboard.

Privacy guarantees

Question	Answer
What is stored?	Knowledge-base ids only (answers, recommendation snapshot, shortlist) keyed by an opaque UUID — see Anonymity is a store property. No PII columns exist; the sanitizer rejects free text.
For how long?	SESSION_TTL_DAYS (default 90) from last activity, then invisible to reads and deleted by the next daily purge.
Anonymity until consent	The store never identifies a person. Identification happens only when the visitor explicitly provides it to an integration (e.g. the Eloqua lead form, #104/#105), which stores it in its own cascade-attached table — this store still never learns it.
How does deletion work?	Automatic: TTL + daily purge, cascading over attached integration rows. Manual: deleting a selection_sessions row removes everything attached to it.

Share links (#839)

The "Copy link to these results" permalink is answer-serialized, not a stored token — it never touches this store. The ?share= param is a base64url payload of exactly {answers, date}: knowledge-base ids plus the ISO date the link was generated. Consequences, by construction:

• No PII in the link. Only factor/option ids and a date are encodable; email, names, and session ids have no path into the payload.
• No server-side record. Nothing is written when a link is created, so token lifetime and revocation are non-concepts — there is nothing to expire or revoke, and links keep working after the sharer's session TTL.
• Recipient sovereignty. Opening a shared link renders a live recomputation under the recipient's own session and consent state; the sharer's session row is never referenced or attributed. If the recipient interacts, the answers become their session via the normal write path.
• Catalog drift is disclosed, not frozen. The shared view renders a dated banner ("recomputed from today's catalog"); stale ids in an old link are dropped with the same tolerance as stored sessions.

Decision record: issue #839 (recorded before build). Encoder/decoder: src/lib/share-results.ts.

Data export / BI access (#356)

Admins outgrow the built-in charts, so the data must not be a walled garden. The authenticated endpoint GET /admin/analytics/export streams sessions for external analysis in two formats:

• CSV (default) — a text/csv attachment (Content-Disposition, no-store), one row per session, RFC 4180 escaped. Loads straight into a spreadsheet or BI tool.
• JSON (?format=json) — a self-describing { count, columns, rows } payload for programmatic consumers; the field schema (columns) travels with the data.

Both views project through one column schema (SESSION_EXPORT_COLUMNS in src/lib/server/analytics/session-export.ts), so CSV and JSON can never drift.

Authorization. The endpoint self-guards with requireCapability(view_audit) (granted to every admin role) — a +server.ts endpoint does not inherit the /admin layout guard, so it enforces the same capability the analytics page does, from the same locals.admin. The analytics filter bar links to it with an Export CSV button carrying the current track/time filter.

Filtering. Honours the same track + time (from/to, YYYY-MM-DD UTC) filter as the analytics page. Application/budget are answer-value segments that don't apply to the session grain (a session's application is unknown until it answers that factor), so the export ignores them — the same call the completion funnel makes.

Exported field schema

Every field is a knowledge-base id, a timestamp, a count, or a boolean — there is no PII column because the store has none. NULL scalar columns export as an empty string; list columns are pipe-separated in shortlist order.

Column	Description
session_id	Opaque anonymous session id (server-minted UUID).
created_at	When the session was first recorded (UTC).
updated_at	Last selection-activity write (UTC) — the retention/expiry basis.
completed_at	When the visitor first reached results, or empty if never.
track_slug	Track attribution slug (default = the bare /select flow).
client_class	Write-time classification (human; empty for legacy rows). Exports are human-only.
experiment_id	A/B experiment assigned at start (#355), or empty if none.
experiment_variant_id	A/B variant arm assigned, or empty if none.
completed	true when the session reached results, else false.
engaged	true when the session answered ≥ 1 factor, else false.
answer_count	Number of factors the session answered (non-skipped).
recommended_system_id	System id the engine recommended, or empty if none surfaced.
alternative_system_ids	Alternative system ids surfaced, pipe-separated (shortlist order).
rule_id	The right-sizing rule id that fired, or empty if none.
systems_viewed	System ids the results surface showed, pipe-separated.
systems_viewed_count	Number of systems the results surface showed.

Event export follows #358. Per-interaction event export awaits the session_events stream (#358); this session-grain export is deliverable today.

Export guarantees

• Genuine-human only — the read's WHERE is the shared genuineHumanTraffic() predicate (#349), so bot, internal, legacy-unclassified, and (in production) test rows never reach an export, exactly as they never reach a metric.
• Retention-honest — the export applies the same TTL cutoff the purge uses (datetime(updated_at) >= now - SESSION_TTL_DAYS), so a row that is purge-eligible but not yet deleted is never exported. Combined with the PII-free-by-construction store, an export can contain no personal data and no expired data.
• Reproducible — rows are ordered oldest-first (created_at), so the same segment yields the same ordering across runs.

Seed data

seed/sql/050-selection-sessions.sql seeds every state analytics needs to exercise — in-progress, completed, expired, track-attributed, and all client_class buckets (human, bot, internal, plus a NULL legacy row) — with seed- prefixed ids, which can never collide with live cookie identities (those are UUIDs).