Soft-deleted documents hold their path against new documents #69

Implementation status (PR #71)

PR #71 implements approach A on both built-in adapters:

• path rows retain their values but use deleted_at plus generated alive for live-only uniqueness and lookup;
• soft delete tombstones every version and path row atomically, while explicit storage-level un-delete restores both or rolls back on a reclaimed-path conflict;
• uploaded sources and persisted generated variants are retained because immutable versions and duplicated documents can share storage paths; ordinary soft delete performs no object cleanup;
• the reference document importer is now an adapter-neutral plain upsert and rejects the retired deleted-document --force flag;
• existing installations use packages/db-postgres/sql/0006_soft_delete_path_liveness.sql or packages/db-mysql/sql/0001_soft_delete_path_liveness.sql, not the squashed fresh-install baseline.

Issue #72 owns the deferred design for reference-safe media regeneration and eventual cleanup. This issue remains open until PR #71 merges.

Problem

A soft-deleted document keeps its path reserved forever. Creating a new document at that path fails with ERR_PATH_CONFLICT against a document the editor has already, deliberately, deleted — and there is no supported way to release it.

The deletion may have been entirely correct and intentional. From the editor's point of view the document is gone: it is absent from every list view, every read, and every public route. But the path namespace still says otherwise, and the error message gives no indication that the blocking document is deleted rather than live.

Why it happens

Byline's delete is soft. softDeleteDocument (packages/db-postgres/src/modules/storage/storage-commands.ts, and the MySQL twin) sets is_deleted = true on every version row of the document. The byline_current_documents view filters those rows out, so the document disappears from reads.

Nothing touches byline_document_paths. That table is keyed (document_id, locale) and carries a unique constraint on (collection_id, locale, path) (packages/db-postgres/src/database/schema/index.ts:170-196). It has no notion of deletion — the row survives the soft delete intact and keeps occupying the slot. The next createDocument at that path hits SQLSTATE 23505, which classifyError reports as a unique violation on idx_document_paths_collection_locale_path and core's rethrowPathConflict translates into ERR_PATH_CONFLICT.

There is also no un-delete surface anywhere in the system. restoreDocumentVersion restores a version of a live document; it does not clear is_deleted. So once a document is deleted, its path is unreachable through every supported API — it can be neither reclaimed by a new document nor returned to the deleted one.

Evidence that this already bites

Both repository import scripts have had to work around it in raw SQL:

• apps/webapp/byline/scripts/lib/import-docs-force.ts implements a --force "revive" that reaches past the lifecycle layer and runs UPDATE ... SET is_deleted = false, status = 'draft' on the latest deleted version, precisely so the import can reuse the held path.
• apps/webapp/byline/scripts/lib/media-ingest.ts:493-510 performs the same dance for media, and when it cannot, raises: media path '<path>' is reserved by a deleted media document. Re-run with --force to reclaim it, rename the source image, or purge that document.

Neither script should need to bypass the lifecycle layer to do something this ordinary.

Candidate approaches

Not a decision — the point of the issue is to pick one.

A. Scope path uniqueness to live documents (recommended). Add a liveness marker to byline_document_paths that is NULL for a deleted document and a constant for a live one, and move the unique constraint to (collection_id, locale, path, <marker>). Both PostgreSQL and MySQL exclude NULLs from unique-key collisions, so this gives partial uniqueness portably — PostgreSQL's partial unique index has no MySQL 8 equivalent, so the NULL formulation is what keeps the two adapters at parity. The path value stays on the row, so a future un-delete still knows what the document was addressed as, and reclaiming that address becomes a conflict raised at un-delete time rather than at create time. softDeleteDocument maintains the marker inside its existing transaction.

B. Delete the path row on soft delete. Simplest change, same create-side outcome. Loses the original path, so an un-deleted document comes back with no address and any restore-with-path story has to be designed separately.

C. Rename the held path to a tombstone value (__deleted__/<document_id>/<path>). Works under the existing constraint on both adapters, but mutates stored editorial data and needs an extra column to be reversible — strictly worse than A.

D. Improve the error only. Keep the invariant and have ERR_PATH_CONFLICT distinguish "held by a deleted document" from "held by a live one", with an admin action to reclaim it. This does not solve the problem on its own, but the clearer error is worth having alongside A or B — today the editor cannot tell the two cases apart.

Open questions

• Does un-deleting a document belong in this issue's scope, or does it want its own? Approach A makes it representable; it does not implement it. Recommend keeping the un-delete surface separate and limiting this issue to releasing the path.
• Does hard delete / purge need to land at the same time to give operators a way to reclaim storage as well as the path?
• Interaction with the tree reconciliation in packages/core/src/services/document-lifecycle/tree.ts, which already runs on delete and has its own view of placement.
• Per-locale paths (Per-locale paths (translated slugs) #25) is still deferred, but the constraint is already locale-keyed, so whatever lands here must stay correct per locale rather than per document.

Acceptance criteria

• Deleting a document and then creating a new one at the same path succeeds through the public lifecycle API, with no raw SQL and no --force flag.
• The behaviour is identical on @byline/db-postgres and @byline/db-mysql, covered by the shared suite in packages/db-conformance/src/suites/document-paths.ts.
• Two documents deleted at the same path do not collide with each other.
• A live document still cannot take a path held by another live document — ERR_PATH_CONFLICT is unchanged for the case it was designed for.
• apps/webapp/byline/scripts/lib/import-docs-force.ts and media-ingest.ts can drop their revive workarounds, or the issue records why they still need them.
• docs/04-collections/05-document-paths.md ("Path uniqueness", and the lifecycle bullets around line 215) documents what a delete now does to the path row.