Hardening 6: enforce database lifecycle, schema consistency, and integrity constraints

[Occumed79]

Parent: #170

Goal

Make the two-database architecture predictable, migration-safe, query-efficient, and recoverable.

Required lifecycle work

• Gracefully close all pools during Render/process shutdown signals.
• Split liveness from readiness; readiness must perform bounded queries against every required database.
• Document the exact purpose and selection rules for DATABASE_URL, DATABASE_URL_POOLED, and DATABASE_URL_2.
• Ensure connection-pool limits/timeouts are intentional and environment-appropriate.

Schema/migration controls

• Add migration version tracking and expose the expected/current schema version to diagnostics without exposing secrets.
• Add CI schema-drift detection.
• Reconcile source/upload/master relationships before deleting any tables.
• Add/verify foreign keys and uniqueness constraints for provider sources, upload runs/records, normalized identities, and master/source associations.
• Add/verify indexes for viewport bounds/geospatial access, source, provider type/category, country, city, normalized identity, upload ID, and commonly filtered status fields.
• Define transaction boundaries for bulk changes and rollback.
• Add orphan/dead-record detection and reconciliation scripts/tests.

Recovery

• Document backup/restore process for both databases.
• Add a non-production restore verification procedure or automated test using temporary/test databases.
• Ensure migrations are forward-safe and rollback/recovery implications are documented.

Acceptance

• Shutdown tests prove pools close cleanly.
• Readiness fails if either required DB cannot answer a minimal query.
• CI fails on unapplied migration/schema drift.
• Constraints prevent duplicate source/upload/master relationships that should be unique.
• Query plans for representative viewport/source/type/location queries use the intended indexes.
• Orphan detection reports zero unexpected records on test fixtures.
• No obsolete table is dropped in this PR unless its data has been reconciled and migration tests prove safety.