Databases
Database Enum Cleanup for Closed Workflow States
Database enum cleanup starts when product states, workflow labels, or status values stop being created but still exist in schemas, APIs, and historical rows. Removing an enum value too early can block writes, break deserialization, or erase the meaning of old records.
For stale enum values from retired workflow states, cleanup should start by proving that no supported writer can create the value, old rows still have a readable meaning, and API clients will not fail on the revised contract. The useful output is an enum cleanup migration with writer proof, row counts, client contract diff, value mapping, and rollback SQL: stop new writes before transforming stored values, then keep the historical mapping visible for support and reporting.
Key takeaways
- Review stale enum values from retired workflow states through Writer state, Stored data, Client contract, not age alone.
- Use one client release cycle plus the longest reporting and historical-support window before deciding that quiet means unused.
- Start with the reversible move: stop new writes before transforming or removing stored enum values.
- Slow down when blocking writes or erasing the meaning of historical workflow records is still plausible.
- Prevent repeat cleanup by making teams create enum values with owner, product meaning, allowed writers, and retirement trigger.
Map Product States
Start with one enum or status family across database types, application constants, API schemas, exports, historical rows, and reporting logic. The best cleanup scope is small enough that owners can answer quickly but wide enough to include the attachments that make removal risky.
| Field | Why it matters |
|---|---|
| Owner | Cleanup needs a person or team that can accept the decision |
| Current purpose | A short reason to keep the item, written in present tense |
| Last meaningful use | read/write activity, size, query plans, job dependencies, and retention rules |
| Dependency evidence | database metrics, query logs, application references, and reporting schedules |
| Risk if wrong | The outage, data loss, access failure, or rollback gap the review must avoid |
| Next action | Keep, reduce, archive, disable, remove, or investigate |
Do not make the inventory larger than the decision. A short list with owners and evidence beats a perfect spreadsheet that nobody is willing to act on.
Enum Evidence to Collect
The useful question is not “how old is it?” It is “what would break, become harder to recover, or lose accountability if this disappeared?” For database enum cleanup for closed workflow states, collect enough evidence to answer that without relying on naming conventions.
| Check | What to look for | Cleanup signal |
|---|---|---|
| Writer state | application constants, validation rules, migrations, import jobs, and old clients | No supported writer can create the value |
| Stored data | row counts by value, archival needs, support tools, and historical reports | Existing records can keep meaning or be migrated safely |
| Client contract | OpenAPI or GraphQL enums, SDKs, mobile versions, and partner docs | Consumers no longer expect the value |
| Migration safety | type alteration plan, fallback value, rollback SQL, and deploy order | Schema and code can change without incompatible deploy windows |
Use several signals together. Activity can miss monthly jobs and incident-only paths. Ownership can be stale. Cost can distract from security or recovery risk. The strongest case combines runtime data, dependency checks, owner review, and a rollback plan.
If the evidence conflicts, label the item “investigate” with a named owner and review date. That is still progress because the next review starts with a narrower question.
Example Enum Review
Count stored values and search application contracts before changing enum definitions.
SELECT status, count(*) AS rows, max(updated_at) AS last_seen
FROM orders
GROUP BY status
ORDER BY rows DESC;
SELECT enumlabel
FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
WHERE t.typname = 'order_status';Treat the output as a candidate list. Do not pipe these checks into delete commands; add owner review, dependency checks, and a rollback path first.
Stop Writes Before Schema Changes
Use the least permanent move that proves the decision. In database enum cleanup for closed workflow states, removal is only one possible outcome; reducing size, narrowing permission, shortening retention, archiving, or disabling a trigger may produce the same benefit with less risk.
- Stop new writes before transforming or removing stored enum values.
- Update API schemas and SDKs before dropping compatibility handling.
- Keep rollback SQL and historical mapping notes with the migration.
Track the cleanup candidate with a simple priority score:
| Score | Good sign | Bad sign |
|---|---|---|
| Impact | Meaningful spend, risk, toil, noise, or confusion disappears | The item is cheap and low-risk but politically distracting |
| Confidence | Owner, purpose, and dependency path are understood | The team is guessing from age or name |
| Reversibility | Restore, recreate, re-enable, or rollback path exists | Deletion would be the first real test |
| Prevention | A rule can stop recurrence | The same pattern will return next month |
Start with high-impact, high-confidence, reversible candidates. Defer confusing items only if they get an owner and a date; otherwise “defer” becomes another word for keeping waste permanently.
Values That Still Explain History
Some cleanup candidates are supposed to look quiet. Do not rush these cases:
- Order, payment, entitlement, support, and audit statuses.
- Mobile or partner clients that parse enum values strictly.
- Historical rows whose old value explains a valid business state.
For these cases, use a longer observation window, explicit owner approval, and a staged reduction. The point is not to avoid cleanup; it is to avoid making the first proof of dependency an outage.
Run the Enum Migration
Run database enum cleanup for closed workflow states as a decision review, not an open-ended hygiene project.
- Pick the narrow scope and export the candidate list.
- Add owner, current purpose, last-use evidence, dependency checks, and risk if wrong.
- Remove obvious false positives, then ask owners to choose keep, reduce, archive, disable, remove, or investigate.
- Apply the least permanent useful change first.
- Watch the signals that would reveal a bad decision.
- Complete the final removal only after the review window closes.
- Save an enum cleanup migration with writer proof, row counts, client contract diff, value mapping, and rollback SQL.
For broader cleanup planning, use the cleanup library to pair this guide with related notes. If the cleanup has infrastructure impact, pair it with a visible owner, a rollback path, and a measurable business case. For infrastructure cleanup, the main cloud cost optimization checklist is a useful companion.
Give States Exit Criteria
Prevention should change the creation path, not just the cleanup path. For database enum cleanup for closed workflow states, the useful prevention fields are data owner, retention policy, recreate path, and review date. Make those fields part of normal creation and review.
- Create enum values with owner, product meaning, allowed writers, and retirement trigger.
- Treat status migrations as API changes, not only database cleanup.
- Review enum values when product workflows merge, rename, or retire states.
The recurring review should be short: sort by impact, pick the unclear items, assign owners, and close the loop on anything nobody claims. If the review keeps producing the same class of candidate, fix the creation path instead of celebrating repeated cleanup.
Example Decision Record
Use a compact record so the cleanup can be reviewed later without reconstructing the whole investigation.
| Field | Example entry for this cleanup |
|---|---|
| Candidate | Stale enum values from retired workflow states in application schemas, API contracts, exports, support tools, and historical reporting tables |
| Why it looked stale | Low recent activity, unclear owner, or no current consumer after the first review |
| Evidence checked | Writer state, Stored data, and owner confirmation |
| First reversible move | Stop new writes before transforming or removing stored enum values |
| Watch signal | The metric, alert, job, route, query, or owner complaint that would show the cleanup was wrong |
| Final action | Keep, reduce, archive, disable, or remove after one client release cycle plus the longest reporting and historical-support window |
| Prevention rule | Create enum values with owner, product meaning, allowed writers, and retirement trigger |
This record is intentionally small. If the decision needs a long narrative, the candidate is probably not ready for removal yet. Keep investigating until the owner, evidence, reversible move, and prevention rule are clear.
FAQ
How often should teams do database enum cleanup for closed workflow states?
Use one client release cycle plus the longest reporting and historical-support window for the first decision, then set a recurring cadence based on change rate. Fast-moving non-production systems may need monthly review; slower systems can be quarterly if every unclear item has an owner and a review date.
What is the safest first action?
The safest first action is usually ownership repair plus evidence collection. After that, stop new writes before transforming or removing stored enum values. That creates a visible test before permanent deletion.
What should not be removed quickly?
Do not rush anything connected to order, payment, entitlement, support, and audit statuses. Also slow down when the cleanup affects recovery, compliance, customer-specific behavior, rare schedules, or security response.
How do you make the decision useful later?
Write the decision as a small operational record: candidate, owner, evidence, chosen action, watch signals, rollback path, final date, and prevention rule. That format helps future engineers, search engines, and AI assistants understand the cleanup without guessing.