Import historical registration totals safely and show aggregate event counts

[alexeygrigorev]

Outcome

Safely derive and publish aggregate-only historical registration totals from protected Luma and Eventbrite exports.

For each canonical event/provider/source revision, persist a reviewed count and reconciliation metadata—not legacy attendee rows. The public event detail may render only the exact integer copy N registered. It never renders attendee cards, names, initials, avatars, profiles, attendee links, or any other attendee identity. There is no public attendee directory or identity exception.

Current native DataTalks.Club registrations remain protected records under #46/#47. The public query combines eligible native registrations with historical aggregate contributions only when coverage and replacement rules prove they do not overlap.

Product and architecture authority

• 02 — URL, SEO, robots, sitemap, and cache compatibility
• 05 — Event/registration ownership and privacy
• 06 — Studio/admin API parity
• 07 — Privacy, security, and protected data
• 09 — Idempotent migration controls
• 10 — Verification strategy
• Accepted checked public Event identity/routes in Ship real public content on the final editorial route map #105; future database Event lifecycle in Implement the event lifecycle, public pages, people, and calendar contracts #45; native verified registrations/operations in Implement accountless verified event registration and cancellation #46/Add event registration operations, attendance, exports, and change notifications #47; protected-source rights/retention controls in Implement privacy inventory, consent evidence, rights, retention, and deletion replay #64; anonymous cache/invalidation in Cache anonymous public traffic safely at CloudFront #109.

This owner decision supersedes the earlier Luma row-import and attendee-identity contract. #112 imports no legacy registration row, answer, consent, or attendee-identity evidence and no longer depends on #111 or #23. #111 remains independently authoritative for new DTC registration questions, named-sponsor consent, and the separately gated automatic DTC newsletter behavior.

Protected source facts

Only aggregate/schema evidence may appear in source control, tests, logs, screenshots, APIs, or issue reports. No attendee row, filename/event name, email, person, answer, raw attendee ID, local path, or secret may be copied into evidence.

Luma snapshot

The protected snapshot contains:

• 159 event JSON documents paired one-to-one with 159 CSVs;
• schema version 1, 50,505 unique provider event/guest pairs, and no duplicate normalized event/email pairs;
• 50,456 approved and 49 declined rows; 157 events have rows and two are empty;
• stable event/guest identifiers and timestamps;
• no attendee-visibility, sponsor-share, newsletter, marketing, privacy-notice, or consent field.

Only 64 of the 159 source events exactly match the complete provider URL recorded by the pinned canonical event projection. The other 95 require explicit reviewed mapping or exclusion. Title, slug, path token, date, or fuzzy similarity may rank masked review candidates only; none may activate a total.

Eventbrite snapshot

The protected archive has SHA-256 5cc493c7e9a142d09f5a524d28df486f4fa33ce832210ea0d325025b939744df and contains 210 entries: 209 CSVs and one XLSX. Its verified expansion ratio is 3.80x, with no hidden, traversal, or symlink entries.

The 209 CSVs contain 24,001 parsed attendee rows across 209 unique numeric provider event IDs. Every current row has categorical status Attending, and the aggregate audit found no duplicate protected attendee keys.

Mapping evidence is exact:

• 200 provider numeric event IDs exactly match provider IDs in pinned legacy _data/events.yaml source records; those records bridge to Ship real public content on the final editorial route map #105 canonical event identities by stable checked source key because the current Ship real public content on the final editorial route map #105 projection stores no literal Eventbrite URL/ID value;
• 9 source IDs require explicit reviewed mapping or exclusion through that source-key bridge;
• 27 Eventbrite IDs present in pinned legacy _data/events.yaml have no CSV in this archive and are recorded as source_missing, never inferred as zero.

Three exact ordered CSV schemas are allowlisted after UTF-8 decoding with an optional BOM and RFC-4180 parsing. The header fingerprint is SHA-256 over the exact ordered header strings joined by byte 0x1f:

Schema	Header SHA-256	Columns	CSVs
eventbrite_csv_v1	333061583991588f9b6bc78c9873feb7ddab8711687ee999da2135a4cbef0c7e	23	22
eventbrite_csv_v2	6f7f37db55176240fa695289cf13c8bcbaf86970f00b0ed18c4f2a1a6ee4e9ae	25	12
eventbrite_csv_v3	c3a799fcbcee38d3e1733fc0cd317e84236f5d17241513c1a76b3646a19ea0b8	24	175

Each accepted schema must contain the exact code-owned mappings for Order #, Order Date, Attendee #, and Attendee Status; the numeric event ID comes only from the validated archive-entry contract and is cross-checked against its mapping. Reordered, missing, duplicate, or additional headers create a new unsupported schema and quarantine that entry. The XLSX is never opened or implicitly converted; it is recorded as unsupported_xlsx and quarantined. An unsupported CSV is likewise quarantined rather than partially parsed.

Name, email, ticket text, newsletter/sponsor questions, and every other unused column are ignored after schema validation and never persisted. The source contains aggregate newsletter answers (12,127 affirmative, 9,103 negative, 2,771 blank/absent); #112 stores none of those answers and causes no newsletter, sponsor, consent, or email side effect. A future PII migration would require its own groomed issue and must preserve exact negative/objection evidence.

Canonical mapping and replaceable aggregate overlay

#112 uses the stable checked canonical event source identity and canonical slug already accepted in #105. It does not wait for #45's full database Event lifecycle.

The events app owns these provider-neutral records:

• HistoricalRegistrationSourceRun: UUID, provider (luma or eventbrite), adapter/schema version, whole-source checksum, safe manifest/row/status totals, mapping-set revision, policy version, state, revision, actor/reason, and timestamps. It stores no local path or row data.
• HistoricalEventMapping: provider plus exact external event identifier, Ship real public content on the final editorial route map #105 canonical source identity, canonical slug snapshot, mapping state, mapping revision, reviewer/reason, and timestamps. Provider event IDs are protected operational identifiers, never public output or issue/log evidence.
• HistoricalRegistrationAggregateRevision: immutable source-run/mapping references, eligible count, excluded/quarantined safe counts, coverage boundary, status-policy version, combination policy, checksum, state, and revision.
• One active aggregate pointer per canonical event/provider/coverage slot. Activation and replacement use compare-and-swap revision guards and a uniqueness constraint; history is never overwritten.

Mapping state is exactly review_required, mapped, excluded, or source_missing. Aggregate state is exactly staged, validated, active, superseded, quarantined, or rolled_back. Only an authorized, reviewed mapped record with a validated aggregate may become active.

An exact provider identifier/full-provider-URL match may prepopulate a mapping proposal but still records the mapping-set revision used for activation. Title, event name, filename, slug similarity, date proximity, or fuzzy matching never activates or silently excludes a source. An exclusion requires reviewer, bounded reason code, free-text reason kept out of public evidence, and revision.

When #45 introduces database-owned Event UUIDs, an expand-and-contract migration resolves each accepted #105 source identity/slug to exactly one Event, attaches the aggregate records, reconciles counts, and only then switches the query. Missing, duplicate, or changed identity blocks cutover; it never guesses.

Aggregate derivation and status policy

The staging adapter reads only an explicitly supplied protected source reference. It rejects hidden entries, traversal, symlinks, duplicate entries/IDs, malformed encodings/CSV/JSON, zip bombs or configured size/count limits, checksum drift, mismatched Luma JSON/CSV pairs, unsupported schema/status, and unsafe archive structure before activation.

It streams rows, validates the exact provider schema, and deduplicates in memory by the provider event/registration identity. The temporary set is discarded after derivation; no row, email, name, attendee identifier, attendee digest, answer, timestamp, or provider payload is written to the application database or report.

The versioned MVP status policy is:

• Luma approved: eligible;
• Eventbrite Attending: eligible;
• declined, cancelled, rejected, duplicate, quarantined, malformed, unknown, or excluded records: not eligible;
• no status is interpreted as attendance/check-in evidence.

Unknown statuses quarantine that provider-event aggregate. Safe reports contain only source checksum, provider, schema/policy/mapping revisions, event-count totals, row-count totals, state counts, and bounded reason codes.

Dry-run and replay of the same source checksum/schema/policy/mapping revision are deterministic no-ops. A changed source creates a new immutable staged revision; it never mutates the active total silently. Activation is atomic for the reviewed mapping set, keeps the prior active revision available for rollback, and publishes no partial candidate.

No-double-count query and replacement policy

The events query service is the only source of the public total. It returns an integer plus a total revision/completeness state, never provider breakdown or attendee data.

Each count contribution occupies an explicit canonical-event coverage slot. Exactly one source may be active in a slot:

• one aggregate revision; or
• a future reviewed row-level replacement projection.

Replacing an aggregate with later row-level migration atomically marks the aggregate superseded and activates the replacement in the same transaction. The query can therefore count the aggregate or its replacement, never both. Rollback restores the prior pointer and total revision without deleting history.

Multiple historical providers for one event are not additive by default. A reviewer must classify their coverage as:

• additive_disjoint, with evidence of non-overlapping provider/time coverage;
• replacement, naming the contribution that wins; or
• exclude.

Unknown or overlapping coverage quarantines the candidate and leaves the last accepted public total unchanged. Aggregate-only data cannot deduplicate people across providers, so it must never claim that title/date/provider similarity proves disjointness.

Native DTC rows use a separate coverage slot beginning at the reviewed cutover boundary. Only active confirmed, attended, and no_show native registrations count; pending, cancelled, expired, rejected, duplicate/quarantined, and deleted/anonymized records do not. A missing/uncertain boundary blocks aggregation rather than double-counting historical and native sources.

A canonical event with complete accepted coverage renders exactly N registered, using an unrounded non-negative integer. registered never becomes attended. A source_missing, review-required, quarantined, or incomplete coverage state is never presented as zero; the count is omitted with no attendee fallback until reconciliation succeeds.

Public event detail, cache, and invalidation

The count appears only on the canonical detail /events/<slug> owned by #105/#45. The hub does not gain a count in this issue. No public response contains provider totals, source state, mappings, filenames, external event IDs, attendee fields, initials, avatars, profiles, a list/search/export endpoint, or custom answers.

Protected staging, Studio, admin API, native registration, and management routes are private/no-store/noindex and zero-TTL. Development remains noindex/nofollow and robots-disallowed.

Aggregate activation, replacement, rollback, mapping invalidation, native confirmation/reactivation/cancellation, deletion/anonymization, or coverage-state change increments one public total revision and creates one durable after-commit invalidation intent for /events/<slug>. Duplicate work coalesces by event plus total revision. Until #109's public-cache isolation/invalidation contract is proven, event details carrying a count remain zero-TTL/no-store. A failed invalidation retries/alerts and cannot publish a partial candidate or an identity.

Studio and admin API parity

Studio:

• GET /studio/events/historical-registration-totals/ — provider-neutral runs and safe aggregate state;
• GET/POST /studio/events/historical-registration-totals/mappings/ — review/revise/map/exclude exact provider-event mappings;
• GET /studio/events/historical-registration-totals/<uuid>/ — safe source/mapping/reconciliation preview and results;
• confirmed POST actions on the detail for dry-run, validate, activate, cancel, and rollback;
• GET /studio/events/<canonical-key>/registration-total/ — protected contribution/completeness preview without attendee rows.

Admin API parity:

• GET/POST /api/v1/admin/historical-registration-imports;
• GET /api/v1/admin/historical-registration-imports/<uuid>;
• POST /api/v1/admin/historical-registration-imports/<uuid>/{dry-run,validate,activate,cancel,rollback} with Idempotency-Key;
• GET/POST /api/v1/admin/historical-event-mappings and PATCH /api/v1/admin/historical-event-mappings/<uuid> with If-Match;
• GET /api/v1/admin/events/<canonical-key>/registration-total.

The local protected-source adapter registers a checksum/reference; Studio/API never accept or return an arbitrary server path or upload the archive. All adapters call the same events application services and enforce the same state transitions, mapping/coverage validation, revisions, idempotency, authorization, results, and rollback.

Register events.historical_registration_mapping.manage and events.historical_registration_import.manage. Exact provider event identifiers are visible only where necessary to the mapping capability and are masked from ordinary run views. Every map/exclude/validate/activate/replace/rollback action records actor, reason, revisions, source checksum, safe aggregate counts, prior/new state, and outcome—never attendee data, raw paths, filenames, row identifiers, or answers. OpenAPI and tests use synthetic values only.

Failure and rollback

Validation or mapping failure activates nothing. A process crash/replay resumes or deterministically returns the stored result without duplicate revisions. Changed checksum, unsupported schema/status, missing source, ambiguous mapping, overlapping provider/native coverage, count mismatch, stale revision, or concurrent activation quarantines the candidate and preserves the last accepted public total.

Rollback is a confirmed, reasoned, idempotent pointer transition to the prior accepted aggregate set, increments the public total revision, and invalidates the event detail. It never deletes native registrations, creates legacy rows, rewrites another provider, sends mail, or exposes protected data.

Source retention/deletion remains outside the aggregate database and follows #64's approved protected-source handling. The application retains only minimized non-PII aggregate/provenance/audit data. No failure triggers verification/confirmation/newsletter/transactional email, sponsor sharing, consent creation, public identity, or provider write-back.

Non-goals

• No legacy EventRegistration, answer, consent, email, name, person, source guest ID/digest, attendee timestamp, or other row-level PII storage/import.
• No attendee card, name/initial, avatar/photo, attendee-identity evidence, public attendee identity, directory, search, list, or export.
• No fuzzy/automatic mapping, silent Event creation/publication, inferred zero for missing archives, overlapping aggregate sum, attendance inference, or unreviewed replacement.
• No Luma/Eventbrite API, webhook, write-back, provider credential management, ticket/payment/QR/wallet import, or attendance migration.
• No newsletter enrollment/question, sponsor share/export, transactional/verification email, privacy acknowledgement, or consent side effect from historical sources.
• No change to Add configurable event registration questions and sponsor consent #111's automatic DataTalks.Club newsletter rule for new verified DTC registrations.
• No broad Implement the event lifecycle, public pages, people, and calendar contracts #45 event lifecycle implementation, production deployment, or public indexing change.

Acceptance criteria

• Provider-neutral models enforce immutable source/aggregate revisions, exact mapping/aggregate states, source checksum/schema/status/mapping policy, one active contribution per coverage slot, and revision-guarded activation/rollback.
• The Luma adapter reconciles 159 paired sources and exact 50,505/50,456/49 totals; only 64 exact full-URL matches are proposals and all 95 others are explicitly reviewed/map-or-exclude without guessed activation.
• The Eventbrite adapter verifies the exact archive checksum/210-entry/209-CSV/one-XLSX/24,001-row/209-ID facts, accepts only the three pinned CSV schema fingerprints, quarantines XLSX/unsupported schemas, reconciles 200 exact legacy-source-ID bridge matches, 9 reviewed map-or-exclude cases, and 27 source-missing legacy IDs without claiming that Ship real public content on the final editorial route map #105 stores Eventbrite IDs.
• Derivation persists only counts/provenance/mapping/audit metadata and proves zero legacy row, PII, attendee digest, answer, notice, consent, email, newsletter, sponsor, or provider side effect.
• Exact status handling counts only Luma approved and Eventbrite Attending; declined/cancelled/rejected/unknown/quarantined/excluded values never contribute or imply attendance.
• Replay/checksum drift/crash/concurrency/mapping revision produces deterministic no-op or quarantine behavior and never partially activates a candidate.
• Cross-provider, native-cutover, and future row-replacement coverage is explicitly additive/replacement/excluded; ambiguous overlap fails closed and aggregate replacement can never be counted with the replacing rows.
• A complete event detail renders only exact N registered; incomplete/review/source-missing state is not shown as zero, and no public route/serializer/cache/search/sitemap/structured data exposes attendee identity or provider breakdown.
• Total-changing mutations increment the representation revision and create/coalesce the exact durable after-commit invalidation; count pages remain zero-TTL until Cache anonymous public traffic safely at CloudFront #109 proof.
• Studio/admin API capability, shared-service, permission/masking, revision/idempotency, audit/redaction, result, OpenAPI, and rollback parity covers every mapping/import/total-preview operation.
• Focused Django/model/migration/import/security/privacy/parity/OpenAPI/cache/SEO/full-event and core Playwright tests pass through repository uv/Make targets.
• Templates are readable/non-minified, WCAG 2.2 AA critical-flow behavior is verified at desktop/mobile, and screenshots contain no attendee/source PII.
• [HUMAN] An authorized operator verifies protected-source checksums, all exact/review/exclude/source-missing mappings, status/schema/coverage policy, dry-run/replay/activation/rollback, public totals, and cache invalidation in development with aggregate-only redacted evidence and zero stored/reported attendee rows.

Automated and failure scenarios

1. Both providers: exact/missing/changed checksum, duplicate/hidden/traversal/symlink entry, decompression/size bound, malformed encoding/CSV/JSON, missing/extra/reordered header, unsupported XLSX/schema/status, duplicate event/attendee key, and row/count mismatch.
2. Exact/review-required/mapped/excluded/source-missing mappings; changed canonical source identity/slug; stale mapping revision; two targets; one target with two providers; title/date/slug/fuzzy candidate; denied role.
3. Zero/one/many approved or Attending rows; declined/cancelled/rejected/unknown; empty event; duplicate source; changed snapshot; dry-run/replay/crash/concurrent activate/cancel/rollback.
4. Historical-only, native-only, disjoint historical+native, overlapping/unknown cutover, disjoint providers, provider replacement, aggregate-to-row replacement, failed replacement, and rollback; assert every slot is counted exactly once.
5. Public total at zero/one/many/incomplete/source-missing/quarantined states; stale cache, invalidation retry/terminal failure, and prior-active fallback.
6. Audit/log/metric/error/API/OpenAPI/export/screenshot canaries prove no local path, filename/event name, raw row, attendee ID/digest, email/name, answer, provider payload, token, or secret leakage and no mail/newsletter/sponsor/provider job.

Browser acceptance

At desktop about 1440×900 and mobile about 390×844 using synthetic fixtures only:

1. Navigate /events to /events/<slug>; verify exact 0 registered, 1 registered, and plural-count rendering where coverage is complete, plus an omitted count for incomplete/quarantined evidence.
2. Verify the detail has no attendee card/name/initial/avatar/profile/list/search/export/provider-total UI in HTML, accessibility tree, structured data, network responses, or cached responses.
3. In Studio review exact/masked mapping proposals, map/exclude a provider event, validate and activate a staged aggregate, inspect safe reconciliation, replay, replace, and roll back; exercise denied, stale, conflicting-coverage, unsupported-schema, and source-missing states.
4. Warm the public detail before and after a synthetic total change; verify revision/invalidation or required zero-TTL behavior without exposing provider or attendee data.
5. Inspect desktop/mobile screenshots plus browser history/network/console for expected pages rather than debug/404 states and for absence of protected values.

Required specification and issue amendments

Implementation updates specifications 02, 05, 06, 07, 09, and 10 for the aggregate overlay, exact public count, no-public-identity boundary, provider-neutral management, protected-source minimization, replacement/deduplication, invalidation, rollback, and verification.

#45, #46, and #47 are amended now: public event pages may expose only #112's exact aggregate registered total and never an attendee identity/list. #111 is amended to remove the abandoned attendee-identity exception and row-import dependency.

Dependencies and delivery

• Depends on the accepted Ship real public content on the final editorial route map #105 checked canonical event source identity, slug, public detail, and protected projection contract plus Add shared service, configuration, operation, and durable-job primitives #31–Complete production admin API identity and credential lifecycle controls #33 shared service/parity/audit foundations.
• Does not wait for the full Implement the event lifecycle, public pages, people, and calendar contracts #45 database Event lifecycle. Implement the event lifecycle, public pages, people, and calendar contracts #45 must later attach the overlay by exact source identity through the guarded expand-and-contract migration above.
• Implement accountless verified event registration and cancellation #46/Add event registration operations, attendance, exports, and change notifications #47 supply native registration state when implemented, but aggregate-source ingestion and synthetic public-count tests can proceed independently; integration adds the reviewed native cutover slot rather than changing historical totals.
• Add configurable event registration questions and sponsor consent #111 and Decision: Approve privacy ownership, retention, and minors policy #23 are not dependencies: Import historical registration totals safely and show aggregate event counts #112 imports no question, answer, PII, notice, consent, or newsletter state.
• Implement privacy inventory, consent evidence, rights, retention, and deletion replay #64 governs authorized protected-source handling and eventual source disposal; it is not a prerequisite for storing non-PII aggregate revisions. The real protected-source run remains the named HUMAN gate.
• Cache anonymous public traffic safely at CloudFront #109 is required before positive edge caching. Without it, the public count works at zero TTL/no-store.
• Historical import never invokes Build the durable Relay delivery intent and status lifecycle #49 or any provider/newsletter/sponsor delivery.

Because protected-source reconciliation requires an authorized operator, implementation commits use Refs #112, retain human, and leave the issue open until the HUMAN criterion passes. No protected export, mapping values, attendee data, provider credential, runtime state, or screenshot is committed.