---
title: "Temporal databases and AI agent memory"
description: "What a temporal database records beyond current state, the main implementations, and what AI agent memory needs on top of temporal tables."
canonical: https://past.dev/guides/temporal-database
last-updated: 2026-09-02
---
# Temporal databases and AI agent memory

Source: https://past.dev/guides/temporal-database

A temporal database stores the history of its data along with the data itself, so queries can ask about any past state rather than only the present. The two standard forms are system-versioned tables, which record when the database held each row version, and application-time tables, which record when each row was true in the world. Agent memory builds on temporal database semantics and adds entity resolution, evidence status, and recall ranking.

## What a temporal database records

A conventional database keeps one row per fact and destroys the previous state on every `UPDATE`. A temporal database keeps every state a row has passed through, each stamped with the period it belongs to. Temporal data is data carrying those period stamps.

The stored history makes three query shapes possible that a conventional table cannot serve: the state of the data as of any past date, the full change history of one row, and the difference between two dates. Deleted rows remain visible in the past, so deletion becomes an event in the history rather than an erasure of it.

History has a storage cost. Every change adds a row version, so temporal tables grow with write rate rather than with entity count. Implementations answer this with history partitioning and retention windows, and a retention window should be a policy decision rather than a reaction to disk usage.

## System-versioned vs application-time tables

SQL:2011 defines two temporal table kinds, one per time axis. They answer different questions and are frequently confused.

| Property | System-versioned table | Application-time table |
| --- | --- | --- |
| Axis recorded | Transaction time: when the database held each row version | Valid time: when the row was true in the world |
| Who maintains it | The database, automatically on every write | The application, through period columns it sets |
| Answers | What did this table say on July 1? | What was true in the world on July 1? |
| Typical uses | Audit, recovery, reproducing a past query | Effective dates, prices, contracts, corrections |

A table can carry both mechanisms, which makes it bitemporal. The [bitemporal data guide](/guides/bitemporal-data) works through why the axes separate and the four query shapes the combination answers.

> **Trap**
>
> System versioning records write time only. It reports what the table contained on a date. Whether the world matched the table on that date is a valid-time question, and it needs its own columns.

## Implementations

Three implementations cover the design space. Each records a different subset of the two axes by default.

- **MariaDB system-versioned tables.** Declaring a table `WITH SYSTEM VERSIONING` keeps every row version and enables `FOR SYSTEM_TIME` queries over transaction time, per the [MariaDB documentation](https://mariadb.com/kb/en/system-versioned-tables/).
- **SQL Server temporal tables.** A system-versioned temporal table pairs the current table with a separate history table the engine maintains, per the [SQL Server documentation](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables).
- **XTDB.** A bitemporal database that stamps every record with valid time and system time and accepts queries as of either axis, per the [XTDB documentation](https://docs.xtdb.com/).

All three version structured rows. None of them extracts facts from prose, resolves entities across sources, or ranks results against a question, which is the gap the next section covers.

## What agent memory needs that temporal tables do not provide

An AI agent remembering emails, transcripts, and tickets needs the temporal semantics above, plus three properties that generic temporal tables do not supply.

- **[Entity resolution](/guides/entity-resolution).** Temporal tables version rows. They do not know that "Acme", "Acme Corp", and "the client" name one company across three sources, so per-source rows never supersede each other and the history fragments by spelling.
- **Evidence status.** A row is either present or absent. An agent needs to know whether an answer is supported by evidence, contradicted between sources, or unsupported, and it needs to see the [conflicting records](/guides/contradictory-facts) when sources disagree.
- **Recall ranking.** SQL answers predicates over columns. An agent asks in prose and needs the most relevant dated evidence ranked, with [supersession](/glossary/fact-supersession) deciding which facts are current. How this retrieval task is evaluated is covered in the [benchmark methodology](/benchmarks/methodology).

## The Postgres pattern

A dedicated temporal engine is rarely a requirement. The common production pattern puts temporal semantics in the data model of a standard database: every fact is a row with a validity period, a recorded-at timestamp, and a supersession pointer, and rows are closed rather than updated. The pattern needs no extensions and ports to any relational engine.

```atemporalfacttableinpostgres
CREATE TABLE fact (
  id            uuid PRIMARY KEY,
  subject_id    uuid NOT NULL,
  predicate     text NOT NULL,
  value         jsonb NOT NULL,
  valid_from    timestamptz NOT NULL,
  valid_to      timestamptz,
  recorded_at   timestamptz NOT NULL DEFAULT now(),
  superseded_by uuid REFERENCES fact(id)
);
```

A change closes `valid_to` on the old row, inserts the new row, and sets `superseded_by`. Range types can replace the two boundary columns with one `tstzrange` column and an exclusion constraint against overlapping windows, per the [PostgreSQL documentation](https://www.postgresql.org/docs/current/rangetypes.html). The [validity window](/glossary/validity-window) entry defines the window semantics.

## Choosing between them

1. Auditing changes to tables one application already owns: turn on system versioning in the database already running them.
2. Business data with effective dates, such as prices and contracts: application-time period columns, maintained by the application.
3. Both axes with engine support, for example regulated reporting over corrected data: a natively bitemporal store, or both SQL:2011 mechanisms on one table.
4. Memory for an AI agent over prose sources: temporal semantics plus entity resolution, evidence status, and ranked recall, which means a memory system rather than bare tables.

past.dev implements the fourth option: facts carry event time, validity windows, and supersession links, and recall returns ranked, dated evidence. Self-hosted deployments run with docker compose and keep memory in the customer's own Postgres, with the same API as managed. The [quickstart](/docs/memory-api/quickstart) covers the API and the [self-hosting guide](/docs/memory-api/self-hosting) covers deployment.

## Frequently asked questions

### What is a temporal database in simple terms?

A database that keeps every past state of its data instead of overwriting it, so you can query the data as it was on any date. Updates close the old version and add a new one rather than destroying anything.

### What is the difference between system-versioned and application-time tables?

A system-versioned table records when the database stored each row version and is maintained automatically. An application-time table records when a row was true in the real world, through period columns the application sets. The first serves audit, the second serves effective dates.

### Do I need a special database for temporal data?

No. The common pattern models validity and recording timestamps as ordinary columns, closes rows instead of updating them, and links each fact to its replacement. Engine-level temporal features automate parts of this but are optional.

### Do AI agents need a temporal database?

Agents that remember facts across sessions need temporal semantics, because facts change and sources arrive late. Those semantics can come from a temporal database or from a temporal data model on a standard one. Agents additionally need entity resolution and ranked, dated recall, which databases alone do not provide.

### Is past.dev a temporal database?

past.dev is a memory API with a temporal model: each fact carries event time, a validity window, and a supersession link. Self-hosted deployments store that memory in the customer's own Postgres.

## Related

- [Bitemporal data](https://past.dev/guides/bitemporal-data)
- [Facts that change over time](https://past.dev/guides/facts-that-change-over-time)
- [Entity resolution](https://past.dev/guides/entity-resolution)
- [Temporal knowledge graph](https://past.dev/glossary/temporal-knowledge-graph)
- [Vector database vs memory](https://past.dev/vector-database-vs-memory)