Temporal Tables
Temporal tables track full row history by pairing a live table with a history table. You set them up by adding period columns and linking a backing archive table. Teams often use this setup to audit data changes and fix broken records. Still, broad database roles let users read old secrets or drop history links. How temporal tables work in a database A standard database table only holds the current state of your data. When an app updates a row, the old values disappear right away. Temporal ta
Tables: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
Temporal tables track full row history by pairing a live table with a history table. You set them up by adding period columns and linking a backing archive table. Teams often use this setup to audit data changes and fix broken records. Still, broad database roles let users read old secrets or drop history links.
How temporal tables work in a database
A standard database table only holds the current state of your data. When an app updates a row, the old values disappear right away. Temporal tables fix this problem by tracking when every change happens. The database uses two timestamp columns to mark when each row version is valid.
One column marks the start time, and the other marks the end time. When a user updates a row, the database moves the old version away. That old row goes into a linked history table with its closing timestamp. The main table then stores the new row version with a fresh start time.
To set this up, an admin creates or alters the main table schema. The database engine can build the history table on its own or link one. Regular queries against the main table only read the current data rows. Special time-travel queries let teams read how data looked at a past moment.
Database roles control who can run these time-travel queries or alter history rules. Admins grant read permissions on both the live table and the history table. Only high-level admin roles should hold permission to turn off system versioning. If versioning stops, someone can edit or delete past audit records without a trace.
What native history tracking solves and what it misses
Native history tracking solves the problem of lost updates and broken change logs. Developers do not need to write custom triggers or app-level audit logic. The database engine handles every insert, update, and delete on its own. This helps teams recover from bad writes and inspect past states with ease.
Still, native database permissions only decide who can log in and read tables. Once a user has read permissions, they can query every column and row by default. History tables often hold years of sensitive customer emails, phone numbers, and card numbers. A single time-travel query can dump millions of historical records in seconds.
Tables: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Native roles also fail to link shared database logins to real human users. Many teams share a single admin or read role across several engineers. When someone turns off versioning or updates a live row, the log shows the role. It does not show which engineer ran the query or why they did it.
Database permissions also cannot inspect or filter live statements in flight. They cannot require a peer check before an engineer alters a versioning rule. They also cannot mask sensitive fields like national IDs inside query results on the fly. Teams need runtime controls that inspect every statement and rewrite sensitive data fields.
Best practices for managing history tables
Teams should separate permissions for current tables from permissions for history tables. Most app services only need to read and write the current live rows. Keep history read permissions limited to specific audit roles and debugging workflows. This shrinks the risk of a wide data leak from old records.
You should also set up a clear data retention rule for old rows. History tables grow fast when an app runs frequent updates on large rows. Old data can fill up disk space and slow down historical reads. Plan regular cleanups for history rows that pass your retention window.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev runs beside those services as an open-source proxy, a sidecar for your Postgres or API. It inspects requests coming in and results going back on the wire.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run. On responses, hoop.dev data masking rewrites matched sensitive values in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
What is the difference between system-versioned and application-time tables?
System-versioned tables let the database engine track exact write timestamps on its own. App-time tables let your app set custom valid dates for business events.
Can users edit rows directly inside a history table?
No, the database blocks direct edits to the history table while versioning stays active. An admin must turn off versioning first to modify or delete old rows.
Do history tables slow down normal read queries?
Current queries only hit the main table, so read speed stays fast. Updates and deletes take a bit more work because the database writes history rows.