Agent notes
Proposed better-supabase changes found while importing a large legacy administration, and the reason each one is hand-built today.
The AgroIT import loads tens of thousands of historical documents through the better-supabase import blocks. Four gaps showed up. Each one is written as a proposal for the library, because the blocks are generic and any software developer who migrates a legacy system would meet them. Until the library changes, CentraKit keeps the hand-written overrides described here.
An imported quote has to keep the status it had in the source, such as accepted or expired. The quote block only creates quotes as drafts and moves them through the normal transitions, so a historical expired quote fails with STATE_TRANSITION_INVALID and a quote with lines that no longer validate fails with LINES_INVALID. About 65 and 30 quotes of the AgroIT backup are affected.
Proposal: import_quote accepts status and the settlement timestamps as data, writes them on the row without running transitions, and skips line validation for a historical status, the same way the invoice override writes paid and partially_paid directly. A flag on the import item (historical: true) makes the intent explicit.
CentraKit today: the hand-written public.import_quote in supabase/schemas/platform/import_targets.sql keeps the historical status for new quotes. Quotes that failed in earlier runs still show as conflicts until the next run.
better_supabase.open_items_for_party(tenant, party_id, as_of, side) scans the whole ledger for every call. The call grows with the ledger, so a tenant with 12,000 imported ledger entries and 47,000 invoices pays for the whole history on every customer page.
Proposal: keep a maintained open-item table keyed by party and document, updated by the posting function, with an index on (tenant, party_id, side). open_items_for_party and open_item_ageing then read that table instead of folding the ledger. A rebuild function recomputes the table from the ledger for a tenant, which an import can call once at the end.
CentraKit today: no CentraKit page calls it. The invoice rows carry amount_paid_cents, so an open balance can come from the invoices.
The user asked for paid imported invoices to be settled in the ledger so that open-item queries are fast. This is not feasible with the current invoice block without double posting. allocate_payment creates a payment and posts a ledger entry. The AgroIT journals already contain the real payment entries, so allocating a payment per invoice would book every receipt twice and break the debit and credit totals that the integrity check verifies.
Proposal: an import_allocation subject that writes an invoice_allocations row linking an existing ledger entry line to an invoice, with no new posting. The importer maps the AgroIT journal line that closed an open item to that allocation. Together with the open-item table above, open-item queries become fast and the ledger stays as the source wrote it.
CentraKit today: paid invoices carry the settled state on the invoice row only, and the ledger keeps the entries the source had. The ledger and the invoice settlement are not linked by allocation rows.
import_apply_batch applies a staged batch one item at a time and calls the entity function per item. Each call resolves its references with several single-row queries. A full AgroIT run therefore takes a long time on the hosted database, and the cron worker drains the contacts of this backup at roughly 50 items per minute.
Proposal: let an entity function declare a set-based apply that receives the whole batch as a jsonb array, resolves references with one join against import_records, and writes with insert ... on conflict. The per-item path stays as the fallback and for entities with side effects such as issuing an invoice. A pgTAP test per entity compares the per-item and the set-based result on the same fixture before the set-based path is enabled.
CentraKit today: the per-item path is the only one. The client skips unchanged rows with a content hash (--state), so a repeat import sends only new and changed rows. A set-based path was not added because it needs a pgTAP suite for every entity that this work did not include.
Updating a customer reindexes every order, quote and asset of that customer through the search watch triggers. A large import of customers with long histories spends most of its time there.
Proposal: skip the watch triggers while better_supabase.import_run is set and rebuild the affected search rows once after the run. CentraKit today: import_customer skips updates that change nothing, which removes the cost for repeat imports but not for the first import of changed names.
A chunk whose message failed five times is archived and its row in import_chunks stays open, so the run never finishes. Proposal: mark the chunk and the run as failed when the last attempt fails, and let a repeat send re-enqueue open chunks. CentraKit today: re-enqueue by hand with pgmq.send.
public.import_invoice in supabase/schemas/platform/import_targets.sql is the hand-written invoice import. The imports dispatch in import_runner_overrides.sql calls it, and the block's own better_supabase.import_invoice stays in module_098_invoices.sql unused. The schema file and the hosted database drifted once, because a migration changed the function and the schema file kept the old text. When you change an override, change the schema file and write the migration in the same commit, then run pnpm supabase:diff to confirm it reports no change.
order_lines.line_type is product exactly when product_id is set, otherwise custom. Tax code keys are three characters, for example zzz, ICL and H19, and a two-character slice finds no code.
An update of one column that no search index reads, for example vat_number on a customer, still fires the search watch triggers of the customer. Each trigger reindexes every order, quote and asset of that customer. Clearing the VAT number of 1,060 customers on the hosted develop database ran as batches of 60 to 300 rows, because one statement over every row ran into the statement timeout.
Proposal: generate each watch trigger as after update of <columns the index reads> instead of after insert or delete or update, and let the trigger function return early when old and new agree on those columns. A pgTAP test updates an unindexed column and asserts that no search row changes. CentraKit today: no change, the hosted cleanup used those batches.
better_supabase.import_invoice and create_invoice fall back to the English title Invoice <number> when an import item has no title, and the column requires a title. An organization that works in another language gets 46,514 English titles that repeat the number the page already shows. Proposal: let the organization or the import source supply a title template, or allow an empty title that the page renders as the number. CentraKit today: the AgroIT mapper sets the title from the first detail line when the source has one, and public.import_invoice sets the title to the plain invoice number when the item has none.
The managed module_064_imports.sql and module_069_catalog.sql equal the generator output, so supabase:blocks:check and better-supabase gen --check pass. CentraKit changes to the imports functions live in supabase/schemas/platform/import_runner_overrides.sql, which loads after the module files. Declaring an imports.targets entry for invoice, quote or open_item does not remove the need, because the generator stops with Two blocks contribute "invoice" to imports: targets can only add entities, not replace the built-in ones.
Proposals for the library:
write override for built-in imports entities such as invoice, quote, open_item and credit_note, in the same shape as the imports.targets entries. The dispatch in import_apply_batch then calls public.import_invoice without a hand edit, and the block stops emitting its own function.import_upsert_batch: collect the external keys left unchanged because the subject was newer into a conflicts array in the result and write them to public.import_conflicts.cancel_import_run: delete the import_records rows whose first_run_id is the canceled dry run, the same cleanup that finish_import_run does.import_supplier_item: when supplierId is missing, resolve refs.customer through the imports map before raising a supplier item needs a supplierId. CentraKit carries it in public.import_supplier_item.CentraKit today: the override file replaces import_apply_batch (the quote, open_item and invoice dispatch lines and the conflicts tracking), import_upsert_batch, cancel_import_run and finish_import_run (the dry-run handling), and defines public.import_supplier_item with the customer ref fallback, which import_apply_batch calls instead of the generated function. After pnpm supabase:blocks, the managed files stay untouched and pnpm supabase:diff reports no change. When the generator changes one of these functions, copy the new text into the override file and re-apply the CentraKit edits there. Record any new override here.
Two import targets fail when the organization already holds data the source does not know about. import_task raised a unique violation when a hand-made task held the source number, and the generated import_stock_count raised when the named location code did not exist.
Proposals for the library: a fixed number that is already taken falls back to the next free number inside set_fixed_number consumers instead of failing, and an import target that names a location by code resolves the organization's default location, creating it when none exists. CentraKit today: import_task checks the number itself, and public.import_stock_count in import_runner_overrides.sql resolves or creates the location and delegates to the generated function.
Last updated on