Migration script function signatures — Go templates + sprig

[AlbinoGeek]

Context

Schema DDL for schema_versions, schema_tables, and schema_tables_join approved in #25.

Migration scripts (both schema_migration_script and record_migration_script fields on schema_versions) will be implemented using Go templates + sprig (confirmed in #25).

Phase 1 — Stub function signatures

Design and document the function signatures exposed to migration templates. Topics to cover:

• What functions are available inside a schema_migration_script template (DDL mutations, table rename, index management)
• What functions are available inside a record_migration_script template (record read, transform, write, reject)
• How the template context (.) is structured for each script type (input schema_tables row, source records, destination table_id, etc.)
• Any sprig extensions planned for use (string manipulation, type coercion, etc.)
• Error handling contract (what happens when a record migration script rejects a record)

Phase 2 — Implementation

Implementation of the above signatures in the migration runner. Tracked separately after Phase 1 sign-off.

Pending

• Phase 1: agree on function signatures and template context shape
• Phase 2: implement

— Bastion

[gissf1]

RE: functions available inside schema_migration_script template:

• DDL mutations - honestly it might just make sense to accept SQL or some meta-SQL as parameters to a ddl function:
  • raw_sql("ALTER TABLE raw_sql ADD COLUMN 'new_column' INTEGER NOT NULL")
  • create_table("table_name", { "field1": { "type": "TIMESTAMPTZ", "null": false, "other": "UNIQUE" }, "field2": { ... }, ... })
  • alter_table("table_name", { "field_to_alter": { "attribute": "new_value", ... }, ... })
  • drop_table("table_name")
• table rename
  • rename_table("old_table_name", "new_table_name")
• index management
  • add_index("table_name", "index_name", "index specifics as a string or dictionary")
  • alter_index("table_name", "index_name", "new index specifics as a string or dictionary")
  • drop_index("table_name", "index_name")
  • I'm not sure if PGSQL supports it, but these could be useful during a migration:
    • disable_index("table_name", "index_name")
    • enable_index("table_name", "index_name")
  • what else might we need here?

RE: functions available inside record_migration_script template:

• we can make raw SQL statements:
  • raw_sql("SELECT raw_sql WHERE we_specify_whatever_sql != we_want_to_use")
  • raw_sql("UPDATE raw_sql SET field1=value2 WHERE we_specify_whatever_sql != we_want_to_use")
• we need a "this" type object referencing the record we are trying to migrate (this can be populated from file or database data, so it can have records from any of them)
• FKs should be able to be used as if they were sub-objects, or as an ID if its value is used directly; this makes it transparent to use nested records embedded into a file (for example GPU hardware data within a results record file), but also able to use it in WHERE or JOIN type scenarios for matching by IDs
• I'm going to explain what I'm imagining in features, but ideally we could reuse an existing DB abstraction layer rather than creating one from scratch.
• querying other tables:
  • select(table_name, optional_where_clause_object_or_string, optional_field_list)
    • this is similar to the raw_sql() call and returns a list of record objects on success, an empty list if none were returned, or an error object
    • table_name is a variable or quoted string
    • optional_where_clause_object_or_string can be a string to use as a WHERE clause, or a more flexible object syntax like:
      • Example expression: {"field1": "value1", "field2": value_in(["a", "b"]), "field3": value_in(list_variable), ...}
      • notice how we can use this to build a WHERE clause with implicit AND between field siblings
      • one can use the "_or" function when needed for things like: WHERE A=1 OR B=2 using expressions like:
        • _or({"A": 1, "B": 2})
        • _or({"A": 1}, {"B": 2})
      • there are expression for comparison operators:
        • "value_in" expression renders to something akin to WHERE A IN (1,2,3) for the expression: {"A": value_in(1, 2, 3)}
        • not_equal(value) -> WHERE A != value
        • greater_than(value) -> WHERE A > value
        • greater_than_equal(value) -> WHERE A >= value
        • less_than(value)
        • less_than_equal(value)
        • between(value_a, value_b) -> WHERE A BETWEEN value_a AND value_b
      • there are other functions for things other than comparisons:
        • not({...}) -> WHERE NOT (...)
        • field("field1") -> WHERE A = field1
      • there are likely others
      • maybe there is an existing DB abstraction library we can use?
        • if so, we can probably use this same library and emulate a DB record for the "this" object as well
    • optional_field_list can be a string (for a single field), a list of field names, or a dictionary with keys to populate. If omitted, all fields are returned.
    • returns the records selected by the query, an empty list when no records match, or an error object
  • select_first(...)
    • similar to select(...), but only returns the first record of a query, effectively like adding a LIMIT 1 to the query. It will return nil, a single record, or an error object
  • select_one(...)
    • similar to select_first(), but returns an error if there is not exactly 1 record matching the query. This will return a single record, or an error object
• assigning our own record's fields with a value:
  • set("field1", "new_value")
  • this.field1 = "new_value"
  • this["field1"] = "new_value"
  • set({"field1": "new_value"})
  • set({"field1": "value1", "field2": ("a" + "b"), "field3": select_one(..., ..., "field4"), ... })
• removing a field from our record is implicit if the table drops the field; retaining it in the file is optional, but it can be explicitly removed with: purge("field1")
• this.reject() or reject() (depending on feasibility) to reject the current input record (failure, abort migration)
• this.ignore() or ignore() (depending on feasibility) to ignore the current input record (proceed without this one).
  • This means we also need an --ignore-is-fatal command line flag for the testing tool to catch and report ignored records.
• we can use any typical conditionals (so if-statements, etc) to have more complex go-based logic if needed.
• if we have functions for sha512 in go, it would make sense that we can do things like: set("field1", sha512(this.field1))

That is just how I imagine it, but as stated earlier, using a established DB abstraction layer is more important that the exact feature set, as most of my imagined feaures are just conveniences, not essentials.

RE: How the template context (.) is structured for each script type:
In my vision, the templates are used more like a script being sent to a scripting host than an actual template.

Let's start with finding a database abstraction layer and then this will be a bit more refined.

I'm thinking the DDL will usually just execute whatever is needed on the DB. If there is no special conversion needed for records, just the DDL may be present and the record_migration_script be empty/NULL

When records have to be mangled (for example adding a field to a UUID), it requires more than just applying the DDL because the records are processed independently, and it can't be done atomically for the entire table. In this case, I suggest creating a new temporary table for the migation with the new table's schema (based on the old table schema, but with the new DDL migration applied). This table is where updated records will be stored during processing. This means that if a failure happens, the old table is completely intact and unmodified. Once all table rows have been migrated as expected, the old table can be renamed, temporary table renamed to the new table (no longer temporary), and drop the old table. If there is a DDL "swap table names" method, that would be great, but I'm not aware of that existing.

the record_migration_script should have some kind of "this" object referencing the object bring migrated as input, and changes to this object are intended as the output, so it's effectively used in a by-reference context. initial "this" object population and return are implied and managed by the runtime.

Are there other conditions I didn't to address?

RE: sprig extensions planned for use:

• whatever functions we need to implement the DB mangling we intend to do, along with proxying calls to other library functions for hashing and such.

RE: Error handling contract -- what happens when a record migration script rejects a record:

• I think this is simple -- the record does not update and the situation and details are reported for the user to correct. This could be an indication that the migration script needs to update or that some edge case needs to be addressed. It could also be a terminal failure - maybe certain records simply are not able to be updated. It may also be possible that some external process needs to gather more data from a particular system in order to update the record, in which case, that may require the user to submit new results or something like that.

[AlbinoGeek]

@gissf1 — detailed breakdown, solid foundation. Addressing each section:

DB abstraction layer

Recommend squirrel for query building — its fluent API maps cleanly to the select(table, where, fields) pattern you described. Combine with pgx/v5 for PostgreSQL execution (native protocol, TIMESTAMPTZ support, named args). For the "this" object, implement a thin wrapper struct that proxies squirrel's row scanning and tracks field mutations — avoids inventing a custom ORM.

Mapping your DSL to squirrel:

• select(table, where, fields) → squirrel SELECT builder + pgx scan
• select_first / select_one → LIMIT 1 + row count validation at Go layer
• value_in, not_equal, greater_than, etc. → squirrel Eq, NotEq, Gt, GtOrEq, Lt, LtOrEq, Between
• _or(...) → squirrel Or{}
• field("field1") → squirrel Eq{"A": squirrel.Expr("field1")}

DDL functions

Looks complete. One note: PostgreSQL does not support DISABLE INDEX / ENABLE INDEX as first-class DDL (that is MySQL/MSSQL). The closest PostgreSQL equivalent involves pg_index.indisvalid catalog manipulation — not appropriate in migration context. Safe to drop those two from the surface; drop_index covers the abort-and-recreate pattern.

record_migration_script and this object

Temp table strategy is correct for non-atomic migrations. One gap: if the process dies mid-migration, the orphaned temp table needs handling on restart. Recommend a resume gate on startup: detect temp tables matching the naming convention (_bakeoff_migration_<table>_<version>) and surface them for operator decision (continue / rollback / drop).

For this mutability — set("field", value) and this.field = value can both be supported if this is a custom type backed by a map with change tracking. purge("field") should write to a separate delete-set so the runtime knows to omit the field on write-back rather than write nil.

ignore() and --ignore-is-fatal

Confirmed. Recommend a companion --dry-run flag: run all scripts, track reject/ignore counts, report without committing. Useful for pre-flight validation of migration scripts against production data.

Conditions not addressed

• FK constraint deferral during migration (SET CONSTRAINTS DEFERRED or FK disabling around bulk moves)
• Transaction scope: per-record vs. batch commit (batch faster; per-record safer for partial failure; should be configurable)
• Progress checkpointing: for large tables, ability to resume from last committed row ID rather than restart from zero
• Concurrency: advisory lock on source table during migration to block concurrent writers

Phase 1 sign-off path

Once the abstraction layer choice is confirmed (squirrel + pgx recommended), I can draft the full function signature table (name, args, return type, error contract) as the Phase 1 deliverable for this issue. Confirm the layer and I will proceed.

— Bastion

[AlbinoGeek]

Phase 1 — Go Template Function Signatures for Migration Scripts

Abstraction layer confirmed as squirrel (query building) + pgx/v5 (PostgreSQL execution), per the prior comment. Signatures below are designed against that stack.

---

1. schema_migration_script — DDL functions

These execute against the live database. Each function compiles to a SQL statement, executes immediately, and returns an error string on failure (empty string on success). Templates should {{- if $err := ... }} {{ fail $err }} {{ end }} on any return.

Signature	Behavior
createTable name cols	CREATE TABLE name (...). cols is a list of {name, type, nullable, default, unique, primaryKey} maps.
dropTable name	DROP TABLE IF EXISTS name.
renameTable old new	ALTER TABLE old RENAME TO new.
addColumn table col	ALTER TABLE table ADD COLUMN .... col is a single column map (same shape as cols element in createTable).
dropColumn table col	ALTER TABLE table DROP COLUMN IF EXISTS col.
renameColumn table old new	ALTER TABLE table RENAME COLUMN old TO new.
createIndex table name cols unique	CREATE [UNIQUE] INDEX name ON table (cols...). unique is bool.
dropIndex name	DROP INDEX IF EXISTS name.
rawSQL stmt	Execute arbitrary SQL. Escape hatch; prefer typed helpers above.

PostgreSQL note: DISABLE INDEX / ENABLE INDEX are not supported (MySQL/MSSQL only). Use dropIndex + createIndex around bulk data moves instead.

Template context (.) for schema_migration_script:

.SrcTable     schema_tables row (table_id, table_name, uuid_namespace, current_version_id, ...)
.DstTable     schema_tables row for destination (nil for in-place minor upgrades)
.SrcVersion   schema_versions row (schema_version_id, description)
.DstVersion   schema_versions row being migrated toward
.JoinRow      schema_tables_join row (src_table, dst_table, migrate_using); nil for minor upgrades

---

2. record_migration_script — record-level functions

Executed once per source record. The this object is populated by the runner before template execution; mutations to it are the output. Commit strategy (per-record vs. batch) is runner-configurable.

Reading and writing fields

Signature	Behavior
getField field	Returns the value of this[field]. Equivalent to {{ .This.field }}.
setField field value	Sets this[field] = value. Marks field dirty for write-back.
deleteField field	Adds field to the delete-set; field is omitted on write-back rather than written as nil.

Record disposition

Signature	Behavior
rejectRecord reason	Aborts migration of this record. Runner increments reject counter, logs reason with record ID and source table. Migration continues to next record unless --reject-is-fatal is set.
ignoreRecord reason	Skips this record (omits from destination). Runner increments ignore counter and logs reason. --ignore-is-fatal treats any ignore as a fatal error.

Querying other tables

Signature	Behavior
select table where fields	Returns []map[string]any. where is a squirrel-compatible map or string clause. fields is a string list or empty for *. Returns empty list on no match; returns error map on SQL error.
selectFirst table where fields	Like select with LIMIT 1. Returns single record map, nil on no match, error map on SQL error.
selectOne table where fields	Like selectFirst but errors if row count ≠ 1.

where supports squirrel operator helpers exposed as sprig-style functions:

Helper	SQL equivalent
valueIn vals	IN (vals...)
notEqual val	!= val
greaterThan val	> val
greaterThanOrEqual val	>= val
lessThan val	< val
lessThanOrEqual val	<= val
between a b	BETWEEN a AND b
orWhere clauses	OR (...) — wraps squirrel Or{}
notWhere clause	NOT (...) — wraps squirrel NotEq / expression
fieldRef field	Resolves a column reference (for WHERE A = col_name)

Hashing / string helpers (sprig extensions)

Standard sprig string and type functions are available (lower, upper, trim, replace, toString, toInt, toFloat64, default, etc.).

Additional bakeoff-specific extensions:

Function	Behavior
sha256sum val	Returns hex SHA-256 of the string representation of val.
sha512sum val	Returns hex SHA-512.
uuidV5 namespace name	Returns a UUID v5 (namespace + name). Useful for deterministic ID re-keying during migrations.
nowUTC	Returns current UTC time as RFC3339 string. Useful for stamping migrated_at fields.

Template context (.) for record_migration_script:

.This         current record (map[string]any, mutable via setField/deleteField)
.SrcTable     schema_tables row for source table
.DstTable     schema_tables row for destination table
.SrcVersion   schema_versions row being migrated from
.DstVersion   schema_versions row being migrated to
.RecordIndex  int — zero-based ordinal of this record in the migration batch
.TotalRecords int — total record count for this migration run (may be -1 if streaming)

---

3. Error handling contract

Condition	Behavior
rejectRecord called	Record not written to destination. Reject counter incremented. Details logged (record ID, source table, reason, script line). Migration continues unless --reject-is-fatal is set.
ignoreRecord called	Record not written to destination. Ignore counter incremented. Logged as above. Fatal if --ignore-is-fatal is set.
Template execution error (syntax, missing field)	Counts as implicit reject for that record; error logged with full template trace.
rawSQL or select* SQL error	Returns error map; template is responsible for branching. Recommend {{ if .Error }} {{ rejectRecord .Error }} {{ end }} pattern.
Mid-migration process death	Temp table (_bakeoff_migration_<table_name>_<version_id>) remains. On next runner startup, detect orphan temp tables and surface operator decision: continue (resume from last committed row), rollback (drop temp table), or inspect.
FK constraints during bulk move	Runner acquires advisory lock on source table; sets SET CONSTRAINTS ALL DEFERRED for the migration transaction.

Partial-migration behavior: the runner creates a shadow table (_bakeoff_migration_<table_name>_<version_id>) using the destination DDL. Records migrate into the shadow table. Only after all records are processed (or --reject-is-fatal / --ignore-is-fatal triggers abort) does the runner atomically rename old → _bakeoff_old_<table_name> and shadow → <table_name>. This keeps the source intact throughout and allows rollback without data loss.

---

4. --dry-run and --ignore-is-fatal / --reject-is-fatal flags

• --dry-run: execute all scripts, accumulate reject/ignore/error counts, report without committing any writes. Useful for pre-flight validation against production data.
• --reject-is-fatal: any rejectRecord call aborts the entire migration immediately.
• --ignore-is-fatal: any ignoreRecord call aborts the entire migration immediately.

---

5. Open questions for sign-off

1. Transaction scope — per-record commit or configurable batch size (e.g. --batch-size N)? Batch is faster; per-record is safer for partial failure resume.
2. Progress checkpointing — resume from last committed row ID on restart, or always restart from row 0 within the shadow table strategy?
3. Concurrency — advisory lock on source table is sufficient, or should writers be blocked at application layer too?
4. rawSQL in record_migration_script — allow or restrict? Unrestricted rawSQL in per-record context has injection risk if record values are interpolated carelessly.

---

This is Phase 1 only — function signatures and contract documentation. Phase 2 implementation of the migration runner awaits human sign-off on the above.

— Bastion // 042004ZJUN26

[AlbinoGeek]

Bastion — PHASE 1 OPEN QUESTIONS RESOLVED — 042309ZJUN26

Addressing the four open questions from the Phase 1 signature comment above. Bastion ruling on each; implementation may proceed once gissf1 confirms or objects.

---

1. Transaction scope — per-record or batch?

Ruling: configurable batch with safe default.

Default --batch-size 100. Set --batch-size 1 for per-record semantics. Batch is 10–100× faster for large tables; per-record allows restart at any row without re-processing. The shadow table strategy makes per-batch commit safe: a crash mid-batch means only the uncommitted batch is lost — the runner resumes from last_migrated_row_id, which is the last successfully committed batch boundary.

Recommend tracking last_migrated_row_id in a _bakeoff_migration_state metadata table (keyed by table_name + version_id) rather than the shadow table itself, so restart logic doesn't require counting rows in the shadow table.

---

2. Progress checkpointing — resume from last committed row or restart from 0?

Ruling: resume from last committed row.

Restart from 0 is not acceptable for production data sets (potentially millions of rows). The runner must write last_migrated_row_id to _bakeoff_migration_state after each successful batch commit. On restart, detect orphan shadow table → read last_migrated_row_id → skip source rows with id <= last_migrated_row_id → resume. The resume gate from the prior comment is confirmed: detect orphan → surface operator decision (continue / rollback / inspect) before touching anything.

---

3. Concurrency — advisory lock sufficient?

Ruling: advisory lock is sufficient within the same PostgreSQL connection pool; document the external-writer caveat.

pg_try_advisory_lock(hash_record('bakeoff_migration', table_name)) acquired at migration start. Released on commit or rollback. Any other connection in the same DB that also respects the advisory lock will block correctly. External writers (application code that does not check the lock) will not be blocked — this is a coordination protocol, not an enforcement mechanism. Document this explicitly: callers must check for advisory lock presence before writing to tables undergoing migration, or stop the application during migration runs. No additional mechanism needed for the common case (migration run against a quiesced system).

---

4. rawSQL in record_migration_script — allow or restrict?

Ruling: restrict. rawSQL is removed from record_migration_script entirely.

Rationale: record values interpolated into a raw SQL string in per-record context is a structural injection risk, even in an internal tool. The select / selectFirst / selectOne family covers all read needs. setField / deleteField cover all write needs. If a migration truly requires an operation the typed helpers cannot express, the correct path is to extend the typed helper surface — not to open a rawSQL escape hatch at the per-record level.

rawSQL remains available in schema_migration_script (DDL context) where the template author controls all input and no record values are interpolated.

---

Phase 1 status

With these four questions resolved, Phase 1 is complete as a design document. The function signature table (Schema 1 and Schema 2 above) + this resolution comment constitute the full Phase 1 deliverable.

Phase 2 gate: Confirm squirrel + pgx/v5 selection (or propose alternative), then implementation may begin. No blocking open questions remain from Bastion's side.

Tagging @gissf1 for review of these four resolutions before Phase 2 begins.

— Bastion // 042309ZJUN26

[gissf1]

RE: DB abstraction layer: agreed on squirrel + pgx/v5

RE: mapping DSL to squirrel:

• select(): agreed
• select_first: agreed
• select_one: should do at least LIMIT 2 so it can error if returned rows != 1 exactly.
• instead of our custom names ("not_equal", "grewater_than", etc), let's just use squirrel's naming scheme for the comparison functions ("NotEq", "Gt", etc); they are equally readable and functional so no need to create new names
• Same for _or(...), we can use Or{..} provided it offers the same syntax, or worst case, we can make it a function like Or(...) with the same parameters.
• unless a nice and short syntax exists within squirrel, we should still define field(...) to call squirrel.Expr(...) because that is far too long for our usage.

RE: DDL functions: understood on PGSQL lacking enable/disable index functionality. We could still create the functions and map them to CREATE/DROP INDEX, but I think a better syntax would be to have a block, like a with block, that disables indexes for that block only. This ensures we can store the field's index description ahead of deleting it, and ensures it is restored when the block completes. We can still call DDL for dropping an index using raw_sql, as it's not much simpler than creating a new function, given how often it will be used.

RE: record_migration_script temp tables:

• temp table handling on startup: Do we plan to support resuming a table migration? I think that's an important question. I agree with allowing operator choice for now, but we may want to just delete the temporary tables at some point. We can decide to automate this deletion later
• temp table naming suggestion is acceptable.

RE: this object:
I have one question: does the current migration script environment already have a this object? if so, we should probably map onto a property of the existing object (perhaps this.rec for example), rather than taking over the whole object. If none exists currently, then we can just use the whole object since we're creating it anyhow.

As long as there is no existing "this" object, I agree with your assessment regarding "For this mutability"; if there is an existing "this" object, let's discuss how to best integrate those features into it as properties.

RE: ignore() and --ignore-is-fatal: agreed on all points.

RE: Conditions not addressed:

FK constraint deferral during migration: I think we should schedule table migrations in such a way to avoid FK dependency issues. So hierarcically:

• start with tables that have no FKs, but are used as FKs from other tables
• move on to tables that only depend on already migrated tables
  • recurse
• When we are left with tables that are self-referential (directly or indirectly):
  • have a deferral queueing process for handling records that do not yet have all their FK targets available
  • migrate records that have all their FK targets available
    • recurse
  • we should be left with self referential records or order invariant records which should be created "simultaneously, in batches, for each set of dependent records.

RE: Transaction scope: per-record vs. batch commit: agreed on both; default to batch in the long term, but doing per-record for now is simpler to code, so let's start with that and implement batching in phase 2.

RE: Progress checkpointing: defer to phase 2 when we reconsider the temp tables user prompt.

RE: Concurrency: I think this is phase 2 also. Eventually though, I think you have the right appoach.

Will reply further in next message.

[AlbinoGeek]

Bastion — RESPONSE TO @gissf1 — 042334ZJUN26

Addressing each point from your message:

---

select_one — LIMIT 2 correction

Confirmed. LIMIT 2 is the correct implementation: fetch up to 2 rows, error if count ≠ 1. LIMIT 1 would silently pass when 2+ rows match. Signature table updated accordingly.

---

Operator naming — use squirrel's native names

Agreed. Drop the custom aliases (not_equal, greater_than, etc.) in favor of squirrel's NotEq, Gt, GtOrEq, Lt, LtOrEq, Between, Or{}. Keep field() as a short alias for squirrel.Expr() — agreed that the raw squirrel form is too verbose for template use.

Or(...) as a short function wrapper around Or{} — agreed if squirrel does not natively expose a variadic function form.

---

Index management — with block pattern

Good call. Replacing disable_index / enable_index with a withIndexDisabled table indexName block construct:

{{ withIndexDisabled "table_name" "index_name" (block) }}

Runtime behavior:

1. Fetch current index definition from pg_indexes (preserving DDL)
2. DROP INDEX index_name
3. Execute block body
4. Recreate index using stored DDL

This is safer than manual drop/recreate (no risk of losing the definition mid-migration), and cleaner template syntax than requiring the author to manually track index DDL. I'll add this to the function surface table.

---

this object — answer to your question

No existing this object in the current migration script environment. Go templates use . (dot) as the root context, which is the runner-supplied struct (.SrcTable, .DstTable, .SrcVersion, .DstVersion, .JoinRow for DDL scripts; .This, .SrcTable, etc. for record scripts). We are creating this fresh — nothing to conflict with.

Recommendation: expose the current record as .This on the template context (matching the existing .SrcTable naming convention), rather than naming it this directly at the root. Template access becomes {{ .This.field_name }} and {{ setField "field_name" value }} as proposed. No property nesting needed (this.rec is not required since .This is already unambiguous).

---

FK constraint deferral — hierarchical ordering

Agreed. Migration order:

1. Leaf tables (referenced by others, no outbound FKs)
2. Dependent tables (all FK targets already migrated) — recurse
3. Self-referential tables (direct or indirect cycles):
   • Deferral queue for records whose FK targets are not yet migrated
   • Migrate records with all FK targets available first
   • Recurse on the deferral queue
   • Final pass: batched creation of remaining self-referential sets

This is the right approach. Runner should compute the migration order graph at startup by reading the schema and sorting topologically. Cycles are detected and flagged for the deferral path. I'll document this as part of the Phase 2 runner design.

---

Transaction scope, checkpointing, concurrency — Phase 2

Confirmed. Per-record commit in Phase 2 (simplest to implement). Checkpointing + concurrency + batching deferred to a later phase. No changes to current deliverable.

---

Phase 1 status

squirrel + pgx/v5 confirmed by @gissf1. All four open questions resolved and accepted (with modifications above). Phase 1 is complete.

Phase 2 may begin. Updated signature surface (incorporating select_one LIMIT-2 fix, squirrel native operator names, withIndexDisabled block) will be reflected in the implementation — no need to re-document Phase 1 for these minor refinements; they are design decisions locked at implementation time.

Awaiting your next message (@gissf1) before closing this issue.

— Bastion // 042334ZJUN26

[gissf1]

RE: template format:
Previously you said: Templates should {{- if $err := ... }} {{ fail $err }} {{ end }} on any return.

I think we should eliminate the double-curly-brace requirements for all scripts in the DB. So your example should become more pure code like: if $err := ... { fail $err }

RE: Migration continues to next record unless --reject-is-fatal is set: reject should be fatal by default, and I don't think we need any --reject-non-fatal since that action can be accomplished with ignores, which should be non-fatal by default. We may allow the pass to process other records despite a rejection though so we can resume, then report the entire reject list before aborting. There should also be a limit specified as to how many rejects it is allowed to skip before considering it a complete failure; it doesn't make sense to report the entire table's records in a reject list. I think aborting after the first 10 rejects by default makes sense; it would allow us to resume later after having made some progress, even when there are rejects to prevent a completion on the whole. It may also help to randomize the order of processing migration records a bit after the first rejection so we can try to complete more of the table migration instead of always aborting after the same first 10 records when we retry.

RE: where supports squirrel operator helpers exposed as sprig-style functions: I think we should use the squirrel operator names whenever possible to standardize the code. Anything not in squirrel or without another standard name (for example from pgx/v5), or when it has a conflicting name, we can fall back to your suggested names.

RE: Additional bakeoff-specific sprig extensions:

I think your suggestions are a good start, but we need to expose various objects to support any hashing methods or string operations. I think having just SHA functions is too limiting; maybe something like: hash(method, content), where method is a string like "sha256" or any other hashing methods supported by the system.

uuidV5 would be better named uuid_5

Where are we using RFC3339 that makes nowUTC useful?

I do think we should offer now() (or .Now) which would return an object (or be the object) representing the wall clock at call time, which can then have methods to generate strings in various formats.

RE: Error handling contract: Template execution error (syntax, missing field): this should always be fatal, more strictly than reject even.

RE: Error handling contract: Partial-migration behavior:

we should never swap a partially migrated table to replace the original/old one until after the migration is complete. We can optionally resume the migration into the same temp table, but until that temp table is complete, the original table name should refer to the original table. If the table schema isn't changing and the UUID isn't changing, then we don't even need the temporary table, so that is fine to do inline without a full migration.

RE: rawSQL in record_migration_script: allow for now in per-record context until we have a few migrations done to determine we have all necessary SQL functions. Ensure any DB records using it have been migrated away and get confirmed approval before removing them. Security concerns are acknowledged, but compatibility and unrestricted capability is more important than security concerns for this table in phase 1. After we have some records in the database and are comfortable with the functions presented not needing raw SQL access, we can remove the rawSQL function. let's resurface this question within a month (or when we have sufficient migrations that we can be confident that it is unnecessary).

RE: configurable batch size: I think it makes sense to do an exponential growth (and then linear/calculated backoff after) until the sooner of:

• batches take at least 1s to commit (database commits should not be so large that they put excessive strain on the DB server)
• batches take more than 20 seconds to complete (we don't want to lose progress by processing such large batches that a mid-batch failure causes a significant loss of time spent processing.
• batches take about 75% of either of the above target time limits, in which case, we should try to use that with an average going forward.

I think it makes sense to start with batch size of 64, then begin exponential growth (next batch would be 128).

Once we reach the time limits, we can determine a backoff rate based on the approximate overage beyond the limit. So, for example, let's say we completed processing a batch of 1024 records in 10 seconds, and it took 1.2 seconds to commit, it makes sense to attempt to target around 853 records in the next round to get closer to the 1.0s limit. the time and record counts should be tracked for the last few commits (10 at most) so we can use that to generate average bulk processing rates that are less affected by transient circumstances (short term system load, network traffic, hardware delays, etc). We could use a running average that is somewhat biased to the latest value (so calculated like: ((previous_average_rate * 3) + current average_rate) / 4), as that would both allow us to ramp up quickly and respond to transient effects.

if the user specifies --batch-size, it should be used as a fixed value, not subject to the above dynamic sizing. if they specify the size of 0, that should imply the above dynamic sizing. Specifying a negative That said, it may make sense to give a warning to the user if batches take excessive time to complete. It may also make sense to have a flag like --batch-target-processing-time for setting the processing time target (maybe the user would want to process for 5 minutes per batch instead of 20 seconds). We can also do something like --batch-target-commit-time for the 1s commit time, but I feel like that will be a less useful flag.

RE: Recommend tracking last_migrated_row_id in a metadata table rather than the shadow table itself: agreed, but I think this should be a temporary table that only exists until the table migrations complete.

RE: this object: Agreed to recommendation of .This on the template context for the current record.

Let's be sure to address all questions before beginning. but I think we are close to completion of this ticket.

[AlbinoGeek]

Bastion — RESPONSE TO @gissf1 — 050429ZJUN26

Addressing all points from your latest message before Phase 2 begins.

---

RE: template format — eliminate double-curly-brace

Agreed. Go template syntax ({{ }}) is the wrong surface for a scripting host where humans write migration logic. Proposing a replacement now.

Options:

1. Tengo (github.com/d5/tengo) — Go-like syntax, compiles to bytecode, fast; your example maps directly: if err := createTable(...) { fail(err) }.
2. Starlark (go.starlark.net) — Python-like syntax, designed for embedded scripting, deterministic execution, established Go bindings.
3. Expr (github.com/expr-lang/expr) — expression language; too limited for scripts with loops and conditionals.

Recommendation: Tengo — syntax closest to your example, idiomatic for a Go-native codebase. Sample:

err := createTable("users", cols)
if err != "" { fail(err) }

rec := selectOne("old_table", {id: this.id}, [])
if rec.error { rejectRecord(rec.error) }
setField("migrated_at", now().rfc3339())

Confirm language choice. This is the blocking question for Phase 2 start — function binding surface and library dependencies depend on it.

---

RE: reject behavior

Confirmed changes:

• Reject is fatal by default — no --reject-is-fatal flag needed.
• No --reject-non-fatal — use ignoreRecord for non-fatal skips.
• Runner accumulates reject list before aborting; continues until --max-rejects threshold (default 10).
• After first rejection, randomize processing order — allows partial progress on retry rather than always failing on the same leading records.

Revised flag surface:

• --max-rejects N (default 10): abort after N accumulated rejects. 0 = fail on first. -1 = collect all, report at end, never abort mid-run.
• --ignore-is-fatal (unchanged).
• --reject-is-fatal removed (reject is fatal by default).

---

RE: squirrel operator names

Confirmed — use squirrel native names (Eq, NotEq, Gt, GtOrEq, Lt, LtOrEq, Between, Or{}). Custom names only when squirrel lacks the operation or name conflicts. Keep field() as short alias for squirrel.Expr(); keep Or() as variadic wrapper around Or{}.

---

RE: hash function generalization

Agreed. Replacing sha256sum / sha512sum with:

hash(method, content) -> string

method is a string ("sha256", "sha512", "md5", "blake2b", etc.) — delegates to Go's crypto and golang.org/x/crypto packages. Unknown method returns an error string, not a panic.

---

RE: uuid_5 rename

Confirmed. uuidV5 → uuid_5.

---

RE: nowUTC → now()

Confirmed. nowUTC removed. Replacing with:

now() -> TimeObject

TimeObject exposes: .unix() (int64), .rfc3339() (string), .format(layout string). Wall clock captured at call time.

---

RE: template execution errors — always fatal

Confirmed. Error hierarchy:

1. Script error (syntax, unresolved variable) → fatal, abort migration entirely, no records written
2. rejectRecord called → per-record failure, counted against --max-rejects
3. ignoreRecord called → per-record skip, counted separately

---

RE: partial-migration behavior

Confirmed: shadow table never swapped until migration 100% complete.

Inline migration path also confirmed: if DDL is null/no structural change AND UUID namespace unchanged, runner migrates against the live table without a shadow table. Runner detects this at startup by comparing src/dst schema fingerprint and UUID namespace. Inline path is an optimization, not the default.

---

RE: rawSQL in record_migration_script — restored for Phase 1

Confirmed. rawSQL reinstated in record_migration_script for Phase 1. Removal gate: all existing record_migration_script records confirmed not using rawSQL + NOMAD approval. Will resurface no later than 30 days after first production migrations are recorded.

---

RE: dynamic batch sizing

Confirmed. Implementation spec:

• Start: 64 records
• Growth: exponential doubling until commit time ≥ 1.0s or processing time ≥ 20s
• Target: ~75% of whichever limit is hit first
• Moving average: (prev_avg × 3 + curr_avg) / 4, window last 10 commits
• --batch-size N (N > 0): fixed, no dynamic adjustment; warn if batches exceed time targets
• --batch-size 0: dynamic sizing (default)
• --batch-target-processing-time T: override 20s target (lower priority)
• --batch-target-commit-time T: override 1s commit target (lowest priority)

One open question: should --batch-size -1 mean unbounded (one batch, all records) or invalid? I recommend invalid — unbounded batches bypass safety limits and can produce multi-hour single commits on large tables. Confirm before implementation.

---

RE: metadata table — temporary

Confirmed. _bakeoff_migration_state dropped when migration completes (shadow table swap commits) or when operator selects rollback on the resume gate.

---

RE: .This object

Confirmed. No change.

---

Phase 1 final status

All questions addressed. Delta from prior Phase 1 comment:

Item	Change
Template syntax	Pending language choice (Tengo recommended) — blocking
rawSQL in record context	Restored for Phase 1
Reject semantics	Fatal by default; --max-rejects controls threshold
Reject processing	Randomize order after first rejection
Hash functions	Generalized to hash(method, content)
uuidV5	Renamed uuid_5
nowUTC	Replaced with now() returning TimeObject

Phase 2 blocking question: confirm scripting language — Tengo vs. Starlark vs. other. Implementation cannot begin until language is confirmed. All other questions are resolved.

— Bastion // 050429ZJUN26

[gissf1]

RE: template format -- eliminate double-curly-brace:

Python-like syntax is wrong for this. Expr is also not right; we need logical short code sequencing, not just a plain expression.

I'm open to considering Tengo, but I am not confident that it is a good fit without some questions:
Would Tengo replace sprig?
Can Tengo better import complete go libraries in a safe and efficient manner?
Can we use semicolons to put multiple calls on the same line (without embedded newlines, to better fit in a DB field)?
Does Tengo provide easier access to hashing functions and other such mechanisms?
Does Tengo support objects and referencing the input object as we had described (having a this object, etc)?

RE: reject behavior: agreed on all.

RE: squirrel operator names: agreed on all.

RE: dynamic batch sizing:

if we're using the moving average, we probably don't need to track 10 commits in memory using arrays; the moving average can be just the total time and total records, updated for each batch, and next dynamic batch size can be calculated per a variation of the formula like:
next_batch_size = target_time * ((3 * total_records / total_time) + (last_batch_size / last_batch_time))

This means we only have to track the integers total_records and last_batch_size, and floats total_time and last_batch_time; not a full array of them.

Also, warning if batches exceed time targets should apply to any mode (dynamic or fixed batch size), but it should only warn once per run, not each time it exceeds the target.

--batch-size -1 should be invalid: agreed. The user can specify crazy sizes or time limits if they want, but there's no reason to allow a purely unlimited batch size.