Profile registration fix checklist

2026-10-24 coordination: Read the retraction simplification handoff before REG-17, REG-20 or REG-F01/F02. Registration performance work remains relevant; current retraction freshness/publication guarantees are being reconsidered. Existing start/commit instructions below still apply.

Working instructions — keep visible

• The user resumed this effort with “Please continue” on 2026-10-24. Work is active on fix/profile-registration-cost.

• Work through the checklist one item at a time, without Railspike or Railspike step approvals.

• Create a dedicated branch before starting checklist work.

• After each item, update this checklist with its outcome, verification, and any remaining limitations, and commit that item's work together with the checklist update before starting the next item.

• Mark an item complete only when its required work and verification are complete. Keep blocked or partial items open and record why.

• Keep unrelated workspace changes out of the item commits.

• Follow the retraction simplification handoff: keep authorization and transaction correctness, but reassess the old immediate-suppression requirements rather than expanding that machinery. The canceled sweep-diagnostics project is outside this work.

Based on Profile registration cost and remediation. This checklist covers engineering work and verification, excluding outage containment. Items are ordered by dependency; all remain open until implemented and verified. Creating this checklist does not start implementation.

The intended result is that registering an independent identity does work proportional to that identity's records and dependencies, while preserving canonical identity, approval, authorship, and publication rules.

Sizing assessment, 2026-10-24: 2.5 R1 units for REG-01–24, with a subjective range of 2–3. Including REG-F01–F02, the independently assessed whole scope is roughly 3–4 units. One unit means the largest well-scoped, releasable slice comfortably fitting one Railspike cycle, including verification and documentation; these values are not hours or sums of checkbox estimates. Suggested core split: (1) measured query/index and targeted-update improvements, (2) bounded identity/approval propagation, (3) incremental consistency and recovery. Each slice must include its applicable validation and recovery, with shared final verification assigned explicitly. Treat optional read availability as a separate cycle.

This is a proposed-backlog estimate, not a started-cycle checkpoint or implementation approval. Rater: Codex, single exposed rater, after source/history inspection and production-log review; baseline application revision 2d91c8029. Frozen R1 report, diff-review, and feedback hashes were verified against the study ledger. Main uncertainties are historical approval dependencies and cross-store recovery. Reassess when the real-data baseline and individual cycle scopes are established; preserve this original assessment.

• REG-01 — Establish a repeatable baseline. Use isolated working copies of QDB's downloaded database and matching canonical dataset for the main baseline; supplement with synthetic fixtures for controlled scaling. Measure fresh registration and linking an identity with existing content separately. Record revisions, hashes, fixture characteristics, and benchmark commands. Verified: matched QDB canonical head and database, three runs of both write flows, small-fixture integration check, integrity/input-preservation checks, and retained baseline evidence and commands. Initial runner commit: 56cbe951d; this item-completion commit includes the real-data results.

• REG-02 — Expose the cost of each stage. Record execution-lock wait/hold time, signature verification, Git commit, approval derivation, profile counts, activity updates, SQLite commit, and artifact invalidation. Include changed-profile/affected-row counts, peak memory, and fallback reasons. Reuse existing timing fields and keep private material out of logs. Verified: seven focused checks and both QDB-scale write flows; instrumented results. Counts are separate from millisecond timings; affected rows count SQL updates, not distinct value changes.

• REG-03 — Define correctness and performance acceptance. Specify canonical expected outcomes for registration and linking. Set measured latency/lock-duration budgets for a representative host, with margin below the request lock timeout. Require that adding unrelated profiles does not add one activity update per profile. Recorded: acceptance criteria, including numeric local/host targets, workload, invariants, scaling inputs and ownership of final checks. Targets are not yet achieved; production-host verification remains pending.

• REG-04 — Index activity by author identity. Add activity(author_identity_id) to the maintained read-model schema. Verify the actual attribution update uses the index and compare its cost against the baseline. Verified: schema 26, prior-schema startup and query-plan check, actual isolated ./v3 rebuild, six focused checks, and QDB measurements. Required REG-06 schema obligations were included in this commit; its later audit remains open.

• REG-05 — Inspect the post-linking query. Measure the predicate matching bootstrap post ID or author identity. Add an author index or change the query only if its plan and workload justify it; preserve rows matching either condition without duplicate effects. Verified: seven-sample QDB query comparison, multi-index OR plan, overlapping-branch regression, schema 27/spec/migration, seven focused checks and isolated ./v3 rebuild.

• REG-06 — Complete the schema contract. Increment the schema version from 25 to the next available version for the change set, update the schema specification, and add a regression starting from the previous schema. Verify normal startup, ./v3 rebuild, and affected UI/API flows. Keep schema identity consistent across all maintained build paths. Verified: versions/spec/startup regressions shipped with REG-04/05; audit updated the expected index inventory and passed all 34 schema, metadata, candidate/promotion, high-traffic, snapshot, registration and directory-invalidation checks. Actual isolated CLI rebuilds passed at versions 26 and 27.

• REG-07 — Compute profile changes explicitly. Compare derived approval, attribution, and count fields with stored values. Update only profiles whose fields changed rather than issuing an update for every identity. Verified: NULL-safe update predicate and retained change set; SQLite observer triggers verify zero unrelated profile writes in fresh registration and one changed profile in the historical fixture. Schema 28/startup/CLI and parity checks passed. One obsolete approval-copy assertion also fails at pre-change dceb127c4; see implementation evidence.

• REG-08 — Remove the all-profile activity loop. Refresh activity only for the newly linked identity and identities with changed public attribution or approval. Use the computed approval values instead of loading them again per profile. Verify unrelated activity remains untouched. Verified: retained profile change set replaces the all-profile loop; NULL-safe activity predicates skip identical attribution and computed approval values avoid per-profile reloads. Observer triggers verify unrelated authors remain untouched and the fixture needs at most two activity statements. Schema 29, six focused checks and isolated CLI rebuild passed.

• REG-09 — Verify the intermediate improvement. Repeat the baseline and query-plan checks after indexing and targeted updates. Record remaining time spent loading all posts, deriving approvals, and counting profiles; do not treat the index alone as completion. Verified: five paired QDB runs and both indexed query plans; retained results. Median fresh/historical wall time is 1,183.9/944.8 ms versus 18,910.4/18,325.1 ms initially. Fresh median misses the 1,000 ms target; full-site loading and approximately 300 ms approval derivation remain. This is intermediate evidence, not production or final acceptance.

• REG-10 — Distinguish registration from historical linking. Define when an identity is independent and new versus connected to existing posts, bootstrap records, seeds, approvals, or invitations. Do not infer independence merely because its profile row is absent.

• REG-11 — Implement the independent-registration path. Create the profile, bootstrap records, username route, applicable approval state, counts, and attribution without loading every post or refreshing every profile. Preserve hidden-bootstrap counting and username-routing rules.

• REG-12 — Bound historical authorship updates. Locate affected posts and activity through indexed identity/bootstrap lookups. Recompute counts for affected owners, including legacy bootstrap ownership and deduplication rules.

• REG-13 — Propagate approval changes through dependencies. Define and implement the lookup/index strategy for approval and invitation dependencies. Use a deduplicated worklist of affected identities, preserving deterministic ordering, canonical acceptance evidence, and termination in the presence of cycles. Apply the schema contract to any additional derived structures.

• REG-14 — Eliminate repeated invitation replay. Avoid sorting/replaying the same unrelated post history on each approval pass. Reuse validated candidate evidence within the operation or maintain dependencies with explicit invalidation rules.

• REG-15 — Make artifact invalidation follow actual effects. Identify all pages affected by changed profiles, approval, attribution, or counts. Invalidate those artifacts without missing transitive changes or unnecessarily invalidating unrelated presentation.

• REG-16 — Validate the incremental starting point. Check that the model represents the expected canonical parent before applying the new write. Define reconciliation for mismatched heads; never mark skipped canonical changes as indexed merely by updating metadata.

• REG-17 — Reduce the critical section safely. Move expensive preparation outside the write lock where safe, then revalidate mutable acceptance inputs under the lock. Preserve canonical serialization, authorization, and transaction boundaries. Coordinate retraction/publication changes with the simplification handoff; do not entrench current guards as permanent requirements or remove them without an agreed replacement.

• REG-18 — Replace unbounded request-time rebuild fallback. Specify an observable, deduplicated maintenance path for incremental failure. Clearly distinguish a rejected write from a canonical write that succeeded while derived state remains pending. Handle candidate validation, repository advancement, promotion, and interrupted recovery.

• REG-19 — Make retries safe. Cover duplicate preparation/finalization, competing registration requests, and retries after canonical commit but before SQLite commit. Reuse the intended identity and avoid duplicate bootstrap records or false success metadata.

• REG-20 — Verify semantic equivalence. Test new and historical identities, approval seeds, transitive approvals, invitation redemption, hidden bootstrap records, usernames, profile counts, and activity authorship. Verify registration against the supported retraction behavior, tracking the intended simplification separately rather than making old immediate-suppression behavior the permanent oracle. Compare with canonical expectations and a full rebuild; resolve discrepancies rather than assuming either implementation is correct.

• REG-21 — Verify concurrency and failure recovery. Exercise competing writers, readers during registration, failures around each commit boundary, and repository changes during candidate building/promotion. Check that locks release and recovery is observable.

• REG-22 — Verify scaling and reader availability. Grow unrelated identities, posts, and activity independently. Check operation counts and query plans as well as elapsed time. Under controlled registration traffic, verify / and /users/ avoid registration-induced busy responses within the supported workload.

• REG-23 — Verify migration and delivery. Test previous-schema startup, CLI/web configuration agreement, candidate promotion, and interrupted maintenance. Document how to deploy the schema change without surprising visitors with a large automatic rebuild. Record supported code/schema compatibility and recovery steps.

• REG-24 — Record completion evidence. Save benchmark comparisons, correctness/concurrency results, remaining limitations, and the dependency delta. No new third-party dependency is currently proposed. Close each item only against its evidence.

The following availability work is a separate design decision, not a prerequisite for the index and targeted-update changes:

• REG-F01 — Decide whether ordinary reads may use the last committed model during a routine write. Specify acceptable presentation staleness and current authorization/privacy requirements. Incorporate the user's acceptance of delayed derived-copy unavailability after retraction; do not assume continuous retraction freshness remains required. Define the replacement contract before changing guards.

• REG-F02 — Implement and verify that policy if adopted. Test reads across canonical/SQLite commit boundaries, concurrent approvals, the agreed retraction/deletion contract, and maintenance failures. Increasing lock timeouts or enabling WAL alone does not resolve the application-level wait.

Repository requirements: Contributing, read-model schema specification. Keep future implementation evidence alongside this checklist or link the approved implementation artifacts here.

Baseline acquisition progress, 2026-10-24: the full /downloads/read_model.sqlite3 download passed PRAGMA quick_check and contains 18,760 posts, 3,845 profiles, and 18,863 activity rows, at recorded canonical head f13b31c925715d0f3fcf06dfebcd9a48ab04714f. It is preserved under ignored state/benchmarks/qdb-20261024/, with URL, SHA-256, metadata, counts, and HTTP headers. Both repository-archive requests (including a retry pinned to that head) returned HTTP 503. The matching canonical archive and registration measurements remain outstanding; REG-01 is not complete. The filtered offline snapshot is not a substitute for this full database.

2026-10-24 REG-01 completion supersedes the earlier acquisition blocker: the user supplied repository.tar.gz.1, and its history yielded the exact database revision. Archive inventory and all baseline results are recorded in the linked evidence. REG-02 is next.