differential

INSTALL differential FROM community;
LOAD differential;

-- Run in order in one DuckDB session, using fresh demo paths and database names.
-- Replace the absolute paths below; the S3 section also needs an existing bucket
-- and AWS credentials (environment variables, a profile, SSO or an instance role).
INSTALL differential FROM community;
LOAD differential;
SET differential_repository = '/absolute/path/differential-demo/repository';

-- LOCAL STORAGE: create, snapshot, read history, branch, clone, restore and clean up.
-- TYPE differential secrets hold storage defaults, never credentials.
CREATE SECRET local_storage (
    TYPE differential,
    DATA_PATH '/absolute/path/differential-demo/local-data'
);
ATTACH 'differential:sales' AS sales (SECRET local_storage);
CREATE TABLE sales.orders (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO sales.orders VALUES (1, 10), (2, 20);
CALL differential_create_snapshot('sales', name => 'baseline',
    message => 'initial load', request_id => 'sales-baseline'); -- snapshot 1
INSERT INTO sales.orders VALUES (3, 30);
CALL differential_create_snapshot('sales', name => 'three-orders'); -- snapshot 2

SELECT sum(amount) FROM sales.orders;                       -- 60
SELECT sum(amount) FROM sales.orders AT (VERSION => 1);     -- 30
ATTACH 'differential:sales' AS sales_history (SNAPSHOT_NAME 'baseline');
SELECT count(*) FROM sales_history.orders;                  -- 2
SELECT snapshot_id, snapshot_name FROM sales.snapshots();
DETACH sales_history;

-- A branch shares the database identity; a clone is an independent database.
CALL differential_create_branch('sales', 'dev', snapshot => 1);
ATTACH 'differential:sales' AS sales_dev (BRANCH 'dev');
INSERT INTO sales_dev.orders VALUES (4, 40);
CALL differential_create_snapshot('sales_dev', message => 'dev order'); -- snapshot 3
SELECT sum(amount) FROM sales_dev.orders;                   -- 70
DETACH sales_dev;
CALL differential_clone_database('sales', 'sales_copy', snapshot => 1);
ATTACH 'differential:sales_copy' AS sales_copy;
SELECT sum(amount) FROM sales_copy.orders;                  -- 30
DETACH sales_copy;

-- Merge dev back into main: a three-way merge against their common ancestor
-- (snapshot 1). main added order 3 and dev added order 4, so nothing conflicts.
SELECT base_snapshot_id, ours_snapshot_id, theirs_snapshot_id, conflicts, validation_state
FROM differential_merge_branch_preview('sales', 'main', 'dev', validate => true); -- 1, 2, 3, 0, valid
-- Merging into an existing branch (destination = ours) needs it detached.
-- DETACH also releases the hidden reader pins taken by AT queries.
DETACH sales;
SELECT snapshot_id, merge_action FROM differential_merge_branch('sales', 'main', 'dev', 'main',
    request_id => 'sales-merge-dev');                       -- snapshot 4, merged
ATTACH 'differential:sales' AS sales;
SELECT sum(amount) FROM sales.orders;                       -- 100
DETACH sales;

-- Restore also requires a detached branch and preserves its previous contents.
CALL differential_restore('sales', 1);                      -- restored snapshot 5
ATTACH 'differential:sales' AS sales;
SELECT sum(amount) FROM sales.orders;                       -- 30
DETACH sales;
CALL differential_drop_branch('sales', 'dev');
CALL differential_expire_snapshots('sales', versions => [2, 3, 4], dry_run => true);
CALL differential_expire_snapshots('sales', versions => [2, 3, 4]);
CALL differential_cleanup_old_files(cleanup_all => true);
CALL differential_delete_orphaned_files(dry_run => true);
CALL differential_compact();
-- The main branch, baseline snapshot and independent clone remain usable.

-- S3 STORAGE: credentials, exports, history, remote reads and recovery.
-- Replace the bucket/prefix and region. The bucket must already exist.
INSTALL httpfs;
LOAD httpfs;
INSTALL aws;
LOAD aws;
-- TYPE s3 supplies credentials by matching SCOPE; these secrets are session-scoped.
-- REFRESH auto keeps temporary credentials current through the credential chain.
CREATE SECRET s3_auth (
    TYPE s3,
    PROVIDER credential_chain,
    REFRESH auto,
    REGION 'us-east-1',
    SCOPE 's3://my-bucket/differential-demo/'
);
CREATE SECRET s3_storage (
    TYPE differential,
    DATA_PATH 's3://my-bucket/differential-demo/'
);
ATTACH 'differential:archive' AS archive (SECRET s3_storage);
CREATE TABLE archive.events (id INTEGER PRIMARY KEY, payload VARCHAR);
INSERT INTO archive.events VALUES (1, 'one'), (2, 'two');
SELECT snapshot_id, export_state, export_locator
FROM differential_create_snapshot('archive', name => 'baseline',
    request_id => 'archive-baseline');                     -- snapshot 1, complete
-- Save the immutable export URI for recovery later in this session.
SET VARIABLE baseline_export = (
    SELECT 's3://my-bucket/differential-demo/' || export_locator
    FROM differential_snapshots('archive') WHERE snapshot_id = 1
);

-- Subsequent exports share unchanged data. Deferred exports can be retried safely.
INSERT INTO archive.events VALUES (3, 'three');
CALL differential_create_snapshot('archive', name => 'three-events',
    export_mode => 'deferred');                            -- snapshot 2, pending
CALL differential_retry_export('archive', 2);              -- snapshot 2, complete
SELECT snapshot_id, export_state FROM archive.snapshots();
SELECT count(*) FROM archive.events AT (VERSION => 1);     -- 2

-- Any host with the same scoped S3 credentials can open this branch head read-only.
-- It sees the latest exported snapshot at ATTACH time; reattach to refresh it.
ATTACH 'differential:s3://my-bucket/differential-demo/heads/archive/main' AS archive_latest;
SELECT count(*) FROM archive_latest.events;                 -- 3
DETACH archive_latest;
SELECT database_name, branch_name, snapshot_id
FROM differential_remote_heads('s3://my-bucket/differential-demo/');
DETACH archive;

-- Evict only an export-complete snapshot. Historical reads then fetch its data from S3.
CALL differential_evict_snapshots('archive', versions => [1]);
CALL differential_cleanup_old_files(cleanup_all => true);
ATTACH 'differential:archive' AS archive_history (SNAPSHOT_VERSION 1);
SELECT count(*) FROM archive_history.events;                -- 2
DETACH archive_history;

-- Recover the saved immutable export into a new writable LOCAL database.
-- The named differential secret chooses its destination; s3_auth reads the source.
CALL differential_recover_export(getvariable('baseline_export'), 'archive_recovered',
    secret => 'local_storage');
ATTACH 'differential:archive_recovered' AS archive_recovered;
SELECT count(*) FROM archive_recovered.events;              -- 2
INSERT INTO archive_recovered.events VALUES (9, 'recovered locally');
DETACH archive_recovered;

-- Branch/clone/restore work with S3 too; evicted data is rehydrated when needed.
-- Branches and clones inherit the source's S3 data path.
CALL differential_create_branch('archive', 'dev', snapshot => 1);
ATTACH 'differential:archive' AS archive_dev (BRANCH 'dev');
INSERT INTO archive_dev.events VALUES (4, 'dev');
CALL differential_create_snapshot('archive_dev');           -- snapshot 3, complete
DETACH archive_dev;
CALL differential_clone_database('archive', 'archive_copy', snapshot => 1);
ATTACH 'differential:archive_copy' AS archive_copy;
SELECT count(*) FROM archive_copy.events;                   -- 2
DETACH archive_copy;
CALL differential_restore('archive', 1);                    -- snapshot 4, complete
ATTACH 'differential:archive' AS archive;
SELECT count(*) FROM archive.events;                        -- 2
DETACH archive;

CALL differential_drop_branch('archive', 'dev');
CALL differential_expire_snapshots('archive', versions => [2, 3], dry_run => true);
CALL differential_expire_snapshots('archive', versions => [2, 3]);
CALL differential_cleanup_old_files(cleanup_all => true);
CALL differential_compact();
-- Expiry and garbage collection reclaim unreferenced LOCAL data, not S3 objects.
-- Existing immutable S3 exports remain readable; the main branch and clone remain.
SELECT database_name, data_path FROM differential_databases();
SELECT kind, state FROM differential_operations();
DROP SECRET s3_storage;
DROP SECRET s3_auth;
DROP SECRET local_storage;