Structured AI Output Makes Integrations Easier

Detailed practical guide

Prepare your workspace and inputs

Save the program as invoice_ingest.py. It includes one valid invoice, one missing field and one invalid numeric value. Run python3 invoice_ingest.py. Replace candidate fixtures with actual model outputs only after the boundary tests pass.

Platform context: Python 3.10+ with sqlite3; a local in-memory database. No AI service is needed to test ingestion.

Scenario to reproduce or investigate

An AI integration returns text that looks like JSON, but a downstream write must reject missing fields, booleans used as amounts and nonfinite numbers, while treating punctuation as a literal supplier name.

1. Define fields and business rules before requesting extraction

   Require supplier, invoice_number and amount, with no additional fields. Keep monetary amounts as decimal strings to avoid a binary floating-point conversion. The example accepts at most two decimal places and a nonnegative finite value. Parsed JSON still needs business-rule checks.

   Command, configuration or input

   {"supplier":"Example Office","invoice_number":"INV-001","amount":"12.50"}

   Expected result · ผลที่ควรเห็น

   A valid extracted record has exactly three fields with a nonempty supplier and invoice number and a two-place amount.

   If it fails · เมื่อไม่ผ่าน

   If your source uses multiple currencies, add a required currency field and a supported-currency rule before storing a value.

2. Run the validator and parameterized insert

   The complete script validates types and amount precision before any insert. It uses a UNIQUE invoice number to make duplicate submissions visible. Supplier punctuation remains a bound value and cannot become an additional SQL statement.

   Save and run this example

   import json
   import sqlite3
   from decimal import Decimal, InvalidOperation
   
   def validate(raw):
       item = json.loads(raw)
       if not isinstance(item, dict) or set(item) != {"supplier", "invoice_number", "amount"}:
           raise ValueError("fields do not match schema")
       for field in ("supplier", "invoice_number"):
           if not isinstance(item[field], str) or not item[field].strip() or len(item[field]) > 100:
               raise ValueError("invalid text field")
       if not isinstance(item["amount"], str) or len(item["amount"]) > 30:
           raise ValueError("amount must be a bounded decimal string")
       try:
           amount = Decimal(item["amount"])
           if not amount.is_finite() or amount < 0 or amount.as_tuple().exponent < -2:
               raise ValueError("invalid amount")
       except InvalidOperation as error:
           raise ValueError("invalid amount") from error
       return item["supplier"], item["invoice_number"], str(amount)
   
   db = sqlite3.connect(":memory:")
   db.execute("CREATE TABLE invoices (supplier TEXT, invoice_number TEXT UNIQUE, amount TEXT)")
   good = json.dumps({"supplier": "Office'; SELECT 1; --", "invoice_number": "INV-001", "amount": "12.50"})
   db.execute("INSERT INTO invoices VALUES (?, ?, ?)", validate(good))
   for bad in [
       '{"supplier":"A","amount":"1.00"}',
       '{"supplier":"A","invoice_number":"INV-002","amount":true}',
       '{"supplier":"A","invoice_number":"INV-003","amount":"NaN"}',
       '{"supplier":"A","invoice_number":"INV-004","amount":"1.001"}',
   ]:
       try:
           values = validate(bad)
       except ValueError:
           continue
       raise AssertionError("invalid invoice accepted: " + repr(values))
   assert db.execute("SELECT COUNT(*) FROM invoices").fetchone()[0] == 1
   assert db.execute("SELECT amount FROM invoices").fetchone()[0] == "12.50"
   print("PASS: 1 literal-value invoice stored; 4 invalid records rejected")
   db.close()
   

   Expected result · ผลที่ควรเห็น

   The output is PASS: 1 literal-value invoice stored; 4 invalid records rejected. The supplier punctuation does not create another operation.

   If it fails · เมื่อไม่ผ่าน

   If sqlite3 is unavailable, install the distribution-supported Python build including SQLite. If validation fails, compare the exact decoded type and decimal precision rather than coercing malformed fields.

3. Connect extraction and handle rejected records

   Keep the raw candidate, source-document ID and validation error in a bounded review queue. Do not retry invalid data indefinitely and do not silently convert missing invoice numbers into invented values. Add source checks for the supplier and amount before financial posting; a schema alone cannot prove that the model read the invoice correctly.

   Command, configuration or input

   Source document → generated JSON → validate → source verification → bound INSERT

   Expected result · ผลที่ควรเห็น

   Rejected candidates cannot reach the write and can be traced to their source document for correction.

   If it fails · เมื่อไม่ผ่าน

   If a duplicate invoice number is legitimate in your business, define its correct composite uniqueness rule before replacing the UNIQUE constraint.

Recovery and cleanup

The sample database is in memory and closes without persistent changes. For a real ingestion change, keep the previous schema and importer deployable and test migration rollback with a copied database.

Apply to actual work and known limits

Port the parameter binding to the actual database driver and add bounded payload size, authentication and source-document reconciliation. Store monetary values using the database type and rounding policy defined by your finance system.

The included self-contained Python fixture was executed successfully in this Linux environment. External products and production infrastructure were not exercised.

PASS: 1 literal-value invoice stored; 4 invalid records rejected