Code explaination sync To DB

[Zack-Rider]

syncFormToDatabase — Line by Line Walkthrough

This document explains every block of syncFormToDatabase in server/src/services/forms.ts in plain English. Read this when someone asks "what does this function do?" or "why is this line here?"

---

Block 1 — Setup connections

const prisma = getPrisma(ctx);
const pool = await getPgPool(ctx);
const client = await pool.connect();

What: Gets two DB connections ready.

• prisma — used to read/write from the main app DB (forms, templates, org schema)
• pool — a pool of Postgres connections to the org's own dynamic DB (invoice, customers, etc.)
• client — one specific connection taken out of the pool. This is used for the transaction. A dedicated connection is needed because BEGIN/COMMIT/ROLLBACK only works on one connection — if you use a pool, different queries might go to different connections and the transaction breaks.

---

Block 2 — Load form data from Prisma

const form = await prisma.forms.findUnique({
  where: { id: input.formId },
  select: {
    orgId: true,
    createdById: true,
    conversationState: true,
    syncedAt: true,
    template: { select: { dbMapping: true } },
  },
});

What: Fetches the form from the app DB.

• conversationState — the full AI chat state: all values the user typed + table rows the AI computed
• dbMapping — comes from the template linked to this form. It's the config that says "write block X to table Y column Z"
• createdById — the user who created the form (stored as created_by_id in the documents table)
• syncedAt — if this is already set, the form was already synced before

const conversationState = form?.conversationState as any;
const dbMapping = form?.template?.dbMapping as any;
const filledPlaceholders = conversationState.filledPlaceholders;

What: Unpacks the three things we'll use throughout the function:

• conversationState — the whole chat state object
• dbMapping — the block config
• filledPlaceholders — a flat key-value map of everything the user typed, e.g. { invoice_number: "SSS/390/2025-26", bill_date: "01/11/2025", receiver_id: "uuid..." }

---

Block 3 — Find the TABLE layout block

const tableBlockEntry = Object.entries(dbMapping.blocks || {}).find(
  ([, block]) => block.mode === 'store' && block.layout === 'table',
);
const tableId = tableBlockEntry?.[0] ?? null;
const tableBlock = tableBlockEntry?.[1] ?? null;

What: Scans all blocks in dbMapping and finds the one that is a store block with layout: "table". This is the block that represents the hire ledger grid — the one that produces multiple rows.

tableId = the block key name (e.g. "hire_ledger_table")
tableBlock = the full block config (columns, rules, tableName, etc.)

Why: Instead of hardcoding "hire_ledger_table", we detect it dynamically so any future document type with a table block works automatically.

---

Block 4 — Get computed rows from tableState

const computedRows = conversationState.tableState?.['hire_ledger']?.computedRows ?? [];

What: Gets the AI-computed grid rows from the conversation state.

Why 'hire_ledger' is hardcoded: The dbMapping block key is hire_ledger_table but the tableState key is hire_ledger. They don't match, so we hardcode the tableState lookup. This is a known issue — future fix is to store tableStateKey in the dbMapping block config.

Each row in computedRows looks like:

{ "product_id": "uuid", "item_key": "Joint Pin", "period_start": "01/10/2025", "days": 31, "amount": 5200, ... }

---

Block 5 — Find customerId from connect blocks

let customerId = null;
for (const block of Object.values(dbMapping.blocks || {})) {
  if (b.mode === 'connect') {
    for (const link of b.links || []) {
      const val = filledPlaceholders[link.source?.placeholder];
      if (val) { customerId = val; break; }
    }
  }
}

What: Loops all connect blocks in dbMapping, finds the first one that has a value in filledPlaceholders, and uses that as customerId.

Why dynamic: The connect block's source.placeholder tells us which key to look up. For an invoice form it's receiver_id. For a contract form it might be something else. By reading from the config instead of hardcoding 'receiver_id', any form type works.

---

Block 6 — Get supplierId (hardcoded)

const supplierRow = await pool.query(
  `SELECT id FROM "suppliers" ORDER BY created_at ASC LIMIT 1`
);
const supplierId = supplierRow.rows[0]?.id ?? null;

What: Queries the org's suppliers table and picks the first row as the "issuing company".

Why hardcoded: There is no relationship between the org record and the suppliers table. The form also doesn't ask "which company is issuing this?" — the consignee block links to the form's consignee entity, not the issuing company itself.

Future fix: Add is_self: Boolean to suppliers. Query WHERE is_self = true.

---

Block 7 — Load org schema (orgTemplate)

const org = await prisma.org.findUnique({
  where: { clerkId: form.orgId },
  select: { dbSchema: true, clerkId: true, name: true, lowercaseName: true },
});
const orgTemplate = org.dbSchema as Template;
const orgSchema = org.dbSchema as any;

What: Loads the org's live schema from the app DB.

• orgTemplate — typed as Template, used when calling ensureFkRelation to add relations/fields
• orgSchema — same object but typed as any, used when checking if a column exists in the schema (orgSchema.tables['invoice'].fields['bill_date'])

Both point to the same object in memory — mutating orgTemplate also mutates orgSchema.

---

Block 8 — Auto-wire FK relations (store blocks)

for (const [, blockConfig] of Object.entries(dbMapping.blocks || {})) {
  if (block.mode === 'store' && block.tableName) {
    for (const col of block.columns || []) {
      if (!col.column?.endsWith('_id')) continue;
      const src = inferFkSourceTable(col.column, orgSchema);
      if (!src) continue;
      const changed = await ensureFkRelation(ctx, orgTemplate, src, block.tableName, col.column);
    }
  }
}

What: Loops all store blocks. For every column that ends in _id (e.g. product_id, customer_id), it tries to figure out which table that FK points to by calling inferFkSourceTable.

inferFkSourceTable("product_id") → strips _id → tries "product" then "products" → finds "products" in orgSchema → returns "products".

Then ensureFkRelation(ctx, orgTemplate, "products", "invoice_line_items", "product_id") is called, which:

1. Adds product_id to orgTemplate.tables['invoice_line_items'].fields
2. Adds products → invoice_line_items 1:M relation to orgTemplate.relations
3. Runs ALTER TABLE "invoice_line_items" ADD COLUMN IF NOT EXISTS "product_id" UUID

Why: New FK columns get detected and created automatically — no manual migration needed.

---

Block 9 — Auto-wire FK relations (connect blocks)

if (block.mode === 'connect') {
  for (const link of block.links || []) {
    const colName = link.to?.column;       // e.g. "customer_id"
    const targetTable = link.to?.tableName; // e.g. "documents"
    const src = inferFkSourceTable(colName, orgSchema); // → "customers"
    await ensureFkRelation(ctx, orgTemplate, src, targetTable, colName);
  }
}

What: Same as above but for connect blocks. A connect block says "set documents.customer_id to the receiver's ID." So we wire customers → documents as a 1:M relation.

---

Block 10 — Auto-wire parentFk from TABLE block rules

if (tableBlock?.rules?.parentFk) {
  const { sourceTable, column } = tableBlock.rules.parentFk;
  await ensureFkRelation(ctx, orgTemplate, sourceTable, tableBlock.tableName, column);
}

What: The TABLE block has a special rules.parentFk config set in the UI:

"parentFk": { "sourceTable": "invoice", "column": "invoice_id" }

This wires invoice → invoice_line_items as 1:M with invoice_id as the FK column. Without this, invoice_line_items rows would have no invoice_id column at all.

---

Block 11 — Auto-wire document_line_items FKs (hardcoded)

for (const [col, src] of [
  ['document_id', 'documents'],
  ['product_id', 'products'],
  ['customer_id', 'customers']
]) {
  await ensureFkRelation(ctx, orgTemplate, src, 'document_line_items', col);
}

What: document_line_items is always inserted (it's not in dbMapping), so the auto-wiring loop above never sees it. These three calls ensure its FKs are registered.

Why hardcoded: These three FKs are fundamental to document_line_items — they will always be needed regardless of document type.

---

Block 12 — Cache updated schema if anything changed

if (schemaChanged) {
  await cacheTemplate(ctx, orgTemplate);
}

What: If any new FK relation was added, cacheTemplate saves the updated orgTemplate back to Prisma (org.dbSchema) and deletes the Redis cache for this org. This ensures the new columns/relations show up in the UI (ERD, table view, etc.) immediately.

---

Block 13 — Read document metadata from dbMapping

const documentNumber = filledPlaceholders[
  dbMapping['document']['columns'].find(c => c.column === 'document_number').source['placeholder']
];
const documentType = dbMapping['document']['columns'].find(
  c => c.column === 'document_type'
).source['value'];
const date = filledPlaceholders['bill_date'];
const itemCount = conversationState.tableState?.['hire_ledger']?.debug?.lookup?.rowsScanned || computedRows.length;

What: Reads the document-level metadata that we'll need to create the documents row.

• documentNumber — read dynamically from dbMapping. The config says which filledPlaceholders key to use (e.g. "invoice_number"), so we look it up.
• documentType — read as a constant from dbMapping (e.g. "Invoice")
• date — bill_date from filledPlaceholders (hardcoded key for now)
• itemCount — total number of products. Tries to get the actual scanned row count from the AI's debug info, falls back to computedRows.length

---

Block 14 — Start transaction

await client.query('BEGIN');

What: Starts the Postgres transaction. Everything after this is atomic — either all writes succeed and get committed, or one failure rolls everything back.

---

Block 15 — Insert documents row

const lastDocOrder = (await client.query(`SELECT row_order FROM "documents" ORDER BY row_order DESC LIMIT 1`)).rows[0]?.row_order;
const newOrder = getGapPosition(lastDocOrder);

const newDocument = await client.query(`
  INSERT INTO "documents" (...) VALUES (gen_random_uuid(), $1, $2, ...) RETURNING id
`, [documentNumber, documentType, date, itemCount, createdById, customerId, supplierId, newOrder]);

const newDocumentId = newDocument.rows[0]?.id;

What: Creates one row in the documents table.

getGapPosition(lastDocOrder) — calculates the new row's position. The gap-based system leaves space between row positions (e.g. 1000, 2000, 3000) so you can insert a row between two others without renumbering everything.

RETURNING id — gets back the newly created row's UUID, which is needed by all later inserts as document_id.

---

Block 16 — Deduplicate rows (productRowMap)

const rowIdColumn = tableBlock?.rules?.rowIdColumn ?? 'product_id';
const productRowMap = new Map<string, any>();
for (const row of computedRows) {
  const rowId = row[rowIdColumn];
  if (rowId) productRowMap.set(rowId, row);
}

What: computedRows has one row per product per period. For example, "Joint Pin" appears 3 times if it appears in 3 billing periods. We only want one row per product in the DB.

This map uses product_id as the key — so if the same product appears multiple times, the last one wins (last period's values are kept).

rowIdColumn comes from rules.rowIdColumn in dbMapping (set in the FK Links UI). This makes deduplication dynamic — tomorrow if the row key is something other than product_id, it just works.

This map is then used for all three of: products_and_documents, document_line_items, and invoice_line_items.

---

Block 17 — Batch insert products_and_documents

const valueSets = rowIds.map((_, i) => `($${i * 2 + 1}, $${i * 2 + 2})`).join(', ');
const params = rowIds.flatMap((id) => [newDocumentId, id]);
await client.query(
  `INSERT INTO "products_and_documents" ("document_id", "product_id") VALUES ${valueSets} ON CONFLICT DO NOTHING`,
  params,
);

What: Links each unique product to this document. This is a junction table (many-to-many between documents and products).

All rows are inserted in one query regardless of how many products there are. 21 products = 1 query.

ON CONFLICT DO NOTHING — if this product was already linked to this document somehow, skip silently.

---

Block 18 — Batch insert document_line_items

for (const row of dliRows) {
  const name = `${documentNumber}-${itemKey}-${balanceQty}`;
  lastDliRowOrder = getGapPosition(lastDliRowOrder);
  dliValueSets.push(`(gen_random_uuid(), $${paramIdx}, ...)`);
  dliParams.push(name, 'Invoice', itemKey, balanceQty, rowId, customerId, supplierId, newDocumentId, lastDliRowOrder);
}
await client.query(`INSERT INTO "document_line_items" (...) VALUES ${dliValueSets.join(', ')}`, dliParams);

What: Creates one document_line_items row per unique product. These are the "universal" line items — present for every document type, used for search/overview across documents.

Name format: SSS/390/2025-26-Joint Pin-5 — readable in the UI.

All rows built in a loop first, then inserted as one batch query.

---

Block 19 — Process connect blocks (UPDATE documents)

for (const [targetTable, links] of Object.entries(connectBlocksByTargetTable)) {
  for (const link of links) {
    const value = filledPlaceholders[link.placeholder];
    await client.query(
      `UPDATE "${targetTable}" SET "${link.column}" = $1 WHERE id = $2`,
      [value, newDocumentId]
    );
  }
}

What: Reads all connect mode blocks from dbMapping and applies them as UPDATE queries.

Example: receiver block says { to: { column: "customer_id", tableName: "documents" }, source: { placeholder: "receiver_id" } }. So we run:

UPDATE "documents" SET "customer_id" = '<uuid>' WHERE id = '<newDocumentId>'

This is fully dynamic — any connect block in dbMapping gets applied here, for any document type.

---

Block 20 — Group store blocks by table

const storeBlocksByTable: Record<string, Array<...>> = {};
for (const [blockKey, blockConfig] of Object.entries(blocks)) {
  if (block.mode === 'store') {
    storeBlocksByTable[block.tableName].push({ blockKey, block, hasLayout: block.layout === 'table' });
  }
}

What: Groups all store blocks by which DB table they write to.

Multiple blocks can point to the same table. For example, totals, charges, and invoice_header all write to invoice. By grouping them, we can merge all their columns into one INSERT.

hasLayout: true means the block has layout: "table" — it should insert multiple rows from computedRows, not one row from filledPlaceholders.

---

Block 21 — Process layout store block (batch multiple rows)

const computedRows = [...productRowMap.values()];
const validRows = [];

for (const computedRow of computedRows) {
  const rowData = {};
  for (const columnConfig of block.columns || []) {
    let value = computedRow[placeholder];
    // date conversion if needed
    rowData[columnName] = value;
  }
  // inject invoice_id from insertedIds
  if (tableBlock?.rules?.parentFk) {
    rowData[column] = insertedIds[sourceTable] ?? null;
  }
  // inject product_id from the row itself
  if (tableBlock?.rules?.rowIdColumn) {
    rowData[tableBlock.rules.rowIdColumn] = computedRow[tableBlock.rules.rowIdColumn];
  }
  lastRowOrder = getGapPosition(lastRowOrder);
  validRows.push({ rowData, rowOrder: lastRowOrder });
}

// Then batch insert all validRows in one query
await client.query(batchQuery, allParams);

What: This is the layout path — handles invoice_line_items. For each unique product row:

1. Reads column values from computedRow using the dbMapping column config (placeholder → DB column name)
2. Converts dates if needed
3. Injects invoice_id — this comes from insertedIds['invoice'] which was saved when the invoice row was inserted in Block 22 below. Order matters — invoice must be processed before invoice_line_items.
4. Injects product_id per row from the row itself

All rows built first → single batch INSERT. 40 rows = 1 query.

---

Block 22 — Process non-layout store block (single row)

const allColumns = {};
for (const { block } of tableBlocks) {
  for (const columnConfig of block.columns || []) {
    const value = filledPlaceholders[placeholder];
    if (value !== undefined) allColumns[columnName] = value;
  }
}

// Filter to only columns that exist in orgSchema
const validColumns = {};
for (const [columnName, value] of Object.entries(allColumns)) {
  if (orgSchema.tables[tableName]?.fields[columnName]) {
    // convert date if needed
    validColumns[columnName] = value;
  }
}

// Inject customer_id dynamically
if (customerId) {
  await ensureFkRelation(ctx, orgTemplate, 'customers', tableName, 'customer_id');
  validColumns['customer_id'] = customerId;
}

const result = await client.query(insertQuery, insertParams);
const insertedId = result.rows[0]?.id;
if (insertedId) insertedIds[tableName] = insertedId;

What: This is the single-row path — handles tables like invoice.

1. Merges columns from all store blocks pointing to this table (e.g. totals + charges + invoice_header → one invoice row)
2. Filters to only columns registered in orgSchema — safety check so we don't try to insert into non-existent columns
3. Converts dates
4. Injects customer_id dynamically using ensureFkRelation — creates the column + relation if they don't exist yet
5. Inserts one row, saves the new ID in insertedIds[tableName]

insertedIds['invoice'] = newInvoiceId is then available for the layout block above to inject as invoice_id.

---

Block 23 — Invalidate Redis + COMMIT

await redis.del(ctx, REDIS_KEY.ORG.DEFAULT(org.name));
await client.query('COMMIT');

What:

• redis.del — clears the org's cached schema from Redis so the frontend picks up any new columns/relations on the next page load. Without this, the user would see stale data until the cache expires.
• COMMIT — finalizes all writes. If this line is reached, everything succeeded.

---

Block 24 — ROLLBACK on error

} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

What:

• ROLLBACK — if anything threw an error between BEGIN and COMMIT, this undoes all writes. Nothing partial is ever saved.
• client.release() — always runs (even on error) and returns the connection back to the pool. Without this, the connection would be "leaked" and the pool would run out of connections over time.

---

Block 25 — Mark form as synced

await prisma.forms.update({
  where: { id: input.formId },
  data: { syncedAt: new Date() },
});

What: Sets syncedAt timestamp on the form after a successful commit. This runs outside the transaction — it writes to the app DB (Prisma), not the org's dynamic DB.

Once set, the "Add to Database" button in the UI is disabled and the form shows a "Synced" badge.