On May 28, 2026, Database Design: Replacing Soft Deletes by Separating States into Tables trended near the top of Zenn, sparking widespread discussion among enterprise SaaS developers. The article outlines how the general-purpose soft delete pattern using deleted_flag, is_deleted, or deleted_at creates inconsistencies, query complexity, index bloat, and an inability to maintain audit logs in enterprise SaaS by overloading "deletion, archiving, deactivation, expiration, and account cancellation" into a single flag. It proposes a phased migration toward an architecture that "isolates state into separate tables" and "maintains event logs." Around the same time, gihyo.jp featured Bringing "Time Travel Queries" to MySQL with Bintrail: Architecting Database Forensics Engagements for 2026, reflecting how "properly preserving data history" is becoming the mainstream of database design in 2026.
For custom development firms supporting enterprise SaaS, core systems, and administrative consoles for mid-market clients, this represents an opportunity to take "existing databases operated with soft deletes" and progressively redesign them for clients around "state tables plus event logs." Connecting with the multi-tenant SaaS fault tolerance covered in Cloudflare Dynamic Workflows Engagements, the database forensics in Bintrail MySQL Time Travel Engagements (GH Media), and the data governance in Monzo Data Mesh Engagements, we structure "database redesign to retire soft deletes" as a custom development package.
Why retiring soft deletes is a turning point
| Dimension | Soft delete (deleted_flag type) | State tables + event logs |
|---|---|---|
| Representable states | Binary (active / deleted) | Arbitrary (active / archived / suspended / withdrawn, etc.) |
| Query complexity | WHERE deleted_at IS NULL across all queries | JOIN on state tables |
| Index efficiency | Bloated by flag columns | Optimizable by individual state |
| Audit Logging | "When" and "by whom" remain unclear | Completely preserved in event logs |
| GDPR / account deletion compliance | Conflicts with hard deletion | Deletion policies defined per state |
| Business rules | Scattered across application code | Centralized in state transition tables |
| Data recovery | Just reverting a flag | Rewinding events |
| Testing | Explosion of boundary conditions across queries | Comprehensive coverage via state × event combinations |
| Understanding business logic | Impossible to understand without reading code | Self-documenting through schema |
In other words, "retiring soft deletes" is not a superficial DB refactoring trick, but a re-expression of the business domain that "makes data speak the business state."
Three structural changes beneficial to custom development projects
Structure 1: From "flag proliferation" to "state table normalization"
In enterprise SaaS for mid-market companies, there are numerous cases where flags multiply—such as is_deleted, is_archived, is_suspended, withdrawn_at, and expired_at—causing queries to collapse. In our custom development, we execute business state auditing → state transition diagrams → state table design over two to three months. This represents the OLTP-side design version of the data governance covered in our Monzo Data Mesh Client Services.
Structure 2: From "uncertainty over when records were deleted" to "complete retention in event logs"
With soft deletes, discovering "who, when, and why" a record was deleted often relies solely on application logs, creating headaches during audits, regulatory compliance reviews, and incident investigations. Through custom development, we introduce a lightweight event sourcing model (retaining event logs only for state changes) to solve auditing, recovery, and analysis in one go. This represents the application-layer design version of the DB forensics covered in our Bintrail MySQL Time Travel Client Services.
Structure 3: From "business rules scattered across application code" to "state transition tables"
When business rules are scattered throughout the application—such as "withdrawn users cannot be updated" or "archived items are excluded from search"—specification changes become a nightmare. In our custom development, we use state transition tables and guard functions to consolidate business rules in the database and backend, keeping frontends and batch jobs lightweight. This is the data model governance version of the fault-tolerant workflows explored in our Cloudflare Dynamic Workflows Client Services.
Five phases of DB redesign to eliminate soft deletes offered for clients
Phase 1: Current state assessment (2–3 weeks)
- Auditing flag and state columns across primary tables
- Organizing business states (active, archived, suspended, withdrawn, expired, etc.)
- Auditing query patterns (
WHEREconditions and index utilization) - Reviewing audit and regulatory compliance requirements
- Performance measurement (slow queries and index efficiency)
- Impact mapping (applications, batch jobs, analytics, and BI)
Phase 2: Design (2–4 weeks)
- Finalizing the business state model (state transition diagrams)
- State table design (normalization and indexes)
- Event log design (immutable, append-only, and TTL)
- Application-side guard functions and repository layer
- Phased migration planning (Strangler Pattern)
- Audit query templates
Phase 3: PoC + application to a single domain (4–6 weeks)
- Full implementation on one primary domain (e.g., users, orders, or contracts)
- Parallel run (legacy flags + new state tables + event logs)
- Consistency verification and performance benchmarking
- Rewriting existing queries
- Updating BI and analytics queries
Phase 4: Full rollout (3–6 months)
- Sequential domain-by-domain migration
- Phased deprecation of legacy flags
- Rewriting applications and batch jobs
- Establishing audit dashboards
- Stakeholder training
Phase 5: Monthly ongoing operations (retainer)
- Monitoring state transition KPIs
- Event log capacity management and TTL operations
- Supporting additional states for new business requirements
- Performance optimization
- Semi-annual architecture reviews
Standard technology stack set for custom development
| Layer | Recommended technology | Alternative |
|---|---|---|
| DB | PostgreSQL / MySQL 8 / Aurora | TiDB / CockroachDB |
| Event logs | Table appends / Outbox + Kafka | EventStoreDB |
| Migrations | Atlas / sqldef / in-house proprietary | Flyway / Liquibase |
| ORM / queries | Drizzle / sqlc / Prisma | TypeORM |
| State transitions | XState / in-house proprietary DSL | finite-state-machine |
| Audit queries | dbt / SQL / metric stores | Looker / Metabase |
| Observability | Datadog / Honeycomb | Sentry Performance |
| Data quality | Great Expectations / dbt tests | Soda |
Which SaaS platforms need it and which do not
| SaaS platforms that need it | SaaS platforms that do not |
|---|---|
| Complex business states (3 or more values) | Simple CRUD operations |
| Auditing, regulatory, or compliance mandates | Internal use only |
| Business rules scattered across application code | Few rules and stable logic |
| Withdrawal, expiration, or suspension present in business operations | States are strictly active or deleted |
| Demands for data recovery and history lookups | No historical records required |
Seven essential clauses to include in custom development contracts
| Clause | Details | What the client should verify |
|---|---|---|
| Target domains | Tables and features targeted for migration | Out-of-scope targets |
| Business rules matrix | State transitions and guard conditions | Business unit approval |
| Data consistency | Verification procedures for legacy flags vs. new states | Procedures for handling inconsistencies |
| Audit log requirements | Retention periods, searching, and statutory regulations | Regulatory compliance |
| Performance SLA | Query response times and throughput | KPI alignment |
| Phased migration | Strangler Pattern and feature flags | Rollback procedures |
| Handover Upon Project Completion | Schemas, business rules matrices, and operational runbooks | Internal operational continuity |
Client-side ROI estimation (assuming a mid-sized SaaS with 100 tables)
| Item | Soft delete operations | State tables + event logs | Difference |
|---|---|---|---|
| Number of slow queries | 80 / month | 20 / month | -60 / month |
| Data inconsistency incidents | 5 incidents / month | 0.5 / month | -4.5 / month |
| Audit and regulatory compliance effort | 80 hours / month | 15 hours / month | -65 hours |
| Impact analysis for specification changes | 40 hours / case | 8 hours / case | -32 hours |
| Data recovery resolution time | 4 hours / case | 0.5 hours / case | -3.5 hours |
| Annual benefit | — | — | Equivalent to approximately 12 million yen + 90% incident reduction |
Calculated at an hourly rate of 8,000 yen, this yields business benefits of over 10 million yen in annual labor savings + reduced incident and audit risks. Even an investment of this scale can be recouped within 8 months.
Five common pitfalls
Pitfall 1: Refactoring by "merely adding more flags"
Simply adding a status column to use alongside deleted_flag ushers in an era where both must be written in every WHERE clause. Finalize normalization into state tables and the phased deprecation of legacy flags during the design stage.
Pitfall 2: "Migrating all tables at once"
Changing 100 tables at once will cause inconsistency detection to break down. Proceed in two- to three-month increments using the Strangler Pattern, domain boundaries, and parallel runs.
Pitfall 3: Event logs that "store everything for now"
Turning updates across every row into events causes storage capacity to explode. Retain logs strictly for state changes, and determine TTLs and archiving during initial design.
Pitfall 4: Overlooking rewrites for BI and analytics queries
Even if the OLTP side is transformed, failing to rewrite dbt, BI, and executive dashboards will deliver inconsistent metrics to leadership. Always include BI within the migration scope.
Pitfall 5: Lacking consensus with business units
"Defining business states" is a realm that engineers cannot decide in isolation. Always first conduct business unit workshops to achieve consensus on state transition diagrams.
90-day action plan
| Week | Action |
|---|---|
| Week 1〜2 | Current-state assessment + flag column auditing + performance measurement |
| Week 3〜4 | Business unit workshops + consensus on state transition diagrams |
| Week 5〜6 | State table and event log design specifications |
| Week 7〜9 | Single-domain PoC + parallel run |
| Week 10 | Consistency verification and performance benchmarking |
| Week 11 | Rewriting BI and analytics queries |
| Week 12 | Production switchover for one primary domain |
| Week 13 | Monthly operations agreement + rollout plan for remaining domains |
Summary — Toward DB design where data speaks the business domain
The era of using a single deleted_flag to represent five business states has reached its limit against increasingly complex enterprise SaaS, audit requirements, and data recovery demands. From the standpoint of supporting mid-market enterprise SaaS and mission-critical systems through custom development, "soft delete retirement DB redesign" providing integrated assessment, design, PoC, phased rollout, and monthly operations will be our new core service offering.
If you are dealing with issues such as "All our queries are collapsing due to deleted_flag," "We cannot capture audit logs," or "Specifications for withdrawal, expiration, and suspension are fragmented," please feel free to reach out through our inquiry form.
Sources
- DB Design That Retires Soft Deletes and Separates State into Tables (Zenn 2026-05-28)
- Bringing Time Travel Queries to MySQL with Bintrail: Designing DB Forensics Services for Clients (gihyo.jp)
- Custom Bintrail MySQL Time Travel Development (GH Media)
- Cloudflare Dynamic Workflows Client Services (GH Media)
- Monzo Data Mesh Client Services (GH Media)
