Skip to content

API

Jump to bottom
Eric Hanson edited this page Aug 9, 2025 · 3 revisions

Aquameta Bundle System API Reference

The Aquameta Bundle System provides Git-like version control for database rows using JSONB-based meta identifiers. This document serves as a comprehensive API reference for developers using the bundle system.

Overview

The bundle system operates on these core concepts:

  • Repositories: Version control containers (like Git repos)
  • Commits: Immutable snapshots of tracked data
  • Tracking: Managing which rows are under version control
  • Staging: Preparing changes for commit
  • Working Copy: Live database state vs versioned state
  • Remotes: Distributed version control between databases

All identifiers use JSONB domains:

  • row_id: {"schema_name": "x", "relation_name": "y", "pk_column_names": ["id"], "pk_values": ["123"]}
  • field_id: Extends row_id with "column_name": "field_name"

Repository Management

create_repository(repository_name)

Purpose: Creates a new bundle repository

Parameters:

  • repository_name (text): Unique name for the repository, cannot be empty or null

Returns: uuid - The repository ID

Usage Example:

SELECT bundle.create_repository('org.example.myapp');

Notes: Repository names should use reverse domain notation (e.g., org.company.project)

delete_repository(repository_name)

Purpose: Permanently deletes a repository and all its data

Parameters:

  • repository_name (text): Name of repository to delete

Returns: void

Usage Example:

SELECT bundle.delete_repository('org.example.myapp');

Notes: This cascades to delete all commits, blobs, and tracked data. Cannot be undone.

repository_exists(repository_name)

Purpose: Check if a repository exists

Parameters:

  • repository_name (text): Repository name to check

Returns: boolean - true if repository exists

Usage Example:

SELECT bundle.repository_exists('org.example.myapp');

repository_id(repository_name)

Purpose: Get the UUID of a repository from its name

Parameters:

  • repository_name (text): Repository name

Returns: uuid - Repository ID, or null if not found

Usage Example:

SELECT bundle.repository_id('org.example.myapp');

head_commit_id(repository_name)

Purpose: Get the UUID of a repository's HEAD commit

Parameters:

  • repository_name (text): Repository name

Returns: uuid - Head commit ID, or null if no commits

Usage Example:

SELECT bundle.head_commit_id('org.example.myapp');

checkout_commit_id(repository_name)

Purpose: Get the UUID of a repository's currently checked out commit

Parameters:

  • repository_name (text): Repository name

Returns: uuid - Checkout commit ID, or null if not checked out

Usage Example:

SELECT bundle.checkout_commit_id('org.example.myapp');

Tracking Management

track_untracked_row(repository_name, row_id)

Purpose: Add an untracked row to a repository's tracking list

Parameters:

  • repository_name (text): Repository name
  • row_id (meta.row_id): JSONB row identifier

Returns: void

Usage Example:

SELECT bundle.track_untracked_row(
    'org.example.myapp',
    meta.make_row_id('public', 'users', ARRAY['id'], ARRAY['123'])
);

Notes: Row must exist in the database and not already be tracked

untrack_tracked_row(repository_name, row_id)

Purpose: Remove a tracked row from a repository

Parameters:

  • repository_name (text): Repository name
  • row_id (meta.row_id): JSONB row identifier

Returns: uuid - The untracked row ID

Usage Example:

SELECT bundle.untrack_tracked_row(
    'org.example.myapp',
    meta.make_row_id('public', 'users', ARRAY['id'], ARRAY['123'])
);

track_untracked_rows_by_relation(repository_name, relation_id)

Purpose: Track all untracked rows in a specific relation (table/view)

Parameters:

  • repository_name (text): Repository name
  • relation_id (meta.relation_id): JSONB relation identifier

Returns: void

Usage Example:

SELECT bundle.track_untracked_rows_by_relation(
    'org.example.myapp',
    meta.make_relation_id('public', 'users')
);

get_tracked_rows_added(repository_name)

Purpose: Get all newly tracked rows (not yet staged)

Parameters:

  • repository_name (text): Repository name

Returns: TABLE(repository_id uuid, row_id meta.row_id)

Usage Example:

SELECT * FROM bundle.get_tracked_rows_added('org.example.myapp');

get_tracked_rows(repository_name)

Purpose: Get all tracked rows (newly tracked + staged + committed)

Parameters:

  • repository_name (text): Repository name

Returns: SETOF meta.row_id

Usage Example:

SELECT * FROM bundle.get_tracked_rows('org.example.myapp');

Staging Operations

stage_tracked_row(repository_name, row_id)

Purpose: Stage a newly tracked row for commit (moves from tracked to staged)

Parameters:

  • repository_name (text): Repository name
  • row_id (meta.row_id): Row identifier

Returns: void

Usage Example:

SELECT bundle.stage_tracked_row(
    'org.example.myapp',
    meta.make_row_id('public', 'users', ARRAY['id'], ARRAY['123'])
);

stage_tracked_rows(repository_name)

Purpose: Stage all newly tracked rows for commit

Parameters:

  • repository_name (text): Repository name

Returns: void

Usage Example:

SELECT bundle.stage_tracked_rows('org.example.myapp');

stage_row_to_remove(repository_name, row_id)

Purpose: Stage a row for deletion in the next commit

Parameters:

  • repository_name (text): Repository name
  • row_id (meta.row_id): Row identifier

Returns: void

Usage Example:

SELECT bundle.stage_row_to_remove(
    'org.example.myapp',
    meta.make_row_id('public', 'users', ARRAY['id'], ARRAY['123'])
);

stage_updated_fields(repository_name, relation_id_filter)

Purpose: Stage all field changes for commit

Parameters:

  • repository_name (text): Repository name
  • relation_id_filter (meta.relation_id, optional): Limit to specific relation

Returns: void

Usage Example:

-- Stage all field changes
SELECT bundle.stage_updated_fields('org.example.myapp');

-- Stage only changes in users table
SELECT bundle.stage_updated_fields(
    'org.example.myapp',
    meta.make_relation_id('public', 'users')
);

stage_deleted_rows(repository_name, relation_id_filter)

Purpose: Stage all deleted rows for removal

Parameters:

  • repository_name (text): Repository name
  • relation_id_filter (meta.relation_id, optional): Limit to specific relation

Returns: void

Usage Example:

SELECT bundle.stage_deleted_rows('org.example.myapp');

empty_stage(repository_name)

Purpose: Clear all staged changes

Parameters:

  • repository_name (text): Repository name

Returns: void

Usage Example:

SELECT bundle.empty_stage('org.example.myapp');

Notes: This removes all staged additions, deletions, and field changes

Commit Operations

commit(repository_name, message, author_name, author_email, parent_commit_id)

Purpose: Create a new commit with all staged changes

Parameters:

  • repository_name (text): Repository name
  • message (text): Commit message
  • author_name (text): Author's name
  • author_email (text): Author's email
  • parent_commit_id (uuid, optional): Parent commit, defaults to HEAD

Returns: uuid - New commit ID

Usage Example:

SELECT bundle.commit(
    'org.example.myapp',
    'Add new user management features',
    'John Doe',
    'john@example.com'
);

Notes: Automatically updates HEAD pointer and empties the stage

get_head_commit_rows(repository_name, relation_id_filter)

Purpose: Get all rows in the HEAD commit

Parameters:

  • repository_name (text): Repository name
  • relation_id_filter (meta.relation_id, optional): Filter by relation

Returns: TABLE(position integer, row_id meta.row_id)

Usage Example:

SELECT * FROM bundle.get_head_commit_rows('org.example.myapp');

Checkout Operations

checkout(repository_name)

Purpose: Check out the HEAD commit to the working directory

Parameters:

  • repository_name (text): Repository name

Returns: void

Usage Example:

SELECT bundle.checkout('org.example.myapp');

Notes: Applies all rows and field values from HEAD commit to database tables

delete_checkout(repository_name)

Purpose: Remove all checked out data from the database

Parameters:

  • repository_name (text): Repository name

Returns: void

Usage Example:

SELECT bundle.delete_checkout('org.example.myapp');

Notes: Deletes rows in reverse dependency order to handle foreign keys

Status and Information

status(repository_name, detailed)

Purpose: Get comprehensive status information about repository

Parameters:

  • repository_name (text, optional): Specific repository, or null for all
  • detailed (boolean, optional): Include detailed change lists, defaults to false

Returns: text - Formatted status report

Usage Example:

-- Basic status for specific repository
SELECT bundle.status('org.example.myapp');

-- Detailed status for all repositories
SELECT bundle.status(null, true);

-- Status overview for all repositories
SELECT bundle.status();

Notes: Shows commits, tracked/staged/changed row counts, and current checkout state

Ignore Rules

ignore_schema(schema_id)

Purpose: Ignore an entire schema from version control

Parameters:

  • schema_id (meta.schema_id): Schema identifier

Returns: void

Usage Example:

SELECT bundle.ignore_schema(meta.make_schema_id('temp_schema'));

unignore_schema(schema_id)

Purpose: Remove schema from ignore list

Parameters:

  • schema_id (meta.schema_id): Schema identifier

Returns: void

ignore_table(relation_id)

Purpose: Ignore a specific table/view from version control

Parameters:

  • relation_id (meta.relation_id): Relation identifier

Returns: void

Usage Example:

SELECT bundle.ignore_table(meta.make_relation_id('public', 'temp_table'));

unignore_table(relation_id)

Purpose: Remove table from ignore list

Parameters:

  • relation_id (meta.relation_id): Relation identifier

Returns: void

ignore_row(row_id)

Purpose: Ignore a specific row from version control

Parameters:

  • row_id (meta.row_id): Row identifier

Returns: void

Usage Example:

SELECT bundle.ignore_row(
    meta.make_row_id('public', 'users', ARRAY['id'], ARRAY['admin'])
);

unignore_row(row_id)

Purpose: Remove row from ignore list

Parameters:

  • row_id (meta.row_id): Row identifier

Returns: void

ignore_column(column_id)

Purpose: Ignore a specific column from version control

Parameters:

  • column_id (meta.column_id): Column identifier

Returns: void

Usage Example:

SELECT bundle.ignore_column(
    meta.make_column_id('public', 'users', 'password_hash')
);

unignore_column(column_id)

Purpose: Remove column from ignore list

Parameters:

  • column_id (meta.column_id): Column identifier

Returns: void

Content Storage

hash(value)

Purpose: Generate SHA256 hash of a text value

Parameters:

  • value (text): Text to hash

Returns: text - SHA256 hash or special null hash

Usage Example:

SELECT bundle.hash('example text');

Notes: Returns special constant for null values

unhash(hash)

Purpose: Retrieve original value from hash via blob table

Parameters:

  • hash (text): SHA256 hash

Returns: text - Original value

Usage Example:

SELECT bundle.unhash('\x123abc...');

Notes: Throws exception if hash not found in blob table

create_blob(value)

Purpose: Store a value in the blob table with its hash

Parameters:

  • value (text): Value to store

Returns: boolean - true if blob was created, false if already exists

Usage Example:

SELECT bundle.create_blob('important data');

Import/Export

import_repository(bundle_json)

Purpose: Import a repository from JSON export

Parameters:

  • bundle_json (text): JSON string containing repository data

Returns: void

Usage Example:

SELECT bundle.import_repository('{"repository": {...}, "commits": [...], "blobs": [...]}');

Notes: Uses INSERT ... ON CONFLICT DO NOTHING for idempotent imports

Utility Functions

random_string(length)

Purpose: Generate random string of specified length

Parameters:

  • length (int): Desired string length

Returns: text - Random string

Usage Example:

SELECT bundle.random_string(10);

clock_diff(start_time)

Purpose: Calculate elapsed time from start timestamp

Parameters:

  • start_time (timestamp): Starting timestamp

Returns: text - Elapsed seconds as text

Usage Example:

SELECT bundle.clock_diff(clock_timestamp() - interval '5 seconds');

Data Types

field_hash

Composite type for field identifiers and their value hashes:

TYPE field_hash AS (
    field_id meta.field_id,
    value_hash text
);

row_exists

Composite type for row existence checks:

TYPE row_exists AS (
    row_id meta.row_id,
    exists boolean
);

stage_row

Composite type for staging information:

TYPE stage_row AS (
    row_id meta.row_id,
    new_row boolean
);

Views

trackable_relation

Shows all relations that can be version controlled (have primary keys and not ignored)

tracked_row_added

Shows all newly tracked rows across repositories

stage_row_to_add

Shows all rows staged for addition

stage_row_to_remove

Shows all rows staged for removal

stage_field_to_change

Shows all fields staged for change

Best Practices

  1. Repository Naming: Use reverse domain notation (e.g., com.company.project.module)

  2. Committing Workflow:

    -- Track new data
SELECT bundle.track_untracked_rows_by_relation('my.repo', meta.make_relation_id('public', 'users'));

-- Stage tracked rows
SELECT bundle.stage_tracked_rows('my.repo');

-- Stage field changes  
SELECT bundle.stage_updated_fields('my.repo');

-- Commit all changes
SELECT bundle.commit('my.repo', 'Add user data', 'Author', 'author@email.com');

  3. Status Checking: Always check status before major operations:

    SELECT bundle.status('my.repo', true);

  4. Error Handling: Most functions raise exceptions on error conditions (repository not found, row already tracked, etc.)

  5. Performance: Use relation filters when possible for large datasets:

    SELECT bundle.stage_updated_fields('my.repo', meta.make_relation_id('public', 'large_table'));

Notes

  • All public functions are in the bundle schema
  • Internal functions prefixed with _ are not part of the public API
  • The system uses PostgreSQL's JSONB for flexible, validated identifiers
  • Text is used as "lingua franca" - all PostgreSQL types have text representation
  • Hash-based content storage provides deduplication across all repositories

Clone this wiki locally