DDL (Data Definition Language)

-- Create database with optional quota settings
CREATE DATABASE emp_prod WITH (
    max_memory_bytes = 1073741824,
    max_storage_bytes = 10737418240,
    max_qps = 1000,
    max_connections = 100,
    cache_weight = 1,
    priority_class = 'standard',
    maintenance_cpu_pct = 25
);

-- Drop database
DROP DATABASE emp_prod;          -- errors if non-empty
DROP DATABASE emp_prod CASCADE;  -- drops all collections
DROP DATABASE emp_prod FORCE;    -- forces materialization of dependent clones, then drops

-- Rename database
ALTER DATABASE emp_prod RENAME TO emp_prod_old;

-- Set or update quota
ALTER DATABASE emp_prod SET QUOTA (
    max_memory_bytes = 2147483648,
    max_qps = 2000,
    cache_weight = 2
);

-- Enable DML audit
ALTER DATABASE emp_prod SET AUDIT_DML = 'writes';  -- 'none' | 'writes' | 'all'

-- Set idle session timeout
ALTER DATABASE emp_prod SET IDLE_TIMEOUT 1800;  -- seconds; 0 = disabled

-- Clone database
CLONE DATABASE staging FROM emp_prod AS OF SYSTEM TIME 1730000000000;
CLONE DATABASE preview FROM emp_prod AS OF LATEST;

-- Force materialization of a clone (blocks until complete)
ALTER DATABASE staging MATERIALIZE;

-- Mirror database
MIRROR DATABASE emp_eu FROM emp_prod MODE = async;
MIRROR DATABASE emp_eu FROM emp_prod MODE = sync;

-- Promote mirror to writable
ALTER DATABASE emp_eu PROMOTE;

-- Move tenant between databases (offline, requires all sessions drained)
MOVE TENANT acme FROM emp_us TO emp_eu;

-- Set per-tenant quota within a database
ALTER TENANT acme IN DATABASE emp_prod SET QUOTA (
    max_memory_bytes = 536870912,
    max_qps = 500,
    max_connections = 50,
    max_vector_dim = 1536,
    max_graph_depth = 5
);

-- Session management
USE DATABASE emp_prod;  -- or pgwire \c emp_prod
SHOW DATABASES;
SHOW DATABASE LINEAGE FOR staging;
SHOW DATABASE MIRROR STATUS FOR emp_eu;
SHOW DATABASE QUOTA FOR emp_prod;
SHOW DATABASE USAGE FOR emp_prod;
SHOW TENANT QUOTA FOR acme IN DATABASE emp_prod;
SHOW TENANT USAGE FOR acme IN DATABASE emp_prod;
SHOW SESSIONS;
SHOW SESSIONS IN DATABASE emp_prod;
KILL SESSION '550e8400-e29b-41d4-a716-446655440000';
SHOW AUDIT IN DATABASE emp_prod WHERE event_type = 'database_cloned';

-- Role and access
GRANT DATABASE_OWNER ON DATABASE emp_prod TO alice;
GRANT DATABASE_EDITOR ON DATABASE emp_prod TO bob;
GRANT DATABASE_READER ON DATABASE emp_prod TO charlie;
GRANT CREATE COLLECTION ON DATABASE emp_prod TO developers_role;
GRANT SELECT ON DATABASE emp_prod TO analysts_role;
ALTER USER alice SET DEFAULT DATABASE emp_prod;

-- API keys with database scope
CREATE API KEY prod_key FOR alice WITH DATABASES (emp_prod, emp_staging);
ALTER API KEY prod_key SET DATABASES (emp_prod);
SHOW API KEYS;

-- Service accounts with database scope
CREATE SERVICE ACCOUNT sa_prod FOR DATABASE emp_prod;
ALTER SERVICE ACCOUNT sa_prod SET DATABASES (emp_prod, emp_staging);
DROP SERVICE ACCOUNT sa_prod;

-- OIDC / SSO providers
CREATE OIDC PROVIDER corp_sso WITH (
    issuer = 'https://idp.corp.com',
    jwks_uri = 'https://idp.corp.com/.well-known/jwks.json',
    audience = 'nodedb-prod'
);
ALTER OIDC PROVIDER corp_sso SET CLAIM MAPPING (
    { claim: 'groups', value: 'eng', effect: 'add_databases = (emp_prod)' }
);
DROP OIDC PROVIDER corp_sso;
SHOW OIDC PROVIDERS;

-- Schemaless document (default)
CREATE COLLECTION users;

-- Strict document
CREATE COLLECTION orders (
    id TEXT PRIMARY KEY, customer_id TEXT, total FLOAT, status TEXT, created_at TIMESTAMP
) WITH (engine='document_strict');

-- Key-Value
CREATE COLLECTION sessions (key TEXT PRIMARY KEY) WITH (engine='kv');

-- Columnar (plain analytics)
CREATE COLLECTION logs (ts TIMESTAMP TIME_KEY, host VARCHAR, level VARCHAR, message VARCHAR)
    WITH (engine='columnar');

-- Timeseries (peer engine, append-only with retention + continuous aggregates)
CREATE COLLECTION metrics (ts TIMESTAMP TIME_KEY, host VARCHAR, cpu FLOAT)
    WITH (engine='timeseries', partition_by='1h', retention='90d');

-- Spatial (peer engine, R-tree + geohash + OGC predicates)
CREATE COLLECTION locations (geom GEOMETRY SPATIAL_INDEX, name VARCHAR)
    WITH (engine='spatial');

-- Convenience alias for timeseries
CREATE TIMESERIES metrics;

DROP COLLECTION users;          -- soft-delete: tombstoned, still restorable
UNDROP COLLECTION users;        -- restore while within the retention window
DROP COLLECTION users PURGE;    -- admin-only: immediate hard-delete, not restorable
SHOW COLLECTIONS;
DESCRIBE users;

-- Retention window knobs
ALTER SYSTEM SET collection_gc_retention = '7d';
ALTER TENANT acme SET collection_gc_retention = '24h';

-- Inspect the lifecycle
SELECT name, dropped_at, size_bytes_estimate FROM _system.dropped_collections;
SELECT * FROM _system.l2_cleanup_queue;

ALTER TABLE orders ADD COLUMN priority INT;
ALTER COLLECTION orders ADD COLUMN region STRING DEFAULT 'us-east';
ALTER COLLECTION orders DROP COLUMN region;

CONVERT COLLECTION cache TO kv;
CONVERT COLLECTION users TO document_strict;
CONVERT COLLECTION logs TO document_schemaless;

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX ON users(username);
CREATE VECTOR INDEX ON articles METRIC cosine DIM 384 M 16 EF_CONSTRUCTION 200;
CREATE SEARCH INDEX ON articles FIELDS title, body ANALYZER 'english' FUZZY true;
CREATE SPATIAL INDEX ON locations(geom);
CREATE SORTED INDEX lb ON scores (score DESC) KEY player_id;
DROP INDEX idx_email;

-- Rebuild all indexes on a collection (non-blocking concurrent path recommended)
REINDEX CONCURRENTLY articles;

-- Rebuild a specific named index only
REINDEX INDEX idx_email CONCURRENTLY users;

-- Blocking rebuild (holds write lock for the duration — use only in maintenance windows)
REINDEX articles;

-- ASYNC (default): Event Plane, zero write-latency impact
CREATE TRIGGER notify AFTER INSERT ON orders FOR EACH ROW $$
BEGIN INSERT INTO notifications (user_id, message) VALUES (NEW.customer_id, 'Order placed'); END; $$;

-- SYNC: same transaction, ACID
CREATE TRIGGER enforce AFTER UPDATE ON accounts FOR EACH ROW WITH (EXECUTION = SYNC) $$
BEGIN IF NEW.balance < 0 THEN RAISE EXCEPTION 'Negative balance'; END IF; END; $$;

DROP TRIGGER notify ON orders;
SHOW TRIGGERS;

-- SQL expression (inlined, zero overhead)
CREATE FUNCTION full_name(first VARCHAR, last VARCHAR) RETURNS VARCHAR
LANGUAGE SQL IMMUTABLE AS $$ first || ' ' || last $$;

-- Procedural
CREATE PROCEDURE transfer_funds(from_id UUID, to_id UUID, amount DECIMAL) BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;

CALL transfer_funds('acc_a', 'acc_b', 50.00);

CREATE CHANGE STREAM order_events ON orders WITH (URL = 'https://hooks.example.com/orders');
CREATE CONSUMER GROUP processors ON order_events;
CREATE TOPIC alerts WITH (RETENTION = '1 hour');
PUBLISH TO alerts 'message';

CREATE MATERIALIZED VIEW order_stats AS SELECT status, COUNT(*), SUM(total) FROM orders GROUP BY status;
REFRESH MATERIALIZED VIEW order_stats;

CREATE CONTINUOUS AGGREGATE cpu_hourly ON cpu_metrics AS
    SELECT time_bucket('1 hour', ts) AS hour, host, AVG(cpu) FROM cpu_metrics GROUP BY hour, host
WITH (refresh_interval = '1m');

CREATE SCHEDULE nightly_cleanup CRON '0 2 * * *' AS BEGIN
    DELETE FROM sessions WHERE expires_at < now();
END;

BACKUP TENANT acme TO '/backups/acme.bak';
RESTORE TENANT acme FROM '/backups/acme.bak';
RESTORE TENANT acme FROM '/backups/acme.bak' DRY RUN;
PURGE TENANT acme CONFIRM;