Change Request: API Versioning for pgmi Session Interface#

Status: IMPLEMENTED This design document describes changes that have been completed. The “Current State” section reflects the pre-implementation state for historical context. The “Target State” is now the actual implementation. See Session API for current API documentation.

Overview#

Introduce versioned API contracts for pgmi’s session-scoped interface (temp tables, views, functions). This enables:

• Stable view and function names and columns for deploy.sql across pgmi upgrades
• DevOps pipelines pinned to specific API versions
• Internal refactoring freedom for pgmi maintainers

Motivation#

Before this change, deploy.sql scripts referenced internal tables like pg_temp.pgmi_source directly. If pgmi changes the table structure, all user deploy.sql scripts break. By introducing versioned views as the public API, pgmi can evolve internally while maintaining backward compatibility.

Use case: A CI/CD pipeline uses pgmi deploy --compat=1. When a later pgmi changes its internal tables, the pipeline’s queries still resolve because pgmi provides the v1 names and columns. --compat does not pin behavior: plan ordering, which files load, and how deploy.sql is sent follow the binary version.

Design Decisions (Already Made)#

Pre-Implementation State (Historical)#

Internal Tables (in internal/params/schema.sql)#

Existing Views#

Existing Functions (Public API)#

Note: Parameter access is via current_setting('pgmi.key', true). Templates handle their own declaration, validation, and defaults.

Implemented State#

File Structure#

internal/params/
  schema.sql          # Internal tables (_pgmi_*) and most functions, always executes first
internal/contract/
  api-v1.sql          # V1 views and pgmi_test_generate
  api-v2.sql          # Future: V2 contract (when needed)

Renamed Internal Tables (in schema.sql)#

V1 API Views (in api-v1.sql)#

Note: pgmi_plan_view is moved from schema.sql to api-v1.sql. It references internal tables (_pgmi_source, _pgmi_source_metadata) but exposes a stable interface. The view contains execution ordering logic (UNNEST sort_keys, ROW_NUMBER, etc.).

V1 API Functions#

Functions that are part of the v1 contract. Only pgmi_test_generate lives in api-v1.sql; the others are defined in schema.sql:

• pgmi_test_plan(p_pattern)
• pgmi_is_sql_file(filename)
• pgmi_persist_test_plan(target_schema, p_pattern)
• pgmi_test_callback(e) - default callback
• pgmi_test_generate(p_pattern, p_callback) - generates macro replacement code

Note: Parameter functions (pgmi_declare_param, pgmi_get_param) were removed. Parameters are accessed via current_setting('pgmi.key', true). Templates handle declaration, validation, and defaults.

Internal functions (not part of public API, stay in schema.sql):

• pgmi_register_file(...) - called by Go only
• pgmi_validate_pattern(...) - internal helper
• pgmi_has_tests(...) - internal helper

Macro Code Generation Architecture#

Current State (Go generates SQL)#

The Go preprocessor in internal/preprocessor/ finds pgmi_test() or CALL pgmi_test() calls and replaces them with a large DO block containing loops, savepoints, and EXECUTE statements. This means:

• Go needs to know internal table names
• Different API versions would require different Go code
• SQL generation logic is split between Go and SQL

Target State (SQL generates SQL)#

Move code generation to a SQL function that returns the replacement code as TEXT:

┌─────────────────┐     ┌────────────────────────────┐     ┌──────────────────┐
│ Go preprocessor │ ──► │ SELECT pgmi_test_generate( │ ──► │ Go replaces      │
│ finds macro     │     │   'pattern', 'callback')   │     │ macro with       │
│ pgmi_test(...)  │     │ Returns: DO $$ ... END $$  │     │ returned SQL     │
└─────────────────┘     └────────────────────────────┘     └──────────────────┘

The Generator Function (in api-v1.sql)#

CREATE OR REPLACE FUNCTION pg_temp.pgmi_test_generate(
    p_pattern TEXT DEFAULT NULL,
    p_callback TEXT DEFAULT 'pg_temp.pgmi_test_callback'
) RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    v_callback_safe TEXT;
    v_pattern_literal TEXT;
BEGIN
    -- Escape/validate inputs
    v_callback_safe := COALESCE(p_callback, 'pg_temp.pgmi_test_callback');
    v_pattern_literal := CASE
        WHEN p_pattern IS NULL THEN 'NULL'
        ELSE format('%L', p_pattern)
    END;

    -- Return the complete DO block as TEXT
    -- This references internal tables (_pgmi_test_source, etc.)
    RETURN format($code$
DO $pgmi_test$
DECLARE
    v_step RECORD;
    v_event pg_temp.pgmi_test_event;
    v_savepoint_name TEXT;
    v_ordinal INT := 0;
BEGIN
    -- Suite start
    v_event := ROW('suite_start', NULL, NULL, 0, 0, NULL)::pg_temp.pgmi_test_event;
    PERFORM %s(v_event);

    FOR v_step IN (
        SELECT * FROM pg_temp.pgmi_test_plan(%s)
    )
    LOOP
        v_ordinal := v_ordinal + 1;
        v_savepoint_name := 'pgmi_test_' || v_ordinal;

        CASE v_step.step_type
            WHEN 'fixture' THEN
                v_event := ROW('fixture_start', v_step.script_path, v_step.directory, v_step.depth, v_ordinal, NULL);
                PERFORM %s(v_event);
                EXECUTE (SELECT content FROM pg_temp._pgmi_test_source WHERE path = v_step.script_path);

            WHEN 'test' THEN
                SAVEPOINT _test_sp;
                v_event := ROW('test_start', v_step.script_path, v_step.directory, v_step.depth, v_ordinal, NULL);
                PERFORM %s(v_event);
                BEGIN
                    EXECUTE (SELECT content FROM pg_temp._pgmi_test_source WHERE path = v_step.script_path);
                    v_event := ROW('test_end', v_step.script_path, v_step.directory, v_step.depth, v_ordinal, NULL);
                    PERFORM %s(v_event);
                EXCEPTION WHEN OTHERS THEN
                    RAISE;
                END;
                ROLLBACK TO SAVEPOINT _test_sp;

            WHEN 'teardown' THEN
                v_event := ROW('teardown_start', NULL, v_step.directory, v_step.depth, v_ordinal, NULL);
                PERFORM %s(v_event);
        END CASE;
    END LOOP;

    -- Suite end
    v_event := ROW('suite_end', NULL, NULL, 0, v_ordinal, NULL)::pg_temp.pgmi_test_event;
    PERFORM %s(v_event);
END $pgmi_test$;
$code$,
        v_callback_safe,      -- suite_start callback
        v_pattern_literal,    -- pattern for pgmi_test_plan
        v_callback_safe,      -- fixture callback
        v_callback_safe,      -- test_start callback
        v_callback_safe,      -- test_end callback
        v_callback_safe,      -- teardown callback
        v_callback_safe       -- suite_end callback
    );
END;
$$;

COMMENT ON FUNCTION pg_temp.pgmi_test_generate IS
'Generates the SQL code for pgmi_test() macro expansion.
Called by Go preprocessor, returns a DO block as TEXT.
Part of the versioned API contract - v1 generates code referencing v1 internal tables.';

Implementation Note: The actual implementation in api-v1.sql differs from this proposal. Instead of generating a single DO block containing SAVEPOINT commands (which would fail because PL/pgSQL doesn’t support savepoints), the actual implementation generates a series of top-level SQL statements where SAVEPOINT/ROLLBACK are at the SQL level and each fixture or test runs through SELECT pg_temp.pgmi_run_test_source(path). See internal/contract/api-v1.sql for the working implementation.

Updated Go Preprocessor Flow#

// internal/preprocessor/preprocessor.go

func (p *Preprocessor) ExpandMacros(ctx context.Context, conn *pgxpool.Conn, sql string) (string, error) {
    // Find all pgmi_test(...) or CALL pgmi_test(...) calls
    matches := pgmiTestRegex.FindAllStringSubmatchIndex(sql, -1)

    for _, match := range matches {
        pattern, callback := extractArguments(sql, match)

        // Call the SQL generator function (part of versioned API)
        var generatedSQL string
        err := conn.QueryRow(ctx,
            "SELECT pg_temp.pgmi_test_generate($1, $2)",
            pattern, callback,
        ).Scan(&generatedSQL)
        if err != nil {
            return "", fmt.Errorf("macro expansion failed: %w", err)
        }

        // Replace macro with generated SQL
        sql = replaceMacro(sql, match, generatedSQL)
    }

    return sql, nil
}

Benefits of This Approach#

1. Version isolation: v1 generator knows v1 internals, v2 generator knows v2 internals
2. Go becomes version-agnostic: Just calls pgmi_test_generate(), doesn’t know table names
3. SQL generation in SQL: More natural, easier to maintain and test
4. Testable: Unit test the generator function independently
5. Consistent behavior: Generated code is deterministic and versioned

Go Implementation#

New Package: internal/contract#

// internal/contract/contract.go
package contract

import "embed"

//go:embed api-v1.sql
//go:embed api-v2.sql
var embeddedAPIs embed.FS

type Version string

const (
    V1      Version = "1"
    Latest  Version = V1  // Update when new version released
)

var supportedVersions = map[Version]string{
    V1: "api-v1.sql",
}

type Contract struct {
    Version Version
    SQL     string
}

// Load returns the API contract for the specified version.
// Returns error if version is not supported.
func Load(version string) (*Contract, error) {
    v := Version(version)
    if v == "" {
        v = Latest
    }

    filename, ok := supportedVersions[v]
    if !ok {
        return nil, fmt.Errorf("unsupported API version %q; supported: %v",
            version, SupportedVersions())
    }

    content, err := embeddedAPIs.ReadFile(filename)
    if err != nil {
        return nil, fmt.Errorf("failed to load API v%s: %w", version, err)
    }

    return &Contract{Version: v, SQL: string(content)}, nil
}

// SupportedVersions returns list of supported API versions.
func SupportedVersions() []Version {
    versions := make([]Version, 0, len(supportedVersions))
    for v := range supportedVersions {
        versions = append(versions, v)
    }
    sort.Slice(versions, func(i, j int) bool {
        return versions[i] < versions[j]
    })
    return versions
}

CLI Flag Addition#

In internal/cli/deploy.go:

var compat string

func init() {
    deployCmd.Flags().StringVar(&compat, "compat", "",
        "Compatibility level (default: latest)")
}

Deployer Integration#

In internal/services/deployer.go, after executing schema.sql:

// Load and execute API contract
contract, err := contract.Load(cfg.Compat)
if err != nil {
    return fmt.Errorf("API contract error: %w", err)
}

if _, err := conn.Exec(ctx, contract.SQL); err != nil {
    return fmt.Errorf("failed to initialize API v%s: %w", contract.Version, err)
}

Execution Order#

1. schema.sql          → Creates internal tables (_pgmi_*), internal functions
2. Go inserts files    → _pgmi_source via pgmi_register_file()
3. Go inserts params   → _pgmi_parameter
4. api-v{N}.sql        → Creates versioned views, public functions, pgmi_test_generate()
5. Go preprocessor     → Calls pgmi_test_generate() to expand macros in deploy.sql
6. deploy.sql executes → Uses views (pgmi_source_view, pgmi_plan_view, etc.)

Critical: Step 4 (api-v{N}.sql) must execute BEFORE step 5 (preprocessing) because the preprocessor calls pgmi_test_generate() which is defined in the API contract.

Loader Code Updates#

internal/files/loader/loader.go directly references table names for batch inserts:

The pgmi_register_file() function (stays in schema.sql) handles _pgmi_source internally, so loader code calling it doesn’t need changes for that table.

Template Updates#

Advanced Template (deploy.sql)#

Change all references:

-- Before
FROM pg_temp.pgmi_source WHERE ...

-- After
FROM pg_temp.pgmi_source_view WHERE ...

Affected locations in advanced template:

• Parameter loading (reads pgmi_source_view for session.xml)
• Deploy function (joins pgmi_plan_view with pgmi_source_view)

Basic Template (deploy.sql)#

Similar changes - update pgmi_source to pgmi_source_view.

Documentation Updates#

User-Facing Documentation#

Only document the public API:

• Views: pgmi_source_view, pgmi_parameter_view, pgmi_plan_view, pgmi_test_source_view
• Functions: pgmi_test_plan(), pgmi_test_generate()
• Parameter access: current_setting('pgmi.key', true)

Do NOT document:

• Internal tables (_pgmi_*)
• Internal functions (pgmi_register_file, pgmi_validate_pattern, pgmi_has_tests)

CLAUDE.md Updates#

Update the “Session-Centric Deployment Model” section to reference views instead of tables.

pgmi ai Output#

Update embedded documentation to reference the public API only.

Testing Strategy#

Unit Tests (internal/contract/contract_test.go)#

func TestLoad_ValidVersion(t *testing.T) {
    c, err := contract.Load("1")
    require.NoError(t, err)
    assert.Equal(t, contract.V1, c.Version)
    assert.Contains(t, c.SQL, "pgmi_source_view")
}

func TestLoad_EmptyVersionUsesLatest(t *testing.T) {
    c, err := contract.Load("")
    require.NoError(t, err)
    assert.Equal(t, contract.Latest, c.Version)
}

func TestLoad_UnsupportedVersion(t *testing.T) {
    _, err := contract.Load("99")
    require.Error(t, err)
    assert.Contains(t, err.Error(), "unsupported API version")
}

func TestSupportedVersions(t *testing.T) {
    versions := contract.SupportedVersions()
    assert.Contains(t, versions, contract.V1)
}

Integration Tests#

1. Template deployment with default version:

   • Run existing TestTemplateDeployment - should pass unchanged

2. Template deployment with explicit version:

   func TestTemplateDeployment_ExplicitCompat(t *testing.T) {
       // Deploy with --compat=1
       // Verify views exist
       // Verify deploy.sql can query pgmi_source_view
   }

3. Unsupported version error:

   func TestDeploy_UnsupportedCompat(t *testing.T) {
       // Deploy with --compat=99
       // Expect clear error message listing supported versions
   }

4. View functionality:

   func TestAPIViews_MatchTableContent(t *testing.T) {
       // Insert test data into _pgmi_source
       // Query pgmi_source_view
       // Verify identical results
   }

Regression Tests#

Run full test suite to ensure nothing breaks:

make test-all
make test-integration

Implementation History#

Phase 1: SQL Refactoring#

1. Rename tables in schema.sql:

   • pgmi_source → _pgmi_source
   • pgmi_source_metadata → _pgmi_source_metadata
   • pgmi_parameter → _pgmi_parameter
   • pgmi_test_directory → _pgmi_test_directory
   • pgmi_test_source → _pgmi_test_source
   • Update pgmi_register_file() to reference _pgmi_source
   • Update DROP TABLE statements at top

2. Update internal function references in schema.sql:

   • pgmi_has_tests() → reference _pgmi_test_directory, _pgmi_test_source
   • pgmi_test_plan() → reference _pgmi_test_directory, _pgmi_test_source
   • pgmi_declare_param() → reference _pgmi_parameter (Removed - templates handle parameters)

3. Create api-v1.sql:

   • Move pgmi_plan_view from schema.sql (update to reference _pgmi_*)
   • Add simple views: pgmi_source_view, pgmi_parameter_view, etc.
   • Move public functions: pgmi_test_plan(), etc. (Note: pgmi_get_param() and pgmi_declare_param() were removed)
   • Add pgmi_test_generate() function (migrate logic from Go preprocessor)
   • Add pgmi_test_callback() default callback

4. Update loader code (internal/files/loader/loader.go):

   • pgmi_test_source → _pgmi_test_source
   • pgmi_test_directory → _pgmi_test_directory

Phase 2: Go Infrastructure#

5. Create internal/contract package:

   • Embed api-v1.sql
   • Load(version) function
   • SupportedVersions() function
   • Unit tests

6. Update deployer (internal/services/deployer.go):

   • Execute schema.sql first
   • Execute api-v{N}.sql second
   • Pass API version from config

7. Update preprocessor (internal/preprocessor/):

   • Remove inline SQL generation
   • Call pgmi_test_generate() to get replacement code
   • Simplify to: find macro → call function → replace

8. Add --compat CLI flag:

   • Add to internal/cli/deploy.go
   • Wire through config to deployer
   • Default to latest

Phase 3: Templates and Testing#

9. Update templates:

   • Advanced: pgmi_source → pgmi_source_view
   • Basic: pgmi_source → pgmi_source_view
   • Verify CALL pgmi_test() still works (now via generator)

10. Run full test suite:

    • make test - unit tests
    • make test-integration - template deployment
    • Fix any regressions

Phase 4: Documentation#

11. Update documentation:

    • CLAUDE.md - reference views, not tables
    • internal/ai/content/ - update embedded docs
    • Template README files

12. Add version-specific tests:

    • Test --compat=1 explicitly
    • Test unsupported version error
    • Test default version behavior

Acceptance Criteria (Completed)#

CLI & Versioning#

• --compat=1 flag accepted by pgmi deploy
• Omitting --compat uses latest stable version
• --compat=99 returns clear error with supported versions list

Schema Structure#

• Internal tables prefixed with _ (_pgmi_source, _pgmi_parameter, etc.)
• Public views created (pgmi_source_view, pgmi_parameter_view, etc.)
• pgmi_plan_view in api-v1.sql, references internal tables
• pgmi_test_generate() function exists and returns valid SQL

Macro Expansion#

• CALL pgmi_test() expands via pgmi_test_generate() function call
• Go preprocessor no longer contains inline SQL generation
• Generated code references internal tables (_pgmi_test_source)

Templates#

• Advanced template uses pgmi_source_view (not pgmi_source)
• Basic template uses pgmi_source_view (not pgmi_source)
• TestTemplateDeployment passes for both templates

Documentation#

• pgmi ai output only references public API (views, public functions)
• CLAUDE.md references views, not tables
• No references to _pgmi_* tables in user-facing documentation

Risks and Mitigations#

Future Considerations#

• V2 API: Only create when there’s a breaking change that can’t be backward-compatible
• Version deprecation warnings: RAISE WARNING 'API v1 deprecated, migrate to v2'
• Version discovery: pgmi --supported-api-versions command
• Per-project version pinning: .pgmi.yaml with api-version: 1

Estimated Effort#

Note: The pgmi_test_generate() function is the most complex piece - it must produce identical output to the current Go preprocessor. Recommend implementing incrementally with comparison tests.

References#

Files to Modify#

Key Test Files#

Grep Commands for Discovery#

# Find all references to pgmi_source (not _pgmi_source)
grep -r "pgmi_source" --include="*.go" --include="*.sql" | grep -v "_pgmi_source"

# Find all references to pgmi_parameter
grep -r "pgmi_parameter" --include="*.go" --include="*.sql" | grep -v "_pgmi_parameter"

# Find preprocessor SQL generation
grep -r "pgmi_test" internal/preprocessor/