DEV Community: Wonder-David Efe

How do I answer "what did my data look like last month" in Postgres?

Wonder-David Efe — Thu, 09 Jul 2026 18:06:54 +0000

Sooner or later, someone asks: "What did this customer's plan look like when they signed up last February?" The current row won't tell you that; it's been edited three times since. This is the classic historical/temporal data problem: how do you manage changes to dimensional data over time so you can answer "row as of T" on demand. This article is about the schema pattern that answers that question in one SELECT.

There are several approaches to modeling and solving this problem, and I'll discuss some of those approaches below:

a) Event-driven/event sourcing. This has to do with when the domain naturally consists of discrete meaningful facts, e.g., sensor readings, prescriptions, stock movements, financial transactions. Here the change of records events is the natural state and the database is designed for recording these events. It is a Bad fit when the domain thinks in records (customers, products, contracts) whose changes don't have natural event semantics.

b) Versioning in place (this is what SCD Type 2 refers to, if you want to look it up — SCD explainer). The database table holds every version of every record with valid_from / valid_to denoting the change time; the domain still thinks in records. Getting the records state at a certain point in time requires filtering for versions in the valid from/to window. Great when versions have domain meaning. Bad fit when the app is already built; adding versioning in place means every existing query, view, and join needs a WHERE valid_to IS NULL filter. In greenfield the choice between this and a sibling history table (SCD Type 4) is close, and comes down to whether "one table with a filter convention" or "current and history split" fits the team's mental model.

c) Snapshotting a full copy on some cadence or event. Nightly snapshots into a separate table; or freeze a snapshot into a downstream entity when a business event happens (approval, invoice). This is efficient when the ask is anchored to those specific moments and storage is tight. When asking for arbitrary times ("what did this look like Tuesday at 15:37") and for records that change often, you get the values at snapshot time and miss a lot of the changes in between snapshots.

d) Slowly Changing Dimension (SCD) Type 4. In SCD Type 4, you solve by creating a history table in addition to your main table.

The SQL below is Postgres; the pattern translates to any RDBMS that supports row-level triggers, just with different syntax for the trigger body and the point-in-time query.

So you originally have your main table:

CREATE TABLE records (
    record_id  BIGSERIAL PRIMARY KEY,
    -- your app's existing columns
);

Alongside it, you create a history table that mirrors the same shape plus a few bookkeeping columns:

CREATE TABLE records_history (
    history_id  BIGSERIAL PRIMARY KEY,
    valid_from  TIMESTAMPTZ NOT NULL,
    valid_to    TIMESTAMPTZ,
    operation   TEXT NOT NULL,
    LIKE records INCLUDING DEFAULTS
);
CREATE INDEX ON records_history (record_id, valid_from DESC);

LIKE records INCLUDING DEFAULTS copies every column from records. The history table auto-inherits the schema. Only the currently-valid version of a record has valid_to = NULL; older versions have their valid_to filled in with the moment the next edit closed them out.

Every INSERT, UPDATE, and DELETE on records needs to be mirrored into records_history. A row-level trigger keeps them in sync so the app itself never has to write to two tables:

CREATE FUNCTION records_history_trg() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP IN ('UPDATE','DELETE') THEN
        UPDATE records_history
           SET valid_to = now()
         WHERE record_id = OLD.record_id AND valid_to IS NULL;
    END IF;
    IF TG_OP = 'DELETE' THEN
        INSERT INTO records_history
        SELECT nextval('records_history_history_id_seq'),
               now(), now(), 'DELETE', OLD.*;
        RETURN OLD;
    END IF;
    INSERT INTO records_history
    SELECT nextval('records_history_history_id_seq'),
           now(), NULL, TG_OP, NEW.*;
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER records_history_trg
AFTER INSERT OR UPDATE OR DELETE ON records
FOR EACH ROW EXECUTE FUNCTION records_history_trg();

On every UPDATE or DELETE the previously-current version has its valid_to stamped to now(). On INSERT or UPDATE a fresh row is written with valid_from = now() and valid_to = NULL; the new current version. On DELETE we also write a tombstone row with valid_from = valid_to = now() so we can tell later that the record ceased to exist at exactly that moment.

With this in place, asking "what did the table look like on date T?" is one query:

SELECT DISTINCT ON (record_id) *
FROM records_history
WHERE valid_from <= :as_of
  AND (valid_to IS NULL OR valid_to > :as_of)
  AND operation != 'DELETE'
ORDER BY record_id, valid_from DESC;

For each record we pick the version whose validity window contains :as_of, and we skip records that were deleted before that moment. The DISTINCT ON (record_id) with ORDER BY record_id, valid_from DESC gives you one row per record, the most recent one that was valid at :as_of which is exactly what a SELECT * FROM records would have returned if you'd run it at that moment.

One thing to handle at install time: if rows existed before the trigger was added, they'll have no history entries yet. Backfill once:

INSERT INTO records_history (valid_from, valid_to, operation, ...)
SELECT now(), NULL, 'BOOTSTRAP', r.*
FROM records r;

Every existing record now has a BOOTSTRAP version starting at install. Point-in-time queries before that return empty (which is honest as you didn't have history yet). Anything on or after is queryable.

Two things to bake into ops before you ship. Schema drift. When you add a column to records, records_history doesn't gain it automatically. LIKE ... INCLUDING DEFAULTS copies the schema at CREATE time, not on future changes. The trigger's NEW.* copy will fail on the column-count mismatch until you ALTER TABLE records_history ADD COLUMN to match. Bake that ALTER into whatever automation runs your migrations so nobody has to remember.

Filesystem for AI Agents: What I Learned Building One

Wonder-David Efe — Thu, 02 Apr 2026 22:08:48 +0000

Filesystem for AI Agents: What I Learned Building One

Most agentic systems, like Claude Code, that run on laptops and servers, interact with files natively through bash. But building an agentic system that allows users to upload and work with files comes with its own limitations that make you unable to store files on the server the agent runs on, and give the agent the bash tool:

The fact that it's exposed to users anywhere — bad actors can get it to run commands that can crash the server or exploit other stuffs, so you want only file operations
Even if you allow only file operations, you can't store every user's files on the server due to storage limits, so you'll have to store files in remote storage like S3 or Azure — but mounting them will make native commands like grep slow, as it has to download the full file first
Even if you had unlimited storage and didn't need mounting, you still need isolation — where the agent cannot access files uploaded by another user, or by the same user in another session

There are other solutions to these problems, but they each come with their own tradeoffs:

VM/sandbox platforms (E2B, Northflank) — spin up an isolated environment per conversation, which solves security and isolation. But they have cold start latency, operational overhead, and cost that compound at scale. You're managing servers again, just indirectly.
S3 mounting (mountpoint-s3, JuiceFS, s3fs) — mount remote object storage as a local filesystem. Grep and similar commands work, but inefficiently — each scan triggers sequential HTTP range requests that essentially download the whole file in chunks. Too slow for agent workloads.
just-bash (Vercel Labs) — a TypeScript reimplementation of bash with a pluggable filesystem backend. Closest to what I wanted, but TypeScript only. My pipeline is Python.
Localsandbox (CoPlane) — Python wrapper around just-bash, which would have solved the language problem. But it bridges Python to just-bash via a Deno runtime, adding a deployment dependency I didn't want in a Celery environment.

I ran into this problem recently while building a legal AI agentic system where users had to upload files for the agent to work with. The solution I needed had to be database-like storage that doesn't need to be spun up and down like a server, but supports native file operations that can be exposed as tools to the agent, with the agent unable to access anything outside its own scoped workspace.

Then I found AgentFS — a filesystem built specifically for AI agents, backed by Turso/SQLite. It provides scoped, isolated storage per user and session, with file operations that can be wired directly as agent tools.

Of the integration options — Python SDK, AgentFS + just-bash, AgentFS + FUSE — I went with the Python SDK. Unlike FUSE, which gives the agent a real mount but leaves the rest of the server exposed, the Python SDK puts you in full control. The agent can only do what you explicitly wire up as a tool. No shell escape, no arbitrary commands, no environment variable leaks. The isolation is in the design, not bolted on afterward.

The trade-off is that you're responsible for the tool surface. The SDK ships with the basics — read, write, list — but search operations were missing. No grep, no find, no wc. For an agent that needs to navigate files without dumping everything into context, those aren't optional. So I built them and raised a PR to have them integrated directly into the SDK.

AgentFS relies on Turso DB for hosted production use. Locally, the pattern already works — one SQLite file per user, each opened independently with full read-write access. But on a production server, you can't manage hundreds of separate database files manually. You need a single server process that can route connections to the right user's database.

Turso Cloud solves part of this — it supports creating thousands of separate databases and even lets you query across them using ATTACH. But attached databases are currently read-only. You can read from multiple user databases in one session, but you can't write to them. For an agentic system where the agent needs to create, modify, and delete files in a user's scoped workspace, read-only access isn't enough.

Turso has confirmed that full read-write ATTACH support is on their roadmap. On the AgentFS side, the open() call goes through a connect() function that can be pointed at a Turso-managed database instead of a local file — so the SDK integration path is straightforward once Turso ships the write support. Until then, full production multi-user AgentFS is blocked on this upstream feature.