Teaching Data Retention the Right Way: A SapixDB Compliance Archive Walkthrough

Picture this: it's audit season, and someone from compliance asks a question every engineering team dreads — "Can you prove this record was deleted, when it happened, and what it said before it was removed?" In most databases, the honest answer involves a backup restore, a grep through logs, and a prayer. In SapixDB, it's a handful of well-defined API calls, because deletion, in this system, was never a single blunt operation. It's four distinct, deliberately separate primitives — and knowing which one to reach for is the whole lesson.

This post walks through a realistic scenario — a healthcare support-ticket system with HIPAA-grade retention obligations — to show exactly how SapixDB's tombstones, soft-deletes, restores, and time travel fit together, plus how the actual Retention Policy feature automates the whole thing.

The Scenario: A Compliance Archive for Support Tickets

Suppose you run support-tickets-agent, an agent that owns every customer support ticket your company has ever logged. Some of those tickets contain protected health information (PHI). Compliance requires that resolved tickets be removed from everyday view after 180 days, that removals are provably logged, and that — if a removal turns out to be a mistake — someone can undo it without touching a backup tape.

Creating the agent itself requires an admin-scoped key:

POST /v1/agents
Authorization: Bearer <admin-key>

{
  "agent_id": "support-tickets-agent",
  "seed_hex": "4f3c9a1e...64-char-hex-seed"
}

This call needs an admin-scoped key — it's not something a regular service key can do — and it silently writes the agent's genesis block for you.

Writing a ticket is a normal append:

POST /v1/agents/support-tickets-agent/records

{
  "data": {
    "type": "ticket",
    "id": "TCK-4821",
    "status": "resolved",
    "priority": "high",
    "assigned_to": "lena.fischer"
  },
  "flags": 0
}

Now the compliance clock starts ticking on TCK-4821. Here's where the four primitives come in — and where most teams, understandably, get confused.

Four Operations, One Mental Model

It's worth saying plainly: these are four distinct things, and they are not interchangeable.

• Gone from view → tombstone (flags: 2) — any writer can do this
• Gone from view, plus a privileged, provable record of who removed it and why → soft-delete (DELETE, admin only)
• Want it back → /restore
• Want to know what it used to say → as_of

Tombstone: Making a Record Disappear From Normal Queries

A tombstone is what makes a ticket stop showing up in the support dashboard's "Current" and "CountCurrent" views. The data is never erased — it's just no longer considered current.

POST /v1/agents/support-tickets-agent/records

{
  "data": { "_original_hash": "3a7fcd91..." },
  "flags": 2
}

Use this when the requirement is "this row should no longer appear in the app's normal view." Notice there's no special auth header here — any caller with ordinary write access to the agent can do this. SapixDB treats "retire this from view" as routine data-plane work, not a privileged action.

Soft-Delete: The Same Disappearance, Plus a Trail Only an Admin Can Leave

The soft-delete endpoint is admin-scoped only — a regular service key gets a 403 here:

DELETE /v1/agents/support-tickets-agent/records/3a7fcd91...
Authorization: Bearer <admin-key>

This writes a {"_deleted": true, "_original_hash": "3a7fcd91...", "_deleted_at_ms": ...} marker — and that marker carries the tombstone flag too. The original ticket disappears from current/count_current immediately, the same as a plain tombstone, and becomes independently discoverable as a provable, privileged audit entry:

GET /v1/agents/support-tickets-agent/records/deleted

So the real distinction between the two primitives isn't "hidden vs. logged" — both hide the ticket now. It's who's allowed to invoke which guarantee. Any writer can retire a ticket from view with a plain tombstone; only an admin can additionally assert "this removal was a deliberate, compliance-driven action," with the proof to show for it. Reach for a plain tombstone for routine cleanup; reach for soft-delete the moment the removal itself needs to be defensible later — in front of the exact compliance question that opened this post.

Restore: The Real One-Call Undo

Say the tombstone on TCK-4821 turns out to have been premature — someone jumped the gun before the ticket was actually ready to retire. There's no "un-tombstone" operation that reaches back and flips a bit on the old record — nothing in SapixDB ever reaches back and changes an old record once it's written. Instead, restore reads the original payload and appends it fresh:

POST /v1/agents/support-tickets-agent/records/3a7fcd91.../restore

{
  "restored": true,
  "original_hash": "3a7fcd91...",
  "new_content_hash": "9c21fe84...",
  "new_record_id": "01a1183f-7b2c-7e91-9a4d-5c8f21e07a36"
}

The new hash and record ID matter: this is a brand-new, currently-active entry carrying the old payload, not the old entry reactivated in place. It goes through the exact same schema validation and insert checks as any other write — and, worth knowing if you're scripting around this, the endpoint doesn't check whether the hash you give it was ever tombstoned or soft-deleted in the first place. It will happily re-append the payload of any valid hash on the strand. The guardrail here is who's allowed to call it, not what state the target record happens to be in.

Time Travel: Proving What It Said Before Any of This Happened

The actual question compliance usually cares about isn't "is the ticket gone" — it's "what did it say, and when did it say it." SapixDB answers that natively:

POST /v1/agents/support-tickets-agent/query

{
  "type": "as_of",
  "timestamp_hlc": 116737553203200000,
  "limit": 50
}

That timestamp_hlc isn't a calendar date — SapixDB orders every record with a Hybrid Logical Clock, and the API expects one directly instead of parsing a date string for you. The conversion is simple once you know it: take epoch milliseconds and shift left by 16 bits (hlc = epoch_ms << 16). The query then returns every record that existed at or before that instant, oldest first — exactly the view an auditor needs, with nothing reconstructed or inferred after the fact.

One thing worth being precise about: as_of doesn't know what a tombstone is. It returns everything written by that point in the strand's history, tombstone markers included if they'd already been written — it's a raw historical view, not a "current state as of the past" collapse. If TCK-4821 was tombstoned after the timestamp you query, you'll simply see the original record, because the tombstone didn't exist yet at that point in history. That's not special tombstone-awareness — it's just history being history.

Automating the Whole Thing: Retention Policies

Everything above is something a human decides to do, once, on purpose. "Resolved tickets expire after 180 days" isn't a one-off decision — it's a standing rule, and SapixDB has a feature that enforces it automatically, with no per-ticket action from anyone:

POST /v1/policies/retention

{
  "name": "PHI-bearing tickets — 180 day retention",
  "max_age_secs": 15552000,
  "match_phi": true,
  "priority": 10
}

Two things worth being explicit about. First, there's no agent_id field — retention policies are global, evaluated by a background sweep (every 5 minutes) against every registered agent, not configured per-table the way a cron job bolted onto one service would be. Second, match_phi: true checks each record's payload against SapixDB's own PHI field registry directly, which is exactly the mechanism you want here — given the scenario up top, tickets carry PHI without necessarily being manually tagged for it, so content-based matching is more reliable than requiring every writer to remember a _tag field. If your retention rule is scoped differently — say, only tickets your own code explicitly marks — classification_tag matches a top-level _tag field in the payload instead, and you can set both on the same policy; they combine as AND, not as alternatives.

Here's the detail that's easy to miss and genuinely worth knowing if you're debugging what the sweep actually wrote: when it retires a ticket, it writes both effects in one record — the same TOMBSTONE flag the manual tombstone above uses, and the {"_deleted": true, "_original_hash": ...} marker soft-delete uses — in a single append. It's the exact same combination soft-delete now gives you on demand; the sweep just runs it automatically, on a schedule, instead of waiting for an admin to call it.

What This Actually Changes

None of the above requires a backup restore, a snapshot job, or a favor from whoever owns the logging pipeline. Four questions, four primitives, each with exactly one answer: should this stop appearing (tombstone), does the removal itself need to be provably authorized (soft-delete), do I need to undo a mistake (restore), or do I need to know what something looked like before any of this happened (as_of). Automate the whole category with a Retention Policy the moment "someone remembers to call the right endpoint" stops being an acceptable control.

If "prove this was deleted, when, and by what authority" is a question your team currently answers with a shrug or an afternoon of log archaeology, it's worth trying this on your own data — spin up an agent, write a few records, tombstone one, and watch current immediately stop showing it. No reconstruction, no inference, just a query against data that was always capable of answering it.

Want to learn more?

Free Community edition available at sapixdb.com