Load production-shaped CMP content safely into local SQLite

Outcome

A developer can build a realistic, content-rich local review database from an existing converted CMP production snapshot without copying any production identity, learner activity, communication data, or user-supplied free text into the repository or the review site. The review database uses SQLite, remains local and gitignored, can be rebuilt deterministically, and is safe to browse and screenshot.

The 2026-08-08 one-off run (20 courses, 119 homework rows, 604 questions, 48 projects, 169 review criteria, two campaigns, aggregate statistics, and zero production users/registrations/submissions/messages) is useful discovery evidence only. It does not satisfy this issue until the repeatable controls below exist and pass independently.

Product and architecture authority

• 01 — retryable/idempotent imports and application boundaries
• 04 — adopted course behavior and migration ownership
• 07 — protected data, redaction, backup, and restored-data controls
• 09 — snapshot-pinned, repeatable, reconciled imports
• 10 — isolated test data and artifact safety
• Course-platform adoption provenance
• Deferred migration-squash gate
• #98 records the later owner decision that local development and ordinary CI are SQLite-only. This issue follows that decision now; it does not require PostgreSQL or PostgreSQL-specific behavior.
• #100 records that the adopted CMP account is the single website identity. This issue imports no production account identity and may create only the explicit synthetic local review administrator described below.

Scope

Safe source handling

• Extend or replace the current broad-copy behavior in scripts/load_rds_export.py with one documented uv-backed review-data workflow. It must require an explicit source snapshot path; do not search /tmp, a home directory, or another broad location for “latest”.
• Open the original source read-only. Record its opaque snapshot identifier, byte size, and SHA-256 before work; verify size and SHA-256 are unchanged afterward. Never run migrations, deletes, updates, vacuum, attach/write operations, or admin creation against it.
• The original protected backup stays in its existing secure location. Do not copy an unsanitized database into the repository, even transiently.
• Build the content-only sanitized artifact from a fresh migrated SQLite schema by selecting only the allowlisted tables and columns below. Intermediate, sanitized, report, and rollback files live under gitignored repo-local .tmp/ paths with private directory/file permissions. A target local SQLite database may use the project's documented gitignored local database path.
• Refuse when source, sanitized, work, and target paths alias one another; when the target is not SQLite; when a resolved temporary path escapes the repository .tmp/ boundary; or when a target path is not gitignored.
• The tool is local-only and fails closed under deployed development/production settings or against a non-SQLite target.

Exact content allowlist

The implementation keeps a versioned, directly reviewable table-and-column manifest. Source tables or columns absent from that manifest are never copied. A newly discovered table/column or a missing required relationship fails closed with a schema-only diagnostic.

• courses_course: id, slug, title, description, start_date, end_date, registration_url, github_repo_url, social_media_hashtag, first_homework_scored, finished, faq_document_url, min_projects_to_pass, homework_problems_comments_field, project_passing_score, visible.
• courses_registrationcampaign: id, slug, title, edition_label, current_course_id, is_active, marketing_markdown, meta_description, hero_image_url, video_url, created_at, updated_at. This is public campaign presentation/configuration only; courses_courseregistration is forbidden.
• courses_homework: id, slug, course_id, title, description, instructions_url, due_date, learning_in_public_cap, homework_url_field, time_spent_lectures_field, time_spent_homework_field, faq_contribution_field, state.
• courses_question: id, homework_id, text, question_type, answer_type, possible_answers, correct_answer, scores_for_correct_answer.
• courses_homeworkstatistics: id, homework_id, total_submissions, last_calculated, and the exact numeric min_*, max_*, avg_*, median_*, q1_*, and q3_* fields currently declared by HomeworkStatistics. No JSON, labels, IDs other than the row/homework keys, or user-level source is allowed.
• courses_project: id, course_id, slug, title, description, instructions_url, submission_due_date, learning_in_public_cap_project, peer_review_due_date, time_spent_project_field, problems_comments_field, faq_contribution_field, learning_in_public_cap_review, number_of_peers_to_evaluate, points_for_peer_review, time_spent_evaluation_field, state.
• courses_reviewcriteria: id, course_id, description, options, review_criteria_type. options must pass the adopted rubric validator and contain only the existing criterion/score structure.
• courses_projectstatistics: id, project_id, total_submissions, last_calculated, and the exact numeric min_*, max_*, avg_*, median_*, q1_*, and q3_* fields currently declared by ProjectStatistics. No JSON, labels, IDs other than the row/project keys, or user-level source is allowed.
• courses_wrappedstatistics: id, year, is_visible, total_participants, total_enrollments, total_hours, total_certificates, total_points, course_stats, calculated_at, created_at. course_stats is accepted only as a list of objects containing exactly public course title, public course slug, and numeric enrollment_count, with every slug resolving to an imported course. The source leaderboard column is never read/copied and the local value is always [].

The two statistics tables and the safe Wrapped totals are aggregate review context, not a source of learner records. Public course/homework/project instructions URLs are content; learner submission/project/FAQ/social links remain forbidden. Allowed URL fields must pass the adopted model validation and must not contain credentials or secret-bearing query values; a suspicious value fails closed and is not printed.

Denylist and validation

Everything not listed above is excluded. At minimum the sanitized artifact and final review database contain zero source rows from:

• adopted users, user group/permission links, staff/superuser records, password/profile/preferences, Allauth email/social/provider records, API tokens/principals, Django sessions, and Django admin logs;
• course registrations, enrollments, leaderboard complaints, homework submissions/answers, project submissions/commits/links/votes, peer reviews/notes, criteria responses, evaluation scores, certificates, and user Wrapped rows;
• Datamailer contacts/events, recipient/audience state, imports, outbox rows, dispatch runs, send audits, rendered bodies, provider responses, errors, and payloads;
• jobs/tasks/schedules/results, audit records, request metadata, webhooks, credentials, secrets, cookies, registration data, and any other source table not explicitly allowlisted.

Fresh local migration-owned metadata such as django_migrations, content types, and permissions is generated locally; it is never copied from the source.

Validation runs before an artifact or target is published:

1. exact source schema versus the committed allowlist;
2. allowed field/type/domain and JSON-shape validation without echoing values;
3. foreign-key and model checks for every imported content relationship;
4. explicit zero-row assertions for every denylisted table present in the migrated local schema;
5. a file/report/log scan against seeded PII/secret canaries in automated tests;
6. SQLite integrity/foreign-key checks, Django migration check, and Django system check;
7. safe per-table row counts plus a canonical logical checksum over sorted allowlisted values.

Any validation failure reports only snapshot ID, table/column name, category, and counts. It never prints row values, SQL containing values, source email/profile data, paths with credentials, tokens, provider payloads, or free text.

Dry run, apply, repeatability, and cleanup

• A documented dry run reads and validates the source, builds any trial database under a private .tmp/ work path, reports safe aggregate evidence, removes trial output, and leaves the source, current sanitized artifact, and current target byte-for-byte unchanged.
• Apply builds a fresh sanitized SQLite artifact and a fresh migrated target, validates both completely, then atomically publishes/replaces the local target. It never mutates an existing target in place.
• Running dry run twice or apply twice for the same source snapshot and code/schema version produces the same allowed row counts, relationship counts, and canonical logical checksum. SQLite file bytes, timestamps, and the synthetic admin password hash need not be identical.
• Failure before atomic publish leaves the previous target usable and unchanged, deletes partial/rebuilt artifacts, and leaves the original source untouched. No cleanup path accepts an unresolved variable, glob, repository root, home directory, or path outside the named .tmp/ subtree.
• After success, retain only the current sanitized artifact, current local review database, and current redacted report. A documented cleanup command removes a specifically named derived snapshot/report and, only with a separate explicit flag, the local target. It never deletes or changes the original backup.
• Outbound email, Datamailer/provider calls, scheduled jobs, and other network side effects are disabled throughout dry run, apply, validation, and browsing.

Local review bootstrap

• Provide one documented uv/Make entry point that builds the current local SQLite review database, prints only safe provenance/count/checksum evidence, and shows the exact local run command.
• The content sanitizer itself creates no account. The optional review bootstrap may create exactly one deterministic synthetic local superuser, review-admin@example.invalid, through the adopted accounts.CustomUser model after sanitization succeeds. Its password comes from a local-only explicit input/default, is never printed or stored in a report, and the bootstrap refuses to create it under deployed settings.
• Re-running bootstrap updates that one synthetic account rather than adding accounts. The final database has zero production-origin users, emails/social accounts, sessions, tokens, registrations, enrollments, submissions, reviews, or messages.
• The imported public course catalog, homework/questions, projects/criteria, dates, campaigns, and aggregate statistics remain related and browseable through the adopted CMP views/Studio-compatible read paths. No template is minified or mechanically compacted as part of this tooling issue.

Non-goals

• This is not the full production migration, Course → Cohort transformation, account consolidation, score/certificate reconciliation, write freeze/delta import, rollback rehearsal, or cutover owned by #51/#60/#100.
• It does not create a committed fixture, anonymized dataset for redistribution, CI artifact, Docker image layer, S3 object, deployment seed, shared-development seed, or any tracked database/report.
• It does not import GitHub-owned website/editorial content.
• It does not import, hash, pseudonymize, or preserve production user identifiers. Sensitive rows are excluded, not converted into synthetic learners.
• It does not alter the scheduled backup/export job, source retention, AWS, RDS, deployed data, email delivery, Studio permissions, account behavior, course business logic, models, migrations, templates, or public routes.
• Production snapshots are never used in ordinary CI or automated tests. Tests construct synthetic SQLite sources with canary data.
• No PostgreSQL service, PostgreSQL-specific SQL, field, index, trigger, advisory lock, or test branch is introduced.

Dependencies and coordination

• Depends only on closed #30 for the literally adopted CMP schema, migrations, models, and views.
• #97 may use the resulting ignored local database for human review, but remains independently testable from its checked-in read-only projection and does not depend on production content.
• #98 owns the repository-wide SQLite default/ordinary-CI cleanup. This issue uses an explicit local SQLite configuration and is not blocked by #98.
• #100 owns real account consolidation. This issue must keep production identities out and use only the adopted model for its optional synthetic local administrator.
• #60 remains the owner of full protected production-like migration/reconciliation. A content-only local review artifact from this issue is not migration-parity evidence and must not be used as #60's source.
• If #51 changes the accepted course schema first, the engineer must update the explicit safe allowlist and relationship checks to that accepted schema without broadening the privacy boundary.

Acceptance criteria

Source and privacy boundary

• The workflow requires one explicit source snapshot, opens it read-only, proves its size/SHA-256 unchanged, never creates an unsanitized repo-local copy, and never mutates the source.
• A committed exact table/column allowlist implements the list above; unknown source tables/columns, missing required columns, invalid JSON/URLs, and broken content relationships fail closed without printing values.
• The sanitized artifact and target contain all and only the allowed source content/aggregate fields; Wrapped leaderboard is [], and the safe course_stats schema/relations are validated.
• Every denylisted table is absent or has zero source rows, including users/email/social/session/token/staff, registration/enrollment/submission/review/free-text learner data, project submission links/commits, user Wrapped, jobs/audits, and all Datamailer/email/provider data.
• Automated canary tests prove production-like emails, names, social IDs/payloads, session/token/password values, free text, learner URLs, commits, rendered messages, provider payloads, and errors do not appear in the sanitized DB, target DB, stdout/stderr, report, or test artifacts.
• All derived files/reports are gitignored, private, repo-local where required, and absent from Git's tracked/staged diff.

Repeatability and failure behavior

• Dry run leaves source, current sanitized artifact, and target unchanged; two dry runs report identical safe counts/checksum.
• Two applies for the same snapshot and code/schema version report identical allowed counts, relationship counts, and canonical logical checksum and create at most the one optional synthetic review administrator.
• Failed schema, field, relationship, integrity, validation, or replacement cases preserve the prior target unchanged, remove partial outputs, preserve the source checksum, and perform no email/job/network side effect.
• Successful apply uses a fresh migrated SQLite schema plus atomic replacement, refreshes SQLite sequences safely, passes integrity/foreign-key/migration/system checks, and retains only the documented current private artifacts.
• Cleanup accepts only an exact derived snapshot target under the named .tmp/ subtree, is idempotent, requires a separate explicit flag for the local review DB, and cannot remove or modify the source backup or a broad directory.

Developer review and evidence

• One documented uv/Make bootstrap command and one documented local run command work with SQLite and no PostgreSQL service or PostgreSQL-specific feature.
• Bootstrap optionally creates/updates exactly one review-admin@example.invalid adopted account without printing its password; it imports zero production-origin identity, activity, registration, communication, or staff rows.
• The safe report contains only snapshot ID, source size/SHA-256, allowlist/schema version, imported table counts, relationship counts, denylist zero-count results, logical checksum, validation results, and repo-relative derived paths—never record values or sensitive absolute paths.
• The engineer and independent tester each run the synthetic canary suite plus focused loader tests with uv; the tester independently recomputes the manifest/checksum/count evidence.
• With an authorized private snapshot, the tester or authorized operator runs dry-run and apply locally, records only the approved safe aggregate evidence, and browses representative active and archived course, homework/question, project/criteria, campaign, and aggregate-statistics pages.
• Desktop and mobile screenshots show only public course/content fields and synthetic account data, contain no error/debug page or sensitive record, and are stored only under .tmp/screenshots/. No authenticated registrations/enrollments/submissions/reviews/email pages are opened or captured.
• The full Django compatibility suite, migration-drift check, Django system check, and core Playwright regression suite pass against synthetic/isolated databases; no test reads a normal local review DB or a production snapshot.

Verification scenarios

Synthetic safety and schema

1. Build a synthetic source with representative allowlisted rows and unique canaries in every forbidden identity/activity/email/free-text table and payload column. Dry-run and apply; verify all allowed relations and aggregate fields survive and every canary is absent from every output channel/artifact.
2. Add one unknown source table, one unknown column on an allowed table, a malformed rubric JSON value, a Wrapped course_stats entry with an extra/user-like key, a credential-bearing URL, and a broken FK in separate runs. Each run fails closed with schema-only diagnostics and leaves the prior target unchanged.
3. Seed Wrapped leaderboard entries and user Wrapped rows. The target has leaderboard=[], no user Wrapped rows, and no display name/student ID from either source.
4. Seed all account/session/token/social/registration/enrollment/submission/review/vote/complaint/Datamailer/job/audit tables. The target contains no source rows from any of them and no canary appears in output.
5. Interrupt before validation, during validation, and immediately before publish. Partial files are removed, target checksum is unchanged, source checksum is unchanged, and no side effect was attempted.

Repeatability and local bootstrap

1. Run dry-run twice and apply twice for one synthetic snapshot. Compare row/relationship counts and canonical logical checksum; confirm one synthetic admin at most and zero production-origin accounts.
2. Apply snapshot A, then make snapshot B fail validation. Snapshot A remains the active local review DB. Apply valid B and verify only the documented current artifacts remain.
3. Run cleanup twice for one exact derived snapshot. Then attempt traversal, symlink escape, glob, home/repository root, source path, and target deletion without the explicit target flag; every unsafe request is rejected.
4. Run bootstrap and the local server with PostgreSQL absent. Browse /courses/, at least one imported current course and one imported archived course, and representative homework/project/statistics paths without a database/network/email error.

Private authorized rehearsal

1. An authorized operator selects a named converted backup through the existing private workflow and runs the documented dry-run. The source hash remains unchanged; only safe counts/checksums and validation results are retained.
2. Apply to a fresh ignored local SQLite target, repeat apply, and reconcile public content row/relationship counts without inspecting or printing forbidden records.
3. Sign in only as the synthetic review administrator and inspect content/campaign management surfaces. Confirm registration, enrollment, submission, review, account, token, and communication lists are empty before taking public-content-only desktop/mobile screenshots.
4. Run a tracked/staged-file and artifact scan before handoff. No database, report, screenshot, secret, or production-derived value is committed, uploaded, logged in GitHub, or included in CI.

Delivery convention

Follow _docs/PROCESS.md: engineering works uncommitted, an independent tester verifies the privacy boundary and screenshots, product acceptance follows, then the approved focused commit is merged locally without a pull request.

Start with scripts/load_rds_export.py and read the linked architecture, security, migration, and verification specifications, especially the SQLite decision in #98. Define the documented uv-backed workflow around the stated allowlist, denylist, validation, dry-run, apply, cleanup, and bootstrap requirements. Done means repeatable safe counts and checksums, zero forbidden data, preserved source and target safety, and a browseable local review database.