> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Destructive DROP Audit — April 2026

> Scope: All 892 files in supabase/migrations/, filtered to those containing DROP TABLE or DROP COLUMN statements outside SQL comments.

**Status:** Findings only. No rewrites recommended without per-migration spec verification.

**Scope:** All 892 files in `supabase/migrations/`, filtered to those containing `DROP TABLE` or `DROP COLUMN` statements outside SQL comments.

**Driver:** `.cursor/BUGBOT.md` rule — "Dropping columns or tables: NOT OK. Prefer deprecation or a spec-approved multi-phase migration."

## Findings

10 migrations contain destructive DDL outside comments. Triage:

| Migration                                                             | Statement                                                                                                        | Verdict                                           | Notes                                                                         |
| --------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | ------------------------------------------------- | ----------------------------------------------------------------------------- |
| `20260218172403_*.sql:64-68`                                          | `ALTER TABLE cl_pharmacies DROP COLUMN address_line_1, address_line_2, city, state, zip_code` (with `IF EXISTS`) | Intentional consolidation                         | Address moved to a normalized location pattern. Verify spec link.             |
| `20260303234525_*.sql:3`                                              | `ALTER TABLE cl_patient_charts DROP COLUMN chart_summary_metadata`                                               | Comment-only (rollback note)                      | OK                                                                            |
| `20260311000823_*.sql:27`                                             | `ALTER TABLE pf_oauth_state DROP COLUMN updated_at` (NO `IF EXISTS`)                                             | **Risky** — fails replay if column already absent | Add `IF EXISTS` guard if this migration is ever revisited or used as template |
| `20260319182644_*.sql:24`                                             | `DROP TABLE IF EXISTS pf_user_roles`                                                                             | Intentional V1→V2 permissions cleanup             | Confirmed by surrounding context (PF-30)                                      |
| `20260319183800_*.sql:6`                                              | `DROP TABLE IF EXISTS pf_index_cleanup_audit`                                                                    | Intentional audit-table cleanup                   | OK                                                                            |
| `20260326034722_*.sql:12-18`                                          | `DROP TABLE IF EXISTS hr_policies, hr_policy_acknowledgments, hr_policy_distributions CASCADE` + 2 column drops  | Intentional HR redesign                           | Verify spec link to HR module redesign                                        |
| `20260331143000_gr_findings_rpc_regulatory_rls.sql:232`               | `DROP COLUMN IF EXISTS regulatory_bodies`                                                                        | Intentional GR schema change                      | OK                                                                            |
| `20260401173717_*.sql:9`                                              | `DROP TABLE IF EXISTS survey_responses`                                                                          | Intentional legacy table cleanup                  | Verify no consumers                                                           |
| `20260417211339_fa_coa_templates.sql:20`                              | `DROP TABLE` only in rollback comment                                                                            | Comment-only                                      | OK                                                                            |
| `20260417203241_fa_template_applications_and_account_audit.sql:24-27` | `DROP TABLE` / `DROP COLUMN` only in rollback comment                                                            | Comment-only                                      | OK                                                                            |

## Recommendations

1. **No rewrites required from this audit.** Most destructive statements are intentional schema-redesign migrations (V1→V2 permissions, HR redesign, GR field deprecation). Per `.cursor/BUGBOT.md`, these are acceptable when documented; absence of explicit spec links should be remedied by adding header comments referencing the originating ticket/spec.

2. **`20260311000823` lacks `IF EXISTS` on its DROP COLUMN.** This breaks replay-from-empty-DB if for any reason the column is absent. Low priority because the migration is \~6 months old and presumably already applied everywhere it needs to go, but flag for any future "rebuild from migrations" exercise.

3. **Going forward**, the `.cursor/BUGBOT.md` rule should be enforced by the BUGBOT pre-merge check rather than relying on review discipline. P1 of the broader hardening plan adds a pre-commit guard for migration filenames; consider adding a `lint-migrations.ts` extension that fails on `DROP (TABLE|COLUMN)` without an `IF EXISTS` guard and without a `-- spec: …` header comment.

## Out of scope

* The 150 other migrations containing `DROP` (mostly `DROP POLICY`, `DROP TRIGGER`, `DROP FUNCTION`, `DROP INDEX`) — those are routine and not destructive in the BUGBOT sense.
* Post-squash drops embedded in `20260211182655_*.sql` (the squash itself) — already deployed; not separately classified.