[bug]: P0 document deletion orphans storage objects

[kien-ship-it]

Summary

Priority: P0 — systemic storage-lifecycle omission.

Normal single-document deletion and normal batch-document deletion clean up Postgres only. They do not enumerate the document's storage objects, preserve provider/object identity, or call any storage deletion operation. The database cascade removes document_versions rows and their URLs, while bytes remain in S3-compatible storage, Vercel Blob, or the database-backed fileUploads store.

A separate S3 bucket/key parsing bug exists in the version-delete endpoint, but correcting that parser does not resolve the primary incident:

• full-document and batch routes never call the parser or storage;
• Vercel Blob deletion is explicitly unwired;
• database-backed /api/files/{id} deletion returns before removing the storage row;
• ZIP extraction, generated summaries/transcripts, and DOCX replacements create additional objects without a durable cleanup collection.

The resolution must add a complete deletion lifecycle: provider-owned opaque object identity, a persisted object manifest, durable deletion intent before relational cascades, provider-specific batch deletion with idempotent retries, hard deletion only after storage cleanup, and reconciliation for existing orphans.

Reproduction

Normal UI single delete

1. Upload a document using any provider-backed storage mode.
2. Open the document workspace.
3. Delete the document from the UI.
4. Observe that DELETE /api/deleteDocument succeeds and the document disappears from the UI/database.
5. Inspect the configured storage backend.
6. The uploaded object remains.

Code path:

1. WorkspaceShell sends only { docId: String(docId) } to DELETE /api/deleteDocument (apps/web/src/app/employer/documents/_workspace/WorkspaceShell.tsx:318-337).
2. The route authenticates the user and validates the ID, but does not load document URLs, versions, provider, key, or pathname (apps/web/src/app/api/deleteDocument/route.ts:8-47).
3. Its transaction deletes relational children and finally the document row (apps/web/src/app/api/deleteDocument/route.ts:50-72).
4. It returns success after that database transaction (apps/web/src/app/api/deleteDocument/route.ts:74-83).
5. There is no storage import or deletion call on the route.

API batch delete

1. Create several documents with provider-backed objects.
2. Call DELETE /api/documents/batchDelete with their IDs.
3. The endpoint reports every document deleted.
4. Inspect storage; the objects remain.

The endpoint accepts up to 100 document IDs, validates company ownership, and calls the shared DB-only deleteDocumentCore in one transaction (apps/web/src/app/api/documents/batchDelete/route.ts:28-33, :68-105). The shared helper contains only ordered tx.delete(...) calls and a final document-row delete (apps/web/src/server/services/document-delete.ts:31-56).

Version-delete S3 mismatch

For a configured endpoint E, bucket B, and actual key K:

• upload sends PutObjectCommand({ Bucket: B, Key: K });
• getObjectUrl(K) stores E/B/K;
• deleteFileByUrl removes only E/, producing B/K;
• deletion sends DeleteObjectCommand({ Bucket: B, Key: B/K }).

Concrete example:

E = http://localhost:8333
B = pdr-documents
K = documents/abc-file.pdf

stored URL:
http://localhost:8333/pdr-documents/documents/abc-file.pdf

actual upload identity:
Bucket = pdr-documents
Key    = documents/abc-file.pdf

current delete identity:
Bucket = pdr-documents
Key    = pdr-documents/documents/abc-file.pdf

Evidence:

• S3 upload key and returned URL: apps/web/src/lib/storage.ts:101-119
• bucket/key put and URL construction: apps/web/src/server/storage/s3-client.ts:70-98
• endpoint-only slicing: apps/web/src/lib/storage.ts:255-270
• final delete request: apps/web/src/server/storage/s3-client.ts:100-115
• version row deletion after non-throwing helper return: apps/web/src/app/api/documents/[id]/versions/[versionId]/route.ts:150-176

The actual call chain is:

deleteFileByUrl(url)
  -> deleteFile(parsedKey, "s3")
  -> deleteObject(parsedKey)

A missing-key delete can be accepted as non-throwing by an S3-compatible provider, so this path can report success and remove the version row while leaving the actual object. Runtime missing-key behavior varies by deployed provider; the deterministic bug is the wrong target identity.

Expected behavior

A document delete is complete only when:

1. all objects owned by the document/version/artifact group are captured in a durable deletion plan;
2. provider adapters delete or explicitly confirm absence for those exact opaque references;
3. transient and partial failures remain retryable without losing object identity;
4. relational document/version rows are hard-deleted only after storage reaches a terminal clean state;
5. the API distinguishes queued, completed, manual_review, and quarantined instead of reporting database cleanup as physical deletion;
6. single, batch, version, generated-artifact, and replacement flows use the same lifecycle.

Actual behavior

P0: normal single and batch deletes are DB-only

• No document/version object references are selected before deletion.
• No provider or storage-port delete operation is invoked.
• document_versions.documentId cascades on document deletion (packages/core/src/db/schema/base.ts:203-230).
• Version URLs disappear from ordinary relational discovery while external bytes remain.
• A batch request can create this orphan class for up to 100 documents at once.

P1: version delete uses the wrong S3 key

The canonical URL contains endpoint, bucket, and key, but the parser strips only the endpoint. The configured bucket is then supplied separately to DeleteObject, duplicating the bucket inside Key.

P1/P2: Vercel Blob deletion is a no-op

deleteFileByUrl explicitly routes non-S3 Blob URLs into the database branch, whose /api/files/{id} regex does not match a Blob URL (apps/web/src/lib/storage.ts:255-274). The Vercel module imports put and implements upload/fetch, but exposes no delete operation (apps/web/src/server/storage/vercel-blob.ts:1-94).

P1/P2: database-backed deletion dispatch is broken

Database uploads store bytes in fileUploads and return /api/files/{id} (apps/web/src/lib/storage.ts:130-162; packages/core/src/db/schema/base.ts:386-406). deleteFileByUrl returns immediately for canonical relative /api/files/{id} URLs (apps/web/src/lib/storage.ts:255-260) instead of deleting that row.

Unknown URLs are forwarded to deleteFile(url, "database"); its unscoped regex can no-op or delete a matched /api/files/{id} row (apps/web/src/lib/storage.ts:223-232, :270-274). Unknown identities must not silently fall through to another provider.

End-to-end lifecycle findings

Upload identity is available, then discarded

• S3 upload returns both url and pathname/key (apps/web/src/lib/storage.ts:101-119).
• The browser S3 route returns { objectKey, bucket, url } (apps/web/src/app/api/storage/upload/route.ts:39-50).
• The document registration schema accepts storageProvider and storagePathname, but the handler drops both before calling the upload service (apps/web/src/app/api/uploadDocument/route.ts:24-60, :80-93).
• document and document_versions persist URLs, not provider-owned object references (packages/core/src/db/schema/base.ts:157-230).
• fileUploads has provider/path/checksum metadata but no document/version ownership relationship in the shown schema (packages/core/src/db/schema/base.ts:386-406, :737-759).

The system therefore has object identity at creation time but does not preserve it as a lifecycle ownership record.

Generated and replacement artifacts amplify the problem

• ZIP extraction writes every extracted file through a Blob-specific writer and creates child rows containing only URLs (apps/web/src/server/inngest/functions/processDocument.ts:346-476).
• The generated project summary is another object and row (apps/web/src/server/inngest/functions/processDocument.ts:507-565).
• The delete-zip-document step deletes only the original OCR job and original document row; it does not delete the original ZIP or any object collection (apps/web/src/server/inngest/functions/processDocument.ts:604-614).
• Audio/video paths create transcript objects/documents without a durable source/derivative ownership graph (apps/web/src/server/services/document-upload.ts:173-278, :369-435).
• DOCX modification writes a replacement Blob object and then overwrites document.url; it does not preserve or enqueue the old object reference (apps/web/src/server/inngest/functions/modifyDocument.ts:62-97, :131-143).
• The GitHub ZIP route writes through a Blob-specific module while passing explicitStorageType: "s3", demonstrating leaked/mismatched provider knowledge (apps/web/src/app/api/upload/github-repo/route.ts:82-116).

Auxiliary rows do not supply a cleanup contract

• OCR jobs retain documentUrl while their document FK uses ON DELETE SET NULL (packages/core/src/db/schema/base.ts:412-430).
• Upload batch files retain storageUrl/storageType while their document FK uses ON DELETE SET NULL (packages/core/src/db/schema/base.ts:596-625).
• These fields may aid audit/backfill, but no current delete workflow treats them as an owned object manifest.

Root cause

Orphaned or undeletable document-storage objects
├── P0: normal full-document and batch delete have no storage lifecycle
│   ├── UI sends only docId
│   ├── routes/services perform relational deletes only
│   ├── no pre-delete object-reference snapshot
│   ├── no collection/delete-many contract
│   └── DB cascade removes version URLs before provider cleanup
├── Storage identity is not durable
│   ├── document/version rows are URL-only
│   ├── accepted storageProvider/storagePathname are discarded
│   ├── fileUploads is structurally unlinked from document ownership
│   ├── generated artifacts have URLs/free-form lineage only
│   └── edit replacement overwrites the old URL
├── Provider-specific gaps amplify the omission
│   ├── S3 URL parse duplicates bucket in Key
│   ├── Vercel Blob has no delete operation
│   ├── database canonical URL returns before deletion
│   └── current port exposes singular URL-or-key delete only
└── Detection controls are missing in the inspected code/docs
    ├── API success measures SQL completion
    ├── wrong-key/no-op paths may not throw
    ├── deletion tests do not invoke deletion APIs
    ├── no durable manifest/reconciliation contract is shown
    └── no object-deletion backlog/orphan-age signal is shown

This is a missing ownership and deletion lifecycle. The S3 parser defect is one symptom inside that larger failure.

Impact

• Retention/privacy: a user-visible delete can remove metadata while retaining source bytes and generated derivatives.
• Cost: external storage, replication, backup, and egress exposure continue; ZIP and replacement flows multiply object count.
• Auditability: the system cannot prove which exact provider objects were deleted, when, or after which retries.
• Data integrity: DB-first deletion loses cleanup evidence; ad hoc provider-first deletion cannot be rolled back if SQL later fails.
• Operations: existing exposure cannot be quantified from the inspected repository sources. A provider inventory and relational-reference audit are required.

Production provider mix, current orphan count/bytes, retention rules, and provider-specific missing-key behavior are not established by the repository evidence and must be measured.

Proposed resolution

1. Provider-owned opaque identity

type ObjectRef = {
  adapter: "s3" | "vercel-blob" | "database" | "uploadthing";
  storageLocationId: string; // immutable adapter-instance/config-version identity
  key: string;               // opaque outside the adapter
};

storageLocationId identifies the immutable bucket/account/endpoint, Blob store, or database namespace. The adapter resolves trusted server-side credentials/configuration from that ID. Callers cannot construct or parse provider URLs or reinterpret an old key using current configuration.

Proposed storage port:

interface StoragePort {
  put(input: PutInput): Promise<{ ref: ObjectRef; url: string }>;
  get(ref: ObjectRef): Promise<ReadableStream | Buffer>;
  delete(ref: ObjectRef): Promise<void>;
  deleteMany(refs: ObjectRef[]): Promise<DeleteResult[]>;
  getSignedUrl(ref: ObjectRef, opts?: SignedUrlOptions): Promise<string>;
}

Group deleteMany by stable (adapter, storageLocationId), then apply provider-specific batch limits. Listing belongs to a privileged reconciliation interface, not ordinary document callers.

2. Persist an object manifest and artifact lineage

Every successful object write must have a manifest owner before the document/version/artifact becomes active. At minimum, persist:

• immutable ObjectRef;
• tenant/company and owner type/ID;
• artifact group and parent/derivative edge;
• content type, size, checksum, source operation, and timestamps where available;
• lifecycle state and deletion attempts/errors;
• uniqueness/refcount ownership if sharing is supported.

New writes default to exclusive ownership. Shared refs are prohibited unless reference-counted ownership or an equivalent ownership set is implemented transactionally.

3. Durable deletion intent before cascades

ACTIVE
  -> DELETE_REQUESTED
  -> STORAGE_DELETING
  -> STORAGE_CLEAN
  -> RELATIONAL_PURGE
  -> PURGED

STORAGE_DELETING -> WAITING_RETRY -> STORAGE_DELETING
STORAGE_DELETING -> BLOCKED
BLOCKED -> STORAGE_DELETING   (audited repair/requeue)
BLOCKED -> QUARANTINED        (approved exception; never physical-deletion success)
RELATIONAL_PURGE -> PURGE_RETRY -> RELATIONAL_PURGE
DELETE_REQUESTED -> CANCELLED (only before provider deletion starts)

Request flow:

1. Authorize and lock the document/version.
2. Snapshot every owned manifest ref and artifact edge.
3. In one SQL transaction, mark the document deleting/tombstoned and insert the immutable deletion request/outbox.
4. Commit before any provider call.
5. A worker groups refs by (adapter, storageLocationId), deletes in provider-supported batches, and records per-item outcomes.
6. Retry only failed/unknown items; exact-ref deletion must be idempotent.
7. Hard-delete relational rows only after every required item is DELETED or exact-ref NOT_FOUND.
8. Keep deletion tombstones outside the document cascade for audit and idempotent responses.

A BLOCKED or QUARANTINED document remains unserved. QUARANTINED/manual_review is an explicit exception state and must never be reported as physical deletion or completed.

4. Apply one coordinator to every path

• Full document: collect every version/current/source/owned derivative ref before cascade.
• Batch: authorize all IDs, atomically accept durable intent, group/deduplicate by provider location, and expose per-document/per-object progress. Do not promise physically atomic rollback across providers.
• Version: retain current/only-version safeguards and delete its manifest refs rather than parsing url.
• ZIP/generated: define explicit artifact groups for original ZIP, extracted children, summary, child documents, and jobs.
• Audio/video: define source/transcript ownership and propagation.
• Edit replacement: persist the new object/manifest first; in the same SQL transaction switch the pointer and insert the old-ref deletion outbox; provider deletion starts only after commit.

5. Provider adapters

• S3-compatible: derive ObjectRef from the same bucket/key used by PutObject; delete using that stored key and the immutable storage location; implement multi-object batching and per-key outcome handling.
• Vercel Blob: add a real delete adapter using the provider-owned pathname/ref; do not route Blob URLs through the database regex.
• Database fallback: use the fileUploads row ID as the opaque key and link it to manifest ownership.
• Unsupported/legacy: quarantine unknown references. Never guess a provider/key from an arbitrary URL or silently fall through to another adapter.

Failure semantics

Situation	Required behavior
Transient provider/network error	Retain manifest and relational refs; retry the exact immutable ref with bounded exponential backoff/jitter. Do not claim completion while outcome is unknown.
Permanent auth/config error	Mark BLOCKED/manual_review, alert, retain refs, and never fall through to another provider.
Object already absent	Success only under the adapter's exact idempotent/missing-object contract; record the result.
Provider bulk partial failure	Persist per-item outcomes and retry only unresolved refs.
Worker crash after provider success	Retry the exact same idempotent ref and converge.
SQL purge failure after storage cleanup	Retry SQL only; do not restore or re-delete a different object.
Unknown/ambiguous legacy ref	Quarantine for audited review; never infer destructively.
Repaired blocked item	Allow BLOCKED -> STORAGE_DELETING only after validating ref, storage location, and credentials.
Approved permanent exception	Allow BLOCKED -> QUARANTINED, retain tombstone/audit, keep unserved, and never report physical deletion.

Existing-orphan audit and backfill

Run read-only until classification is approved.

Inputs:

1. Provider inventory by immutable location/bucket/store, including key/pathname, size, checksum/ETag where meaningful, timestamps, and version/delete-marker state.
2. Current references from document.url, document_versions.url, ocr_jobs.document_url, upload_batch_files.storage_url, and file_uploads identity fields.
3. Historical evidence from deletion logs, upload records, backups, and migrations.
4. Dedicated legacy parsers that compare the actual S3 K with the erroneous B/K without mutating objects.

Classification:

• Referenced/live: exact current owner; never cleanup-eligible.
• Referenced/historical: exact historical owner/deletion evidence; never cleanup-eligible.
• Confirmed orphan: exact provider/location identity, no live or historical reference, proven tenant owner, no shared/unresolved owner or legal hold, and recorded approval. Only this class can be deleted.
• Unresolved/unreferenced candidate: incomplete ownership evidence; not cleanup-eligible.
• Ambiguous/shared: multiple owners or possible sharing/legal hold; not cleanup-eligible.
• Missing: reference exists but object is absent; handle only through exact adapter contract.
• Cross-tenant/suspicious: ownership conflict; quarantine and investigate.

Controlled cleanup must use a dry-run report, approval, and grace period. Delete only Confirmed orphan items, record provider responses and approvals, then re-inventory to verify results.

Legacy remediation is complete only when the responsible owner and independent tenant/legal-hold approver sign a report showing:

• zero Confirmed-orphan backlog by object count and bytes;
• a signed exception inventory covering every unresolved/ambiguous item;
• snapshot/report identity, provider/tenant object and byte counts, accountable owner, reason, expiry/review date, and cohort thresholds for every exception.

Tests and executable acceptance criteria

• Single delete creates a durable complete object plan before any cascade and hard-deletes rows only after storage completion.
• Batch delete covers the endpoint maximum of 100 documents, mixed providers, duplicates, provider-specific chunking, and partial failures; only failed items retry.
• S3 upload/delete use identical bucket, key, and storageLocationId; a test fails if bucket is duplicated in Key.
• Vercel Blob upload/delete round-trip through a real adapter; no Blob ref enters the database branch.
• Database-backed bytes are manifest-owned and the correct fileUploads row is removed without touching unrelated rows.
• Current/only-version safeguards remain and non-current version deletion cleans exactly its owned refs.
• ZIP, generated summaries, transcripts, and replacements have explicit ownership edges and tested propagation policies.
• Pointer switch and old-ref outbox insertion for edit replacement commit in the same SQL transaction.
• Transient, permanent, timeout, missing-object, partial-batch, worker-crash, and SQL-after-provider failures converge through the documented states.
• BLOCKED repair/requeue and approved QUARANTINED behavior are tested; neither can report false physical deletion.
• New successful object writes have 100% manifest coverage before activation.
• No hard-purged document has a deletion item in PENDING, IN_FLIGHT, RETRYABLE_FAILED, BLOCKED, or QUARANTINED.
• Tenant authorization is enforced before resolving or deleting any manifest ref; callers cannot substitute provider identities.
• Audit dry-runs are deterministic and non-mutating; referenced and ambiguous objects are never cleanup candidates.
• Dashboards and alerts distinguish SQL deletion from provider cleanup and expose request/item backlog, failures, retries, oldest age, blocked/quarantined counts, and estimated orphan bytes.
• Rollback/kill switch pauses workers without discarding durable intent.

Immediate containment

1. Gate or temporarily disable normal full-document and batch hard deletes where storage cleanup cannot be guaranteed. Return queued/unavailable status rather than false completion.
2. Disable or repair version deletion for S3/Blob URLs. A minimal bucket-strip fix may reduce new S3 version orphans but is not a full resolution.
3. Snapshot current URL/provider/path data before additional hard deletes and dual-write manifests for new uploads.
4. Preserve deletion audit events without logging signed URLs, credentials, or sensitive object names.
5. Freeze destructive orphan scripts until tenant ownership, legal holds, shared refs, and the Confirmed-orphan classification are available.

Rollout

1. Shadow provider inventory and relational-reference comparison.
2. Dual-write ObjectRef/manifest for new uploads and replacements.
3. Run adapter delete contracts in dry-run/disabled mode.
4. Enable manifested version deletion for a controlled cohort.
5. Enable full single and batch deletion with explicit status states.
6. Backfill only high-confidence references; delete only approved Confirmed orphans after grace period.
7. Remove destructive URL parsing once active records have opaque refs or explicit quarantine state.

Rollback pauses the destructive worker while preserving outbox/manifest intent and relational references. It cannot restore already deleted provider bytes.

Affected area

• apps/web
• Docker / deployment (SeaweedFS/S3-compatible environments)
• @launchstack/core storage contract/types
• Storage operations and observability

Source index

• apps/web/src/app/api/deleteDocument/route.ts:50-83 — DB-only single delete and success response
• apps/web/src/server/services/document-delete.ts:31-56 — DB-only shared/batch helper
• apps/web/src/app/api/documents/batchDelete/route.ts:28-105 — batch validation, authorization, transaction, success
• apps/web/src/lib/storage.ts:93-171, :202-275 — upload identities, database storage, S3 URL parsing, Blob/database delete dispatch
• apps/web/src/server/storage/s3-client.ts:70-115 — S3 bucket/key put, URL construction, singular delete
• packages/core/src/db/schema/base.ts:157-230, :386-430, :596-625 — document/version URLs, cascades, storage-bearing auxiliary rows
• apps/web/src/app/api/documents/[id]/versions/[versionId]/route.ts:94-176 — one URL-based version delete
• apps/web/src/server/storage/port.ts:21-50; packages/core/src/storage/types.ts:10-24 — ambiguous URL/key and singular-delete contract
• apps/web/src/server/storage/vercel-blob.ts:1-94 — Blob upload/fetch, no delete
• apps/web/src/server/inngest/functions/processDocument.ts:434-565, :604-614 — ZIP/generated object creation and DB-only original cleanup
• apps/web/src/server/inngest/functions/modifyDocument.ts:62-97, :131-143 — replacement object creation and pointer overwrite
• apps/web/__tests__/api/storage/storage-adapter.pbt.test.ts:46-72, :131-185, :262-307 — delete mocks exist, but deletion behavior is not exercised
• docs/architecture/current-infrastructure-map.md:177-180, :232-234 — supported provider shapes

Open decisions required before destructive rollout

1. Synchronous wait versus queued API/status semantics.
2. Artifact-edge propagation for ZIP children, summaries, transcripts, and child documents.
3. Exclusive ownership versus transactional reference counting for intentionally shared objects.
4. Production provider/location inventory and legacy URL classification rules.
5. Retention, legal hold, quarantine, exception, and grace-period policy.
6. Database fallback support versus retirement.
7. Provider-specific listing/delete limits and permissions.