Add native asset/file loader for binding local files as query parameters during migrate-apply #7

New Issue

2026-07-15T14:19:09Z

warkanum commented

2026-07-15 14:19:09 +00:00

Context

Related to #6, but proposing a different (preferred) design after more thought.

We need to load local binary/text asset files (e.g. report template .md/.docx files under sql/vault/borg/data/templates/finance/) into Postgres columns (bytea/text) as part of make migrate-apply, alongside the existing relspec scripts execute seeds/scripts pipeline.

#6 proposed a SQL-comment @embed directive that gets textually inlined into the .sql file before execution. On reflection that's the weaker design: it still round-trips binary data through a SQL text literal (base64/dollar-quoting), which is exactly the kind of encoding/escaping surface area we're trying to avoid, and it couples file-reading logic into the SQL-text preprocessing step.

Proposed feature instead: a native Go asset loader

Add a new loader/writer path (e.g. relspec assets execute alongside the existing relspec scripts execute) that:

Reads a small manifest (YAML/JSON, e.g. assets.yaml colocated with the asset files) describing, per file: the source file path, and the SQL statement/function call to invoke for it, with named placeholders for "file bytes" and "filename" (and any static extra columns/params the manifest supplies).
Executes each entry via pgx with the file's bytes bound as a real query parameter ($1::bytea etc.) — never converted to a SQL text literal, so no base64/escaping, no size-related SQL parsing issues, and binary files stay byte-exact.
Slots into the same [priority]_[sequence]_[name] ordering convention already used for .sql scripts/seeds, so asset-loading steps can be interleaved correctly with scripts execute in a single migrate-apply run.
Is generic — not tied to any particular schema/table — so it's reusable for template files, logo/branding uploads, seed attachments, etc. across any relspec-consuming project, not just this one.

Example manifest shape (illustrative, not prescriptive)

# sql/vault/borg/data/templates/finance/assets.yaml
- file: invoice.md
  call: |
    INSERT INTO org.filepointer (rid_owner, filename, contenttype, jsonstore)
    VALUES (1, :filename, 'text/markdown', jsonb_build_object('content', :bytes::text))
- file: invoice.docx
  call: |
    INSERT INTO org.filepointer (rid_owner, filename, contenttype, jsonstore)
    VALUES (1, :filename, 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', jsonb_build_object('content', encode(:bytes, 'base64')))

(:bytes bound as []byte via pgx, :filename as the base name of file — exact placeholder syntax up to whoever implements it, could reuse whatever variable-substitution convention funcspec/sqlexec already has internally.)

Relationship to #6

This supersedes #6 as our preferred direction — recommend closing #6 in favor of this one, or keeping both open as alternatives if there's a simpler use case elsewhere that still wants the lighter-weight @embed text-inlining behavior. Leaving that call to the maintainer.

Workaround in the meantime

Same as noted in #6: generate the seed .sql literals from source assets via a small script invoked before make migrate-apply, committed as a generated file, until native support exists.

## Context Related to #6, but proposing a different (preferred) design after more thought. We need to load local binary/text asset files (e.g. report template `.md`/`.docx` files under `sql/vault/borg/data/templates/finance/`) into Postgres columns (`bytea`/`text`) as part of `make migrate-apply`, alongside the existing `relspec scripts execute` seeds/scripts pipeline. #6 proposed a SQL-comment `@embed` directive that gets textually inlined into the `.sql` file before execution. On reflection that's the weaker design: it still round-trips binary data through a SQL text literal (base64/dollar-quoting), which is exactly the kind of encoding/escaping surface area we're trying to avoid, and it couples file-reading logic into the SQL-text preprocessing step. ## Proposed feature instead: a native Go asset loader Add a new loader/writer path (e.g. `relspec assets execute` alongside the existing `relspec scripts execute`) that: 1. Reads a small manifest (YAML/JSON, e.g. `assets.yaml` colocated with the asset files) describing, per file: the source file path, and the SQL statement/function call to invoke for it, with named placeholders for "file bytes" and "filename" (and any static extra columns/params the manifest supplies). 2. Executes each entry via `pgx` with the file's bytes bound as a real query parameter (`$1::bytea` etc.) — never converted to a SQL text literal, so no base64/escaping, no size-related SQL parsing issues, and binary files stay byte-exact. 3. Slots into the same `[priority]_[sequence]_[name]` ordering convention already used for `.sql` scripts/seeds, so asset-loading steps can be interleaved correctly with `scripts execute` in a single `migrate-apply` run. 4. Is generic — not tied to any particular schema/table — so it's reusable for template files, logo/branding uploads, seed attachments, etc. across any relspec-consuming project, not just this one. ### Example manifest shape (illustrative, not prescriptive) ```yaml # sql/vault/borg/data/templates/finance/assets.yaml - file: invoice.md call: | INSERT INTO org.filepointer (rid_owner, filename, contenttype, jsonstore) VALUES (1, :filename, 'text/markdown', jsonb_build_object('content', :bytes::text)) - file: invoice.docx call: | INSERT INTO org.filepointer (rid_owner, filename, contenttype, jsonstore) VALUES (1, :filename, 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', jsonb_build_object('content', encode(:bytes, 'base64'))) ``` (`:bytes` bound as `[]byte` via pgx, `:filename` as the base name of `file` — exact placeholder syntax up to whoever implements it, could reuse whatever variable-substitution convention `funcspec`/`sqlexec` already has internally.) ## Relationship to #6 This supersedes #6 as our preferred direction — recommend closing #6 in favor of this one, or keeping both open as alternatives if there's a simpler use case elsewhere that still wants the lighter-weight `@embed` text-inlining behavior. Leaving that call to the maintainer. ## Workaround in the meantime Same as noted in #6: generate the seed `.sql` literals from source assets via a small script invoked before `make migrate-apply`, committed as a generated file, until native support exists.

warkanum closed this issue

2026-07-16 19:45:08 +00:00

warkanum reopened this issue

2026-07-16 19:45:20 +00:00

warkanum referenced this issue

2026-07-16 19:45:43 +00:00

Support external file embedding in seed/script SQL (for binary/text asset inlining) (redo) #6

sgcommand referenced a pull request that will close this issue

2026-07-17 00:46:00 +00:00

feat(assets): add native Go asset/file loader for migrate-apply (#7) #8

sgcommand commented

2026-07-17 00:49:15 +00:00

Implementation complete. PR #8: #8 — Branch: issue-7-native-asset-loader — Commit: 60c5cc40b2 — All 14 unit tests pass, make build and make test clean.

Implementation complete. PR #8: https://git.warky.dev/wdevs/relspecgo/pulls/8 — Branch: issue-7-native-asset-loader — Commit: 60c5cc40b233bfb69335415b0f19210f650f5ea5 — All 14 unit tests pass, make build and make test clean.

warkanum closed this issue

2026-07-18 20:35:46 +00:00

warkanum referenced this issue from a commit

2026-07-18 20:35:46 +00:00

Merge pull request 'feat(assets): add native Go asset/file loader for migrate-apply (#7)' (#8) from issue-7-native-asset-loader into master

sgcommand referenced this issue

2026-07-19 22:52:18 +00:00

feat(scripts): support external file embedding #12

sgcommand referenced this issue

2026-07-19 22:52:42 +00:00

Support external file embedding in seed/script SQL (for binary/text asset inlining) (redo) #6

Sign in to join this conversation.