Use SQLite for local development and ordinary CI

Current status

The SQLite application boundary was implemented, independently tested, accepted, merged, and
deployed. The accepted implementation commit is 3c6b97efd41081338d76903f43eb0d463a75c399,
the merge is 3f6c227b51bee8eabb09a89955603193574d7090, and current main
58932b410bf54f42e9eacafa0bf2b8fed02a4860 is a descendant.

The original exact-SHA on-call report
proved ordinary SQLite CI, the exact-image RDS migration, database-aware readiness, deployed smoke,
release receipts, and rollback safety. Current-main run 31444623581
independently re-proved the boundary: terminal-green quality, full-profile SQLite Django, core
Playwright, container, exact-image RDS migration, readiness, smoke, and rollback-eligible delivery.

This issue remains open because a later specification merge reintroduced three mandates that
conflict with the owner's fixed SQLite/portable-application decision:

• specification 09's foundation milestone
  again says generic PostgreSQL instead of project-local SQLite for local/ordinary CI plus the
  bounded deployed-RDS boundary;
• specification 09's search milestone
  again mandates a PostgreSQL search projection; and
• specification 10's database integration layer
  again prescribes maintained PostgreSQL constraint/concurrency application testing.

The current make database-portability-check remained green because it checks ordinary workflow
jobs and maintained Django Python roots, not normative specifications. This re-grooming repairs
that blind spot without reopening the accepted application rewrite.

One separately bounded HUMAN operation also remains: inventory already-applied legacy PostgreSQL
objects through read-only catalog queries and make an explicit leave/remove decision in a separately
reviewed maintenance issue before production-account migration. This issue authorizes no DDL or
destructive cleanup.

Product decision

SQLite is the deterministic database for local development and ordinary application CI. Maintained
application models, migrations, services, and tests use backend-portable Django contracts. Do not
introduce PostgreSQL-only fields, extensions, indexes, triggers, advisory locks, raw SQL, vendor
branches, or application-test suites.

Deployed development and eventual production may continue to use RDS PostgreSQL as shared durable
storage. That engine is validated only by the exact tested image's deployment migration,
database-aware readiness, and deployed smoke. The runtime psycopg dependency and fail-closed
deployed settings remain required.

The owner's decision is recorded in
issue comment 5228163244.

Accepted issue #104 supersedes only the old
assumption that every per-change Django job must run the unconditional full suite. Main-push CI now
runs the exact reviewed focused SQLite closure only for a safely owned single application and falls
back to the full SQLite suite for shared, cross-application, migration, configuration, workflow,
template/static, unknown, or unsafe changes. A separate four-hour workflow supplies complete
regression coverage. Neither path may provision or connect to PostgreSQL for application testing.

Normative references

• _docs/specs/README.md
• _docs/specs/01-platform-architecture.md
• _docs/specs/08-aws-development-terraform.md
• _docs/specs/09-migration-rollout-roadmap.md
• _docs/specs/10-verification-strategy.md
• _docs/specs/open-decisions.md
• _docs/architecture/database-portability.md
• _docs/PROCESS.md
• Issue #104: selective CI and four-hour full regression

Except for the exact contradictory statements identified above, these specifications remain
authoritative. In particular, RDS PostgreSQL remains the deployed database described by the AWS
specification, and deployed settings continue to fail closed on invalid database configuration.

Scope

Normative specification repair

• Rewrite specification 09's foundation deliverable to state project-local SQLite for local
  development and ordinary CI, portable Django application contracts, and RDS PostgreSQL only at
  the bounded deployed migration/readiness/smoke boundary.
• Replace specification 09's PostgreSQL search projection with the existing backend-portable search
  projection contract. Search ranking, indexing, and public FAQ/Wiki behavior remain owned by the
  content/search issue.
• Remove specification 10's maintained PostgreSQL constraint/concurrency application-test mandate.
  Portable constraints, transactions, optimistic concurrency, idempotency, and service invariants
  are exercised on SQLite; real RDS validation remains deployment-only.
• Align current CI wording with #104: safe one-application changes may run the reviewed focused
  SQLite closure, unsafe/shared changes run full SQLite, and the scheduled full regression remains
  PostgreSQL-free.

Regression prevention

• Extend the code-owned database-portability check to inspect the normative specification set, not
  only workflows and Python application roots.
• The check must reject language that requires PostgreSQL-specific application behavior, search,
  constraints/concurrency, migrations, or application tests while permitting accurate references
  to deployed RDS storage, fail-closed deployed settings, the runtime driver, deployment
  migration/readiness/smoke, explicit prohibitions, and the historical-object follow-up.
• Add deterministic positive and negative fixtures/tests so the three exact regressions above fail
  and legitimate deployed-boundary wording passes.

Legacy-object HUMAN boundary

• An authorized operator uses a read-only database identity against the development RDS database.
• The operator runs only the bounded catalog query below inside a read-only transaction with short
  lock and statement timeouts.
• Evidence may contain only the known public object kind, schema, object name, bound relation,
  function name, count, and an optional digest. It must not contain DATABASE_URL, credentials,
  endpoint/connection material, table rows, function/trigger definitions, raw SQL errors, or other
  database/provider payloads.
• Zero returned objects resolves the decision as nothing to clean up. Any returned object requires
  a separately groomed, backup-verified maintenance issue to decide leave or remove. That issue
  owns any later DDL; #98 never does.

BEGIN TRANSACTION READ ONLY;

SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5s';

WITH legacy_functions(name) AS (
    VALUES
        ('dtc_core_audit_append_only'),
        ('dtc_core_setting_revision_append_only'),
        ('dtc_content_active_state_guard'),
        ('dtc_content_source_guard'),
        ('dtc_content_release_guard'),
        ('dtc_content_child_guard'),
        ('dtc_management_principal_identity_immutable'),
        ('dtc_management_credential_authority_immutable')
),
legacy_objects AS (
    SELECT
        'function'::text AS object_kind,
        namespace.nspname AS schema_name,
        procedure.proname AS object_name,
        ''::text AS relation_name,
        procedure.proname AS function_name
    FROM pg_proc AS procedure
    JOIN pg_namespace AS namespace
        ON namespace.oid = procedure.pronamespace
    JOIN legacy_functions
        ON legacy_functions.name = procedure.proname

    UNION ALL

    SELECT
        'trigger',
        namespace.nspname,
        trigger.tgname,
        format('%I.%I', namespace.nspname, relation.relname),
        procedure.proname
    FROM pg_trigger AS trigger
    JOIN pg_class AS relation
        ON relation.oid = trigger.tgrelid
    JOIN pg_namespace AS namespace
        ON namespace.oid = relation.relnamespace
    JOIN pg_proc AS procedure
        ON procedure.oid = trigger.tgfoid
    JOIN legacy_functions
        ON legacy_functions.name = procedure.proname
    WHERE NOT trigger.tgisinternal

    UNION ALL

    SELECT
        'index',
        schemaname,
        indexname,
        format('%I.%I', schemaname, tablename),
        ''
    FROM pg_indexes
    WHERE indexname = 'jobs_unique_code_schedule_name'
)
SELECT *
FROM legacy_objects
ORDER BY object_kind, schema_name, object_name, relation_name;

ROLLBACK;

Non-goals

• Reimplementing or weakening the accepted SQLite settings, service, migration, CI, or deployment
  work already on main.
• Replacing RDS PostgreSQL in deployed development or production topology.
• Changing AWS networking, RDS sizing/backups, Terraform state, credentials, deployment authority,
  or the #96/#102 exact-release and compensation contracts.
• Reverting #104's reviewed selective-CI mapping, scheduled full-regression contract, aggregate
  gate, or automatic deployment behavior.
• Implementing search ranking/indexing or changing public FAQ/Wiki contracts.
• Adding a PostgreSQL application-test matrix, scheduled PostgreSQL suite, second database engine,
  Redis, external search service, or dependency.
• Reading application rows, production data, secrets, database URLs, credentials, provider
  payloads, or function definitions during the HUMAN inventory.
• Executing DROP, ALTER, CREATE, REINDEX, VACUUM, ANALYZE, migration, restore, or any
  other DDL/data mutation as part of #98.
• Editing, reformatting, compacting, minifying, or generating HTML templates. This issue has no
  visitor-facing UI scope.

Dependencies and coordination

• #96 and #102 are satisfied deployment-controller baselines. Their exact-image migration,
  readiness, smoke, receipt, rollback, and compensation behavior must remain unchanged.
• #104 is a satisfied CI-orchestration baseline and explicitly supersedes the unconditional
  full-suite-per-push wording. It must not be reverted.
• The specification/gate repair is unblocked at current main
  58932b410bf54f42e9eacafa0bf2b8fed02a4860.
• The legacy-object HUMAN inventory is a future pre-production-account-migration operation. It does
  not authorize database access during engineering/tester work and does not block ordinary
  development releases.
• No browser or public-page dependency exists.

Acceptance criteria

Previously satisfied implementation and deployment

• Local development and ordinary application CI use isolated SQLite without a PostgreSQL
  service or ambient DATABASE_URL; deployed settings retain fail-closed PostgreSQL/RDS behavior.
• Maintained application code, models, migrations, services, and ordinary tests use portable
  Django contracts; psycopg remains only for deployed runtime connectivity.
• [HUMAN] Exact-SHA on-call observed the accepted #98 merge through ordinary SQLite CI, exact
  tested-image RDS migration, database-aware readiness, deployed smoke, receipts, rollback safety,
  and terminal green release. The original report and current-main run are linked above.

Current specification and regression repair

• Specification 09 says project-local SQLite for local development and ordinary CI, portable
  Django application contracts, and RDS PostgreSQL only for deployed storage and bounded
  migration/readiness/smoke.
• Specification 09 requires a backend-portable search projection and contains no PostgreSQL
  search-engine mandate; public FAQ/Wiki contracts remain unchanged.
• Specification 10 contains no maintained PostgreSQL-only constraint, concurrency, migration,
  or application-test mandate and keeps real-engine validation only at the deployment boundary.
• Current specifications and CI guidance record #104's exact supersession: safe one-application
  changes use reviewed focused SQLite closures, unsafe/shared changes use the full SQLite suite,
  and the four-hour full regression uses SQLite without PostgreSQL services/connections.
• The code-owned database-portability gate scans normative specifications and has deterministic
  positive/negative tests proving the three regressions fail while legitimate deployed-RDS,
  runtime-driver, prohibition, and historical-follow-up wording passes.
• The repository search/evidence accounts for every maintained normative PostgreSQL reference
  as deployed configuration/storage, runtime driver, explicit prohibition, or the bounded legacy
  follow-up—not required application behavior or application testing.
• The current AWS normative link is
  _docs/specs/08-aws-development-terraform.md; no maintained #98 link targets the removed
  08-aws-sandbox-terraform.md path.
• uv-backed portability, focused regression, quality, migration-drift, Django system/deploy,
  CI-contract, and applicable full SQLite regression gates pass. The changed-path inventory
  contains no HTML/template file.

Remaining HUMAN operation

• [HUMAN] An authorized read-only development-RDS catalog inventory runs the exact bounded
  query above, publishes only permitted redacted evidence, and records nothing to clean up or
  links a separately groomed, backup-verified maintenance issue containing the explicit
  leave/remove decision. No DDL or data mutation occurs under #98.

Verification scenarios

1. Normative negative fixtures: each exact reintroduced phrase—generic PostgreSQL foundation,
   PostgreSQL search projection, and PostgreSQL constraints/concurrency application tests—fails
   the portability gate with a stable diagnostic.
2. Normative positive fixtures: SQLite local/ordinary CI, portable application contracts,
   deployed RDS storage, fail-closed settings, runtime psycopg, deployment migration/readiness/
   smoke, explicit prohibitions, and the historical-object follow-up all pass.
3. #104 preservation: workflow/configuration tests prove focused and full main-push profiles plus
   the scheduled full regression remain SQLite-only and preserve their reviewed selection and
   aggregate-gate behavior.
4. Repository regression: run the uv-backed portability/quality/CI-contract and applicable full
   SQLite gates; there is no template/HTML diff and therefore no changed-page screenshot gate.
5. HUMAN zero-object case: the exact read-only catalog query returns zero rows; record only the
   zero count and nothing to clean up decision.
6. HUMAN present-object case: the query returns one or more allowlisted objects; record only the
   permitted catalog fields/count/digest, open and groom the backup-verified maintenance decision,
   and perform no DDL under #98.

The implementation candidate must remain uncommitted until independent tester and PM acceptance.
Use Refs #98, not Closes #98, while the final HUMAN operation remains unchecked.

Read _docs/specs/09-migration-rollout-roadmap.md, _docs/specs/10-verification-strategy.md, the other normative references, and the implementation behind make database-portability-check. Add deterministic positive and negative coverage for the three PostgreSQL regressions while preserving permitted deployed-RDS wording. Done means the corrected specifications and portability check pass, including the current SQLite and CI contracts.