SapixDBSapixDB/Docs
Home

Manual · Querying

Time Travel Queries

Query your agent's data as it existed at any moment in the past — without maintaining a separate history table, changelog, or audit log. SapixDB's as_of query makes this possible at zero extra storage cost.

✓ Zero overhead — no extra storageEvery record already carries an immutable timestamp_hlc (a Hybrid Logical Clock value) set at write time. as_of is simply a filter: return records where timestamp_hlc ≤ your_timestamp. No separate history table. No changelog to maintain. No additional writes.

How as_of works

When a record is written to SapixDB, the agent stamps it with a timestamp_hlc value derived from the wall clock at the time of the write. This value is immutable — it never changes after the record is created.

An as_of query provides a cutoff HLC. The query engine returns every record whose timestamp_hlc is less than or equal to that cutoff — which is exactly the set of records that existed at that moment in time. Records written after the cutoff are invisible to the query.

JSON — as_of query shape
{
  "type":          "as_of",
  "timestamp_hlc": 109870428282265600,
  "limit":         100
}
ParameterTypeRequiredDescription
typestringyesAlways "as_of"
timestamp_hlcuint64yesHLC cutoff. Records with timestamp_hlc ≤ this value are returned.
limitintegernoMaximum records to return (default 100)
selectstring[]noField projection — return only named fields in each payload
filterobjectnoAdditional filter applied after the as_of cutoff
⚠ Field name is timestamp_hlc, not ts_hlcThe correct parameter name is timestamp_hlc. Using ts_hlc will cause a validation error.

HLC timestamps

SapixDB uses Hybrid Logical Clocks. An HLC value is a 64-bit unsigned integer. The upper 48 bits are the Unix timestamp in milliseconds; the lower 16 bits are a logical counter used to break ties when multiple writes happen within the same millisecond.

Converting wall time to HLC

To convert a wall-clock moment to an HLC value suitable for as_of, multiply Unix milliseconds by 65536 (= 2^16). This sets the logical counter to zero, meaning the result is the earliest possible HLC for that millisecond — ensuring all records written at or before that moment are included.

Python
import time

# Current moment as HLC
now_hlc = int(time.time() * 1000) * 65536
print(now_hlc)  # e.g. 109870428282265600

# A specific past moment
import datetime
dt = datetime.datetime(2026, 6, 1, 0, 0, 0, tzinfo=datetime.timezone.utc)
past_hlc = int(dt.timestamp() * 1000) * 65536
print(past_hlc)
TypeScript
// Current moment
const nowHlc = BigInt(Date.now()) * 65536n;

// A specific past moment
const dt = new Date("2026-06-01T00:00:00Z");
const pastHlc = BigInt(dt.getTime()) * 65536n;

Decoding an HLC back to wall time

Python
hlc = 109870428282265600

# Extract milliseconds (shift right 16 bits)
unix_ms  = hlc >> 16           # bit shift
unix_ms2 = hlc // 65536        # integer division — same result

import datetime
wall = datetime.datetime.fromtimestamp(unix_ms / 1000, tz=datetime.timezone.utc)
print(wall)  # 2026-06-27T12:34:56+00:00

Step-by-step: write → capture → write → query

The canonical pattern for testing time travel: write record A, capture the HLC at that moment, write record B, then run as_of with the midpoint. Only record A should appear.

bash
# Step 1 — Write record A
HASH_A=$(curl -s -X POST http://localhost:7475/v1/agents/inventory/records/json \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{"data": {"item": "widget", "qty": 100}}' \
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(d['content_hash'])")

echo "Wrote A: $HASH_A"

# Step 2 — Capture HLC between A and B
MIDPOINT_HLC=$(python3 -c "import time; print(int(time.time()*1000)*65536)")
echo "Midpoint HLC: $MIDPOINT_HLC"

# Step 3 — Write record B (after the midpoint)
curl -s -X POST http://localhost:7475/v1/agents/inventory/records/json \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{"data": {"item": "widget", "qty": 75, "note": "sold 25 units"}}' \
  | python3 -m json.tool

# Step 4 — as_of MIDPOINT — only record A is visible
curl -s -X POST http://localhost:7475/v1/agents/inventory/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d "{\"type\": \"as_of\", \"timestamp_hlc\": $MIDPOINT_HLC, \"limit\": 100}" \
  | python3 -m json.tool

The response to step 4 will contain only the record written in step 1. The updated record from step 3 is invisible because its timestamp_hlc is greater than the midpoint.

as_of vs time_range

Query typeWhat it returnsTypical use
as_ofAll records with timestamp_hlc ≤ cutoff — a snapshot of the entire agent state at that momentCompliance snapshot, AI replay, point-in-time restore
time_rangeRecords written between a start and end HLC — a window of writesAudit log of changes in a period, incremental sync, recent activity feed

Production patterns

Daily compliance snapshot

Regulators often require that you can reproduce exactly what data you held at the end of a business day. Compute the HLC for 23:59:59 UTC of the target date and run as_of. No snapshot job. No S3 export. Query on demand.

Python — end-of-day HLC
import datetime

def end_of_day_hlc(year: int, month: int, day: int) -> int:
    dt = datetime.datetime(year, month, day, 23, 59, 59, 999000,
                           tzinfo=datetime.timezone.utc)
    return int(dt.timestamp() * 1000) * 65536

cutoff = end_of_day_hlc(2026, 6, 30)
print(f"End-of-day HLC for 2026-06-30: {cutoff}")
curl — GDPR snapshot for 2026-06-30
CUTOFF=$(python3 -c "
import datetime
dt = datetime.datetime(2026, 6, 30, 23, 59, 59, 999000, tzinfo=datetime.timezone.utc)
print(int(dt.timestamp() * 1000) * 65536)
")

curl -s -X POST http://localhost:7475/v1/agents/customers/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d "{"type": "as_of", "timestamp_hlc": $CUTOFF, "limit": 10000}" \
  | python3 -m json.tool

Replaying what an AI agent saw at decision time

When an AI agent makes a decision, record the HLC at the moment of that decision alongside the output. Later, you can replay exactly the data context the agent had — essential for debugging incorrect decisions or satisfying audit requirements.

Python — record AI decision with HLC
import time, json, requests

# Before querying context
decided_at_hlc = int(time.time() * 1000) * 65536

# Fetch context the agent actually saw
context = requests.post(
    "http://localhost:7475/v1/agents/customers/query",
    json={"type": "as_of", "timestamp_hlc": decided_at_hlc, "limit": 100},
    headers={"Authorization": "Bearer spx_root_YOUR_ROOT_KEY"},
).json()

decision = run_ai_model(context["records"])

# Store decision with the HLC so it can be replayed
requests.post(
    "http://localhost:7475/v1/agents/decisions/records/json",
    json={
        "data": {
            "decision":       decision,
            "decided_at_hlc": decided_at_hlc,
            "context_count":  context["count"],
        }
    },
    headers={"Authorization": "Bearer spx_root_YOUR_ROOT_KEY"},
)
Python — replay that decision later
# Retrieve the stored decision
decision_record = ...  # fetch from decisions agent

# Replay: get exactly the same context the agent saw
replay_context = requests.post(
    "http://localhost:7475/v1/agents/customers/query",
    json={
        "type":          "as_of",
        "timestamp_hlc": decision_record["decided_at_hlc"],
        "limit":         100,
    },
    headers={"Authorization": "Bearer spx_root_YOUR_ROOT_KEY"},
).json()

# replay_context["records"] is identical to what the agent saw
ℹ Why HLC instead of wall clock?Wall clocks can jump backwards (NTP corrections, DST) or be skewed across distributed nodes. HLC values are monotonically increasing within each agent and compare correctly across agents in the same SapixDB mesh, making them safe to store and compare across process restarts.

Combining as_of with filter and select

as_of supports both filter and select. The HLC cutoff is applied first, then the filter narrows the result set, then projection strips fields.

curl — enterprise users as of 2026-06-01, email only
CUTOFF=$(python3 -c "
import datetime
dt = datetime.datetime(2026, 6, 1, 0, 0, 0, tzinfo=datetime.timezone.utc)
print(int(dt.timestamp() * 1000) * 65536)
")

curl -s -X POST http://localhost:7475/v1/agents/users/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d "{
    \"type\":          \"as_of\",
    \"timestamp_hlc\": $CUTOFF,
    \"limit\":         1000,
    \"select\":        [\"user_id\", \"email\"],
    \"filter\": {
      \"field\": \"plan\",
      \"op\":   \"eq\",
      \"value\": \"enterprise\"
    }
  }" | python3 -m json.tool