Temporal Tables & Events

TemporalSQL:2011Streaming

Stable On by default · production-ready

What it is

AetheriusDB records every commit as an immutable, versioned state. That gives the database a clean notion of “time”: every committed transaction has a unique point on the timeline, and every row has a precise range of moments during which it was the truth. The temporal SQL surface exposes that structure with the standard SQL:2011 syntax for system-versioned tables — FOR SYSTEM_TIME AS OF, BETWEEN, and FROM … TO.

On the same engine, event-pattern detection over ordered data is exposed through MATCH_RECOGNIZE — the SQL standard for expressing sequential patterns over rows, such as “three failed logins followed by a password reset.” The same query that scans a historical snapshot can also scan a live event log.

Why it matters

Most databases bolt time on after the fact — a separate history table, a custom trigger, or a third-party CDC pipeline. Because AetheriusDB’s storage is naturally versioned, time queries cost about the same as live queries.

No history tables, no triggers, no CDC pipeline — every commit is already a snapshot you can address.
Low overhead for time travel — the engine routes the query to the historical snapshot at planning time, then executes it on the same path as a live query.
Native event-pattern detection — write streaming analytics in standard SQL instead of maintaining a separate stream-processing job.

flowchart LR
  classDef now fill:#1f2640,stroke:#4ade80,color:#4ade80
  classDef past fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef q fill:#1f2640,stroke:#00d4ff,color:#00d4ff

  subgraph TL[Timeline of committed snapshots]
    direction LR
    T0[2025-01-01]:::past --> T1[2025-06-15]:::past --> T2[2025-09-01]:::past --> TN[NOW]:::now
  end

  Q1[SELECT … AS OF '2025-01-01']:::q --> T0
  Q2[SELECT … AS OF '2025-09-01']:::q --> T2
  Q3[SELECT … current]:::q --> TN

How you use it

Temporal queries are a small extension to your existing SQL. Add a FOR SYSTEM_TIME clause and the same SELECT runs against historical state. MATCH_RECOGNIZE works on any table or view that has a natural ordering column.

-- What did the orders table look like on New Year's Day?
SELECT *
FROM orders
FOR SYSTEM_TIME AS OF TIMESTAMP '2025-01-01 00:00:00';

-- All states between two dates.
SELECT *
FROM accounts
FOR SYSTEM_TIME FROM TIMESTAMP '2025-06-01'
                TO   TIMESTAMP '2025-06-30';

-- Streaming event pattern: detect brute-force login attempts.
SELECT user_id, attempt_start, breached_at
FROM auth_events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY ts
  MEASURES
    FIRST(fail1.ts) AS attempt_start,
    reset.ts        AS breached_at
  PATTERN (fail1 fail2 fail3 reset)
  DEFINE
    fail1 AS event_type = 'LOGIN_FAIL',
    fail2 AS event_type = 'LOGIN_FAIL',
    fail3 AS event_type = 'LOGIN_FAIL',
    reset AS event_type = 'PASSWORD_RESET'
);

The temporal queries return historical state as if you had read the table live at that moment. The MATCH_RECOGNIZE query scans the ordered rows and emits one row per matched pattern — no external stream processor required.

Key concepts

Concept	Meaning
System versioning	Every committed change creates an addressable point on a timeline.
`AS OF` query	Read a table as it existed at a specific past instant.
`FROM … TO` query	Read every version that was active during a range of time.
`MATCH_RECOGNIZE`	Standard SQL clause for detecting sequential patterns in ordered rows.

Notes & caveats

Chrono Replay Debugger →

A timeline UI that uses temporal queries to rewind running applications.

Storage Lifecycle →

How old snapshots tier down to cold storage and stay queryable.

Storage →

How committed state is kept durable and addressable over time.