Build an Invoice Extractor with Child Tables and Foreign Keys

An invoice has one header and many line items. Flatten it into one row and you either repeat the header on every line or pack the lines into a JSON string your database cannot filter. PdfParse solves this with child tables: one parent row per document, plus one child row per repeating item, each linked back to its parent by a generated foreign key.

This tutorial builds that schema for a single synthetic invoice, runs an extraction, and checks that every line item points at the right invoice. The dashboard path needs no code. The API path is marked Advanced and uses the public pdfparse-sdk package.

The invoice used in this tutorial

Download the synthetic invoice PDF. The same file ships inside pdfparse-sdk as examples/invoice.pdf. It contains:

Decide what goes in the parent and the child table

Put a field on the parent table when it appears once per invoice. Create a child table only for records that repeat.

This table is the conceptual model, not a request you can send. The exact API payload appears in Create the same tables with the API.

Three rules follow from how PdfParse creates tables:

• Do not add an id or parent-reference column yourself. PdfParse adds an auto-incrementing id to both tables, and adds internal_fk_invoices_id to the child table. The child-table editor shows it as invoices_id; in SQL, API rows, and exports the column is named internal_fk_invoices_id.
• Keep one-per-invoice values on the parent. The bill-to customer appears once, so it is a parent column. A customer child table would always hold exactly one row and add a join without adding information.
• Make extracted columns nullable unless every document is guaranteed to contain the value. A non-nullable column rejects a row when the value is missing from the PDF.

Create the tables in the dashboard

1. Open your project, select Tables, then + Create table.
2. Name the table invoices.
3. Add each parent column from the table above. For each one, enter a Name, choose a Type, write a Prompt, and tick Nullable. Use TEXT for invoice_number, supplier, bill_to, and currency; DATE for invoice_date; and DECIMAL for subtotal, tax, and total.
4. Select Add array column and keep Child Table selected.
5. Name the child table invoice_items.
6. Add description (TEXT), then quantity, unit_price, and line_total (all DECIMAL), each with a prompt and nullable. Select Add to table.
7. Select Create Table.

Previous slideNext slide

Expected result: invoices and invoice_items both appear in the project's table list. Open invoice_items: alongside your four columns it has an id and the parent-reference column.

If creation fails because a table with that name already exists, rename the new pair (for example invoices_v2 and invoice_items_v2) or use a fresh project. Child table names must be unique within the project, too.

Extract the sample invoice

1. Open the invoices table and select Upload PDFs.
2. Add invoice.pdf, wait until it shows as uploaded, then select Confirm.
3. Open Files. Uploads go through the project's routing rules, and this project has none yet, so invoice.pdf shows as Needs review.
4. Select Review, choose invoices as the Destination table, then select Route and start extraction.
5. Wait for the file's status to change to Completed.

Route the PDF to the parent table. PdfParse extracts the parent row and its child rows from the same document in one pass. Do not choose invoice_items, even though it appears in the destination list. To skip the review step for future invoices, add a routing rule for invoices.

Expected result: invoices has one new row and invoice_items has two:

These are the values printed on the PDF, which is what a correct extraction should return. They are not a recorded API response. Real rows also include generated IDs, the source document_record_id, and timestamps.

Previous slideNext slide

If the document fails, it appears under Human review with the error. Fix the cause, usually a vague prompt or a non-nullable column, before you retry.

Check that each line item points at its invoice

Open Query in the project sidebar, paste this statement, and select Run:

SELECT
  i.invoice_number,
  it.description,
  it.quantity,
  it.unit_price,
  it.line_total
FROM invoice_items AS it
JOIN invoices AS i ON i.id = it.internal_fk_invoices_id
ORDER BY i.invoice_number, it.id;

Expected result: two rows, both with invoice_number HOS-2026-1042.

Then confirm that no line item lost its parent and that the lines add up to the subtotal:

SELECT
  i.invoice_number,
  i.subtotal,
  ROUND(SUM(it.line_total), 2) AS sum_of_lines,
  (SELECT COUNT(*)
     FROM invoice_items AS orphan
     LEFT JOIN invoices AS p ON p.id = orphan.internal_fk_invoices_id
    WHERE p.id IS NULL) AS orphaned_items
FROM invoices AS i
JOIN invoice_items AS it ON it.internal_fk_invoices_id = i.id
GROUP BY i.id;

Expected result: subtotal and sum_of_lines are both 65, and orphaned_items is 0. If the sums differ, look for a line that wraps onto two rows in the PDF, or a discount or freight line. Tighten the invoice_items column prompts rather than editing the totals.

The parent link is a column populated by PdfParse during extraction, not a SQLite REFERENCES constraint. Run the orphan check above after importing data into another system.

Create the same tables with the API

This section requires Node.js 20 or later, a project API key from the project's API Keys page, and available processing quota. Keep the key on your server.

POST /v1/tables creates the parent and its child tables in one request. This is the complete request body, matching the dashboard schema above:

{
  "name": "invoices",
  "columns": [
    { "name": "invoice_number", "type": "text", "nullable": true, "prompt": "Invoice number exactly as printed." },
    { "name": "supplier", "type": "text", "nullable": true, "prompt": "Company that issued the invoice." },
    { "name": "bill_to", "type": "text", "nullable": true, "prompt": "Customer name in the Bill to section." },
    { "name": "invoice_date", "type": "date", "nullable": true, "prompt": "Issue date in YYYY-MM-DD format." },
    { "name": "currency", "type": "text", "nullable": true, "prompt": "ISO currency code when explicit; otherwise null." },
    { "name": "subtotal", "type": "number", "nullable": true, "prompt": "Subtotal before tax, as a decimal amount." },
    { "name": "tax", "type": "number", "nullable": true, "prompt": "Total tax amount, not the tax rate." },
    { "name": "total", "type": "number", "nullable": true, "prompt": "Invoice total including tax, not the balance due." }
  ],
  "childTables": [
    {
      "name": "invoice_items",
      "columns": [
        { "name": "description", "type": "text", "nullable": true, "prompt": "Item or service description." },
        { "name": "quantity", "type": "number", "nullable": true, "prompt": "Quantity billed on this line." },
        { "name": "unit_price", "type": "number", "nullable": true, "prompt": "Price per unit before tax." },
        { "name": "line_total", "type": "number", "nullable": true, "prompt": "Printed amount for this line before tax." }
      ]
    }
  ]
}

The request shape is strict about a few names:

• Tables use name and columns; child tables go in a top-level childTables array. There is no fields, key, parent, or repeating property.
• type is one of text, number, date, or boolean. string is rejected. The API's number corresponds to the dashboard's DECIMAL.
• Leave out id and internal_fk_invoices_id. PdfParse adds both.

The response is 201 with the parent's { "id", "slug" }. A name that already exists in the project returns 409 conflict.

Run the extraction from Node.js

Install the SDK, copy the sample invoice, and save your key in .env:

npm install pdfparse-sdk
cp node_modules/pdfparse-sdk/examples/invoice.pdf invoice.pdf
echo "PDFPARSE_API_KEY=replace_with_your_project_api_key" > .env

Save the request body above as invoice-schema.json, then save this script as extract-invoice-items.mjs. It reuses the invoices tables if they already exist, uploads the PDF to the parent table, waits for the job, and reads the linked rows with SQL.

import { readFile } from "node:fs/promises";
import { basename } from "node:path";
import { ApiError, Client } from "pdfparse-sdk";

const input = process.argv[2] ?? "invoice.pdf";
const apiKey = process.env.PDFPARSE_API_KEY;
if (!apiKey) throw new Error("Set PDFPARSE_API_KEY in .env");
const schema = JSON.parse(await readFile("invoice-schema.json", "utf8"));
const client = new Client(apiKey);

try {
  const existing = await client.tables.list();
  const parent =
    existing.find((table) => table.name === schema.name) ??
    (await client.tables.create(schema));

  const upload = await client.uploads.upload(await readFile(input), {
    filename: basename(input),
    tableSlug: parent.slug,
  });
  const created = await client.jobs.create({
    tableSlug: parent.slug,
    documentKeys: [upload.fileId],
  });
  console.log(`Job ${created.id} started for file ${upload.fileId}`);

  const { job } = await client.jobs.waitForCompletion(created.id, {
    interval: 2_000,
    timeout: 15 * 60_000,
  });
  if (job.job_status !== "completed") {
    throw new Error(`Job ${job.id} failed; check the document under Human review`);
  }

  const { rows } = await client.sql.query({
    sql: `SELECT i.id AS invoice_id, i.invoice_number, i.subtotal,
                 it.description, it.quantity, it.unit_price, it.line_total
            FROM invoice_items AS it
            JOIN invoices AS i ON i.id = it.internal_fk_invoices_id
           WHERE i.document_record_id = ?
           ORDER BY it.id`,
    params: [upload.fileId],
  });
  console.table(rows);

  const cents = (value) => Math.round(Number(value) * 100);
  const sumOfLines = rows.reduce((sum, row) => sum + cents(row.line_total), 0);
  if (rows.length === 0 || sumOfLines !== cents(rows[0].subtotal)) {
    throw new Error("Line items do not add up to the subtotal; review the PDF");
  }
  console.log(`${rows.length} line items linked to invoice ${rows[0].invoice_number}`);
} catch (error) {
  if (error instanceof ApiError) {
    console.error({ code: error.code, status: error.statusCode, requestId: error.requestId });
  } else {
    console.error(error.message);
  }
  process.exitCode = 1;
}

Run it:

node --env-file=.env extract-invoice-items.mjs invoice.pdf

Expected output: a job ID, a two-row table for Copy paper and Desk lamp that share one invoice_id, and 2 line items linked to invoice HOS-2026-1042.

Each run uploads and extracts the PDF again, which uses quota. If the script stops while waiting, check the job in the dashboard's Jobs page instead of rerunning it. For a resumable version with saved IDs and CSV export, follow the tutorial in the pdfparse-sdk README.

To read child rows without SQL, call client.rows.search with the child table's id from client.tables.list() and filter on { column: "document_record_id", op: "eq", value: fileId }. Parent-row responses do not embed their child rows.

Export the linked tables

• One table: open the table, select Export, and choose Export as CSV or Export as JSON. Export both invoices and invoice_items, and keep id and internal_fk_invoices_id so the files can be joined again.
• The whole project: open Project settings → Data & export and select Export SQLite Database. The export runs in the background, and PdfParse emails you when the file is ready. Run the orphan check above against the downloaded file before loading it elsewhere.

Next step

You now have an invoice schema whose line items are real rows, linked to their invoice by internal_fk_invoices_id, and two queries that prove the link and the arithmetic. Run five to ten invoices from different suppliers through the same tables and repeat the reconciliation query before automating the workflow.

For when a child table is the wrong choice, read JSON arrays vs child tables. For the same pattern applied to statements and transactions, see Extract Transactions From a PDF Bank Statement. Questions about a more complex invoice schema can go to support@pdfparse.net.