Implement soft delete (recycle bin): `enable.trash` / `softDelete` are spec-only with zero runtime readers

[baozhoutao]

Current state

The spec declares soft-delete capability in two places, but neither has any behavior-changing reader — every delete today is a physical hard delete at the driver level.

• enable.trash (default true, described as "Enable soft-delete with restore capability") — packages/spec/src/data/object.zod.ts (~L110)
• SoftDeleteConfigSchema (enabled / field: 'deleted_at' / cascadeDelete) — packages/spec/src/data/object.zod.ts (~L288)
• The permission model also reserves a Restore (soft-delete recovery) semantic (packages/spec/src/security/permission.zod.ts)

The June 2026 liveness audit (docs/audits/2026-06-objectschema-property-liveness.md) confirms:

trash is one of the 10 flags with zero behavior-changing readers in either repo; softDelete ↔ enable.trash — duplicated, both dead.

The default trash: true is actively misleading: schema authors reasonably assume deleted records are recoverable, but they are gone.

Note: deleted_at on some identity objects (e.g. sys_user) is a better-auth convention local to those objects, not a platform mechanism.

Proposal

Implement platform-level soft delete honoring the existing spec surface:

1. Delete path: when soft delete is enabled for an object, delete stamps the configured timestamp field (default deleted_at) instead of removing the row.
2. Query path: default-scope all reads to exclude soft-deleted rows; provide an explicit opt-in to include/only-trashed (recycle-bin views).
3. Restore: a restore operation clearing the marker, gated by the reserved Restore permission.
4. Purge: a real (physical) delete for emptying the trash, plus optional retention/TTL via the ADR-0057 LifecycleService.
5. Cascade: honor cascadeDelete for related records (or explicitly descope it for v1).
6. Spec reconciliation: collapse the duplicated enable.trash vs softDelete surface into one canonical config (and fix the misleading trash: true default until the runtime actually enforces it).

Open questions: interaction with unique indexes (soft-deleted rows still occupy unique keys), lookup integrity when a target is trashed, and REST/console surface for the recycle bin.