Aller au contenu principal

Database Cleanup Audit

This document is the active entry point for schema and data cleanup work.

It is intentionally short: the goal is to identify likely cleanup targets before any destructive migration or data purge.

Current Approach​

  1. Identify legacy tables no longer used by application code.
  2. Separate:
    • schema candidates to drop,
    • data candidates to purge,
    • active tables that only need retention rules.
  3. Require a usage check before deletion:
    • backend queries,
    • frontend/API exposure,
    • operational scripts,
    • migration history and rollback expectations.

Confirmed Schema/Code Drift​

These need cleanup or correction before any broad delete campaign.

Drift already cleaned​

  • Commercial Proposal product schema (proposal_*, user_settings.proposal_default_*, email_new-proposal, quote_revisions, quote ADDON lines, user_rates addon columns)
    • app code removed in Vague C
    • Liquibase 0277 done — drops tables/columns/seed and restores pre-Proposal quote_items / user_rates shape
    • kept: session user_rate_id (unrelated session↔rate link)
    • R2 object GC for proposal-covers/ / proposal-defaults/ — done (2026-09-06, backend/scripts/gc-proposal-r2-objects.ts, 10 objects / 2.67 MB removed from aaperture-dev; re-run against staging/prod buckets separately if needed)
  • Legacy workflow v1 tables (user_workflows, default_workflows, session_workflow_tasks, …)
    • already dropped in Liquibase 0173_workflow_v2_rework (not a future drop candidate)
    • Kysely types / runtime fallbacks removed earlier
  • session_reminders
    • stale runtime reference removed; table not present live (already gone)

This means further schema cleanup should:

  1. verify 0277 / 0278 on staging,
  2. prefer retention policies over more table drops unless inventory proves dead schema.

Initial Candidate Tables​

These are the first tables to review because they look legacy or replaced by newer flows.

High-confidence review candidates (historical — renamed)​

  • projects / project_tags / project_contacts / project_vendors / project_files / contact_projects
    • renamed to sessions* in Liquibase 0045_rename_projects_to_sessions — not drop candidates under the old names

Review carefully before touching​

  • client_access_tokens
    • legacy /client UX was hard-cut, but the table still appears in active backend flows (client-access services, contracts/billing link generation, contacts merge)
  • migration_logs
    • active operational role in migration observability and sync with Liquibase
  • duplicates
  • user_duplicates
    • still active in backend, frontend, OpenAPI, navigation, and E2E
  • user_reminder_settings
    • still used by billing reminder services
  • session_workflow_action_runs
    • part of the workflow v2 runtime schema even if direct query usage is limited

Progression family — dropped (0275)​

App code deleted in Vague A; Liquibase 0275_drop_progression_tables drops:

  • user_progression, badges, user_badges, challenges, xp_transactions
  • aaperture_coins_transactions, user_aaperture_coins, leaderboard_cache, progression_settings

SMS hard-delete — dropped (0279-0281, Vague E, 2026-09-06)​

App code deleted entirely (not hidden); Liquibase drops:

  • user_sms_templates (0279) — only held system default templates for 2 dev accounts, never referenced by any workflow action
  • SEND_SMS removed from workflow_task_actions.action_type CHECK constraint (0280) — 0 rows used it
  • client_preferences.notification_sms (0281) — declarative preference, never wired to a real send, 0 rows had it set to true

Vague D orphan tables — dropped (0278)​

Confirmed unused at runtime (local inventory 2026-09-05); Liquibase 0278 + Kysely types removed:

  • professional_type_profiles (seed-only rows; catalog professional_types kept)
  • organization_settings (empty; do not confuse with Insights insight_settings)
  • gdpr_processing_activities (empty; other gdpr_* tables kept)

session_reminders not present live (already gone).

Also already dropped in 0173 (do not re-plan):

  • user_workflows / user_workflow_phases / user_workflow_tasks
  • default_workflows / default_workflow_phases / default_workflow_tasks
  • session_workflow_tasks

Data Cleanup Candidates​

These are likely to need retention or purge rules rather than immediate table deletion.

  • expired tokens in auth-related tables
  • old webhook event logs
  • old scheduled email execution artifacts
  • worker/runtime tables with historical rows:
    • job_runs
    • file_objects
    • export_jobs
    • ai_tasks
  • outbox_events — dropped Liquibase 0276 (Nest outbox module already gone)
  • old notification/event tracking rows:
    • email_tracking
    • audit_logs
    • migration_logs
  1. Apply / verify 0277 + 0278 on staging before prod.
  2. R2 GC for Proposal object prefixes — done on aaperture-dev; re-run npm run gc:proposal-r2 -- --delete (with the right R2 credentials in env) against staging/prod if they also carry orphaned objects.
  3. Retention/purge policies for worker history tables (job_runs, export_jobs, ai_tasks, …).
  4. Validate backups and rollback before dropping anything further in production.

Non-Goals​

  • No deletion based only on documentation.
  • No dropping tables that still appear in code, OpenAPI, or operational workflows.
  • No data purge without a retention decision and a production backup path.

Audit Command​

Run the inventory audit before any destructive migration:

psql "$DATABASE_URL" -f infra/db-cleanup-audit.sql

Expected outputs:

  • candidate table presence in public
  • approximate live row counts
  • total relation size
  • insert/update/delete counters from pg_stat_user_tables
  • max created_at / updated_at when those columns exist