DriftDB

Experimental PostgreSQL-Compatible Time-Travel Database (v0.9.1-alpha) - An ambitious temporal database project with advanced architectural designs for enterprise features. Query your data at any point in history using standard SQL.

⚠️ ALPHA SOFTWARE - NOT FOR PRODUCTION USE: This version contains experimental implementations of enterprise features. The codebase compiles cleanly with zero warnings and includes comprehensive CI with security auditing. Many advanced features remain as architectural designs requiring implementation.

🎮 Try the Interactive Demo

Experience DriftDB's time-travel capabilities right now!

cd demo
./run-demo.sh
# Opens at http://localhost:8080

Or simply open demo/index.html in your browser - no installation required!

The interactive demo features:

• Visual time-travel slider to query data at any point in history
• Real-time SQL editor with example queries
• Multiple sample datasets (e-commerce, users, inventory)
• No setup needed - runs entirely in your browser

📖 See demo documentation

🚀 Quick Start

# Start the PostgreSQL-compatible server
./target/release/driftdb-server --data-path ./data

# Connect with any PostgreSQL client
psql -h localhost -p 5433 -d driftdb

# Use standard SQL with time-travel
CREATE TABLE events (id INT PRIMARY KEY, data VARCHAR);
INSERT INTO events (id, data) VALUES (1, 'original');
UPDATE events SET data = 'modified' WHERE id = 1;

-- Query historical state!
SELECT * FROM events FOR SYSTEM_TIME AS OF @SEQ:1;  -- Shows 'original'
SELECT * FROM events;                -- Shows 'modified'

✅ Working Features

Full SQL Support

• All 5 standard JOIN types: INNER, LEFT, RIGHT, FULL OUTER, CROSS (including self-joins)
• Subqueries: IN/NOT IN, EXISTS/NOT EXISTS (including correlated!), scalar subqueries
• Common Table Expressions (CTEs): WITH clause including RECURSIVE CTEs
• Transactions: BEGIN, COMMIT, ROLLBACK with ACID guarantees and savepoint support
• Views: CREATE/DROP VIEW with persistence across restarts
• DDL operations: CREATE TABLE, ALTER TABLE ADD COLUMN, CREATE INDEX, TRUNCATE
• Aggregation functions: COUNT(*), COUNT(DISTINCT), SUM, AVG, MIN, MAX
• GROUP BY and HAVING: Full support for grouping with aggregate filtering
• CASE WHEN expressions: Conditional logic in queries
• Set operations: UNION, INTERSECT, EXCEPT
• Multi-row INSERT: INSERT INTO ... VALUES (row1), (row2), ...
• Foreign key constraints: Referential integrity enforcement
• Time-travel queries: FOR SYSTEM_TIME AS OF for querying historical states

Core Database Engine

• Event sourcing: Every change is an immutable event with full history
• Time-travel queries: Query any historical state by sequence number
• ACID compliance: Full transaction support with isolation levels
• CRC32 verification: Data integrity on every frame
• Append-only storage: Never lose data, perfect audit trail
• JSON documents: Flexible schema with structured data

Tested & Verified

• ✅ Python psycopg2 driver
• ✅ Node.js pg driver
• ✅ JDBC PostgreSQL driver
• ✅ SQLAlchemy ORM
• ✅ Any PostgreSQL client

🎯 Perfect For

• Debugging Production Issues: "What was the state when the bug occurred?"
• Compliance & Auditing: Complete audit trail built-in, no extra work
• Data Recovery: Accidentally deleted data? It's still there!
• Analytics: Track how metrics changed over time
• Testing: Reset to any point, perfect for test scenarios
• Development: Branch your database like Git

✨ Core Features

SQL:2011 Temporal Queries (Native Support)

• FOR SYSTEM_TIME AS OF: Query data at any point in time
• FOR SYSTEM_TIME BETWEEN: Get all versions in a time range
• FOR SYSTEM_TIME FROM...TO: Exclusive range queries
• FOR SYSTEM_TIME ALL: Complete history of changes
• System-versioned tables: Automatic history tracking

Data Model & Storage

• Append-only storage: Immutable events preserve complete history
• Time travel queries: Standard SQL:2011 temporal syntax
• ACID transactions: Full transaction support with isolation levels
• Secondary indexes: B-tree indexes for fast lookups
• Snapshots & compaction: Optimized performance with compression

Enterprise Features (In Progress)

The following features have been architecturally designed with varying levels of implementation:

• Row-Level Security: Policy-based access control with SQL injection protection
• MVCC Isolation: Multi-version concurrency control with SSI write-skew detection
• Query Optimizer: Cost-based optimization with join reordering and index selection
• Point-in-Time Recovery: Restore database to any timestamp
• Alerting System: Real-time metrics monitoring with configurable alerts
• Authentication & Authorization: RBAC with user management, constant-time password comparison, PBKDF2-HMAC-SHA256 key derivation
• Encryption at Rest: AES-256-GCM encryption (partial)
• Performance Regression Detection: CI-integrated benchmark comparison

Working Infrastructure

• Connection pooling: Thread-safe connection pool with RAII guards
• Health checks: Basic metrics endpoint
• Rate limiting: Token bucket algorithm for connection limits

Query Features (Partially Working)

• B-tree indexes: Secondary indexes for fast lookups (functional)
• Basic query planner: Simple execution plans (working)
• Prepared statements: Statement caching (functional)

Planned Query Optimization (Design Phase)

• Advanced Query Optimizer: Cost-based optimization design (not implemented)
• Join Strategies: Theoretical star schema optimization (code incomplete)
• Subquery Optimization: Flattening algorithms designed (not functional)
• Materialized Views: Architecture planned (not implemented)
• Parallel Execution: Threading design (not operational)

Quick Start

Docker Installation (Recommended)

# Quick start with Docker
git clone https://github.com/driftdb/driftdb
cd driftdb
./scripts/docker-quickstart.sh

# Connect to DriftDB
psql -h localhost -p 5433 -d driftdb -U driftdb
# Set DRIFTDB_PASSWORD env var, or check server logs for generated password

Manual Installation

# Clone and build from source
git clone https://github.com/driftdb/driftdb
cd driftdb
make build

# Or install with cargo
cargo install driftdb-cli driftdb-server

60-Second Demo

# Run the full demo (creates sample data and runs queries)
make demo

# Demo includes:
# - Database initialization
# - Table creation with 10,000 sample orders
# - SELECT queries with WHERE clauses
# - Time-travel queries (FOR SYSTEM_TIME AS OF @SEQ:N)
# - Snapshot and compaction operations

PostgreSQL-Compatible Server

DriftDB now includes a PostgreSQL wire protocol server, allowing you to connect with any PostgreSQL client:

# Start the server
./target/release/driftdb-server

# Connect with psql
psql -h 127.0.0.1 -p 5433 -d driftdb -U driftdb

# Connect with any PostgreSQL driver (set DRIFTDB_PASSWORD or check logs)
postgresql://driftdb:<password>@127.0.0.1:5433/driftdb

The server supports:

• PostgreSQL wire protocol v3
• SQL queries with automatic temporal tracking
• Authentication (MD5 and SCRAM-SHA-256 with constant-time verification)
• Integration with existing PostgreSQL tools and ORMs

Manual CLI Usage

# Initialize database
driftdb init ./mydata

# Check version
driftdb --version

# Execute SQL directly
driftdb sql -d ./mydata -e "CREATE TABLE users (id INTEGER, email VARCHAR, status VARCHAR, PRIMARY KEY (id))"

# Or use interactive SQL file
driftdb sql -d ./mydata -f queries.sql

-- Create a temporal table
CREATE TABLE users (
    id INTEGER,
    email VARCHAR,
    status VARCHAR,
    created_at VARCHAR,
    PRIMARY KEY (id)
);

-- Insert data
INSERT INTO users VALUES (1, 'alice@example.com', 'active', CURRENT_TIMESTAMP);

-- Standard SQL queries with WHERE clauses
SELECT * FROM users WHERE status = 'active';
SELECT * FROM users WHERE id > 100 AND status != 'deleted';

-- UPDATE with conditions
UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01';

-- DELETE with conditions (soft delete preserves history)
DELETE FROM users WHERE status = 'inactive' AND created_at < '2023-01-01';

-- Time travel query (SQL:2011)
SELECT * FROM users
FOR SYSTEM_TIME AS OF '2024-01-15T10:00:00Z'
WHERE id = 1;

-- Query all historical versions
SELECT * FROM users
FOR SYSTEM_TIME ALL
WHERE id = 1;

-- Query range of time
SELECT * FROM users
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-01-31'
WHERE status = 'active';

-- Advanced SQL Features (v0.6.0)
-- Column selection
SELECT name, email FROM users WHERE status = 'active';

-- Aggregation functions
SELECT COUNT(*) FROM users;
SELECT COUNT(email), AVG(age) FROM users WHERE status = 'active';
SELECT MIN(created_at), MAX(created_at) FROM users;

-- GROUP BY and aggregations
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT department, AVG(salary), MIN(salary), MAX(salary)
FROM employees GROUP BY department;

-- HAVING clause for group filtering
SELECT department, AVG(salary) FROM employees
GROUP BY department HAVING AVG(salary) > 50000;

-- ORDER BY and LIMIT
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT name, email FROM users WHERE status = 'active'
ORDER BY name ASC LIMIT 5;

-- Complex queries with all features
SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2023-01-01'
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY AVG(salary) DESC
LIMIT 5;

SQL:2011 Temporal Syntax

Standard Temporal Queries

-- AS OF: Query at a specific point in time
SELECT * FROM orders
FOR SYSTEM_TIME AS OF '2024-01-15T10:30:00Z'
WHERE customer_id = 123;

-- BETWEEN: All versions in a time range (inclusive)
SE