Skip to content

PostgreSQL

The test-estate safety rule: stand the test estate up only beside a test instance, never a production deployment. Its systems are throwaway test services with published credentials: change-me is every test credential. They are still encrypted and still ask for a login, because FACE refuses a connection that is not (see Connector security). Use a scratch machine or a scratch cluster.

Grade A · dataset: SQL (DDL and rows in the engine’s dialect) · type postgres

What it is

A PostgreSQL database. In the test estate it is Quillmere’s operational system, schema ops: customers and their delivery sites, warehouses (three distribution centres and a retail store), yards and docks; the item master (ops.items: every SKU with its name, category, EAN-13 and the words a camera or OCR reads off it); a year of orders, 2025-10-01 to 2026-09-29 with the 2025 holiday season in it, and the shipments that served them, leg by leg; the fleet, the site and yard equipment with their readings against stated limits, and the maintenance work orders; inventory, reorder policies and replenishment orders; route plans, their stops and their revisions; freight invoices, claims, their evidence and correspondence. The connector reference is SQL warehouses and databases.

Grade

A. Open source. It stands up locally from the download, with the model and its rows, end to end. Any PostgreSQL 13 or later works; the dataset is verified on 16.15.

See the grade table for all 32 types.

Settings and credentials

From the connection catalogue. Settings are ordinary fields on the connection record. Credentials are kept in a sealed bundle and never returned.

Names
Typepostgres
Other spellings that resolvepostgresql
Settingshost, port, database, username, ssl_mode
Credentialspassword

The test connection

Quillmere ops (Postgres)

SettingTest value
hostpg.quillmere.invalid
port5432
databasequillmere_ops
usernameface_reader
ssl_moderequire

Credentials: password. In the test estate, every credential is change-me. Credentials are write-only: they go into the sealed credential bundle and are never returned.

Stand up the test system

The safety rule at the top of this page comes first: a scratch machine or a scratch cluster, never a production or platform one.

  1. Download postgres.zip and unzip it. It holds postgres/01_schema.sql, the rows in postgres/02_data_01.sql to postgres/02_data_16.sql (load them in name order), and postgres/expected.json.

  2. Start a scratch PostgreSQL 13 or later with TLS on, because ssl_mode require needs it. The official image ships a self-signed certificate you can use for a test:

    docker run --rm -e POSTGRES_PASSWORD=change-me -p 5432:5432 postgres:16 \
      -c ssl=on \
      -c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem \
      -c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
  3. Create the database and the reader role, then load the schema and the rows, in that order:

    psql -h localhost -U postgres -c "CREATE DATABASE quillmere_ops"
    psql -h localhost -U postgres -c "CREATE ROLE face_reader LOGIN PASSWORD 'change-me'"
    psql -h localhost -U postgres -d quillmere_ops -f postgres/01_schema.sql $(for f in postgres/02_data_*.sql; do printf -- '-f %s ' "$f"; done)
    psql -h localhost -U postgres -d quillmere_ops -c "GRANT USAGE ON SCHEMA ops TO face_reader; GRANT SELECT ON ALL TABLES IN SCHEMA ops TO face_reader"
  4. Add the connection with the settings above, as Create it in the Console shows. Replace pg.quillmere.invalid with the address of your scratch engine: a .invalid name never resolves.

Create it in the Console

The screens below were taken on a live instance, adding the quickstart’s database as it runs in the data-systems namespace of Runink’s platform cluster. Your host is your scratch engine’s address; every other value is the test connection’s.

  1. Open Settings › Integrations › Systems and select New connection.

  2. Source: under Warehouses & databases, select PostgreSQL, then Next.

    The PostgreSQL card selected in the source catalog

  3. Details: fill in the settings.

    FieldValue
    Connection namea name you will recognise, for example Quillmere ops (PostgreSQL); the Id follows it
    Environmentwhere this source lives, for example dev or staging
    Host / endpointyour engine’s address
    Port5432
    Database namequillmere_ops
    Userface_reader
    Ssl moderequire (encrypt, do not verify the certificate). Use a verifying mode when the engine’s certificate is from a CA your runner trusts. FACE refuses disable, allow, prefer and an unset mode.

    Step 2, Details: host, port, database, user and the encryption in transit

  4. Scroll to Credentials and enter the reader’s Password. It is sealed when you save and never shown again.

    The Credentials section with the write-only Password field

  5. Runner: keep Runink managed, or pick a registered runner that can reach the engine, then Next.

  6. Review: check the values. Credentials reads none entered until you type the password in step 4. Keep Test after saving ticked and select Test & save.

    Step 4, Review: every value before saving

The new connection appears in the list with a credential stored chip and the test’s result. It shows as a source on Ask and in every brief’s source list. To read it, see Ask.

What FACE finds

FACE reads the catalogue: information_schema, with the declared primary and foreign keys, every COMMENT ON table, view and column as the dataset’s and field’s description, and each view’s definition (pg_get_viewdef). Partitions and relations an extension owns (such as pg_stat_statements’ views) are not listed as datasets; the discovery says how many were left out. A test loads this dataset into an empty engine, runs FACE’s discovery, and compares the result with the expected model (TestSynthUniverseDiscoveryMatchesExpected).

DatasetKindDomainPrimary keyRowsFields
ops.customerstablecustomerscustomer_id75
ops.customer_sitestablecustomerssite_id1811
ops.warehousestablewarehouse_opswarehouse_id411
ops.yardstableyardyard_id410
ops.dockstablewarehouse_opsdock_id95
ops.itemstableproduct_catalogsku_id225
ops.orderstableordersorder_id47366
ops.order_linestableordersorder_id, line_no77935
ops.transfer_hubstableshipmentshub_id99
ops.supplierstableinventorysupplier_id87
ops.supplier_itemstableinventorysupplier_id, sku_id267
ops.vehiclestabletelematicsvehicle_id4914
ops.vehicle_shiftstabletelematicsshift_id2705
ops.vehicle_positionstabletelematicsvehicle_id, observed_at17287
ops.site_assetstablewarehouse_opsasset_id5110
ops.asset_channelstablewarehouse_opschannel_id18310
ops.asset_readingstablewarehouse_opschannel_id, read_at121243
ops.maintenance_work_orderstablewarehouse_opswork_order_id39112
ops.shipmentstableshipmentsshipment_id335126
ops.shipment_legstableshipmentsshipment_id, leg_no366318
ops.reefer_readingstablecold_chainshipment_id, leg_no, read_at26326
ops.temperature_excursionstablecold_chainexcursion_id3110
ops.inventory_positionstableinventorysku_id, warehouse_id6411
ops.reorder_policiestableinventorysku_id, warehouse_id5416
ops.replenishment_orderstableinventoryreplenishment_id106614
ops.route_planstableshipmentsroute_plan_id, plan_version17210
ops.route_stopstableshipmentsstop_id75721
ops.route_plan_revisionstableshipmentsrevision_id2014
ops.freight_invoicestablefreight_auditinvoice_id330810
ops.claimstableclaimsclaim_id23512
ops.claim_evidencetableclaimsevidence_id8226
ops.claim_messagestableclaimsmessage_id7127
ops.remediation_orderstableclaimsremediation_id5610
ops.yard_movestableyardmove_id4189
ops.gate_eventstableyardevent_id4088

The tables of schema recovery (read by the connection postgres-recovery, not by postgres-operations) are described in Dangerous goods and luxury assets; their row counts are the generator’s (TestSynthOpsRecoveryTables) and are not restated here.

Every table declares its primary key, and the relations are declared foreign keys: an order to its customer and warehouse, a shipment to its order, origin warehouse, destination site, dock and tractor, a leg and its reefer readings to their shipment, a channel’s readings to the channel, a policy and a replenishment order to the supplier’s item, a stop to its plan version and shipment, a revision to the plan version it produced and to the revision that triggered it, evidence and messages to their claim. expected.json lists every one.

What the rows let FACE show

ScenarioTablesWhat FACE does with them
Routesshipments (origin_warehouse_id, dest_site_id, status, transport_mode, temp_min_c/temp_max_c), customer_sites (lat/lon)the run’s routes phase draws every pending shipment from its warehouse to its customer site, with the tightest temperature band the routed shipments state
Reactive fulfilmentinventory_positions (on_hand, available, lead_time_days), orders + order_linesthe site page computes safety stock and a reorder point per item from the lead time, the daily demand and the cycle-service level your organisation sets in Admin, and shows the items under it after the last demand spike
Fleet planningroute_stops (a dispatch feed’s columns: vehicle_id, stop_sequence, latitude/longitude, demand_kg, vehicle_capacity_kg, planning_speed_kmh, window_start/window_end, temperature_min_c/max_c), vehicles (status, capacity, reefer capability), vehicle_shiftsthe fleet planner’s stops, beside the GPS fixes of the live fleet topic
Recursive re-plansroute_plan_revisions (trigger_kind, triggered_by_revision_id, legs_before/legs_after)the history of re-plans, an excursion’s re-plan triggering a delay re-plan, a reassignment and the receiving plan’s re-plan
Predictive maintenancesite_assets, asset_channels (each limit and its source), asset_readings, maintenance_work_orders30 days of readings to check a projection against what happened; the live readings are on the Kafka equipment topic
Cold chainshipment_legs (band per leg), reefer_readings, temperature_excursionstime out of band per leg
Claimsclaims, claim_evidence, claim_messages (sent, replied, resolved, closed), remediation_ordersfollow a claim from filing to close
Item lookupitems (name, category, aliases, ean), inventory_positionsfind an item by the word a detector or OCR produces (teddy bear, pikachu, husky) or by its EAN-13, then its stock at each warehouse
The 2025 holiday seasonorders + order_lines from 2025-10-01, replenishment_orders, route_plans, route_plan_revisionsa season to forecast the next from: the toy line’s lift from November to Christmas (the teddy bear steady, the Pikachu plush breaking out, the husky plush running out on its 24-day supplier lead time), a smaller lift on household items, the peak-season replenishment orders, and the demand_spike re-plans of Black Friday week and the pre-Christmas peak
The store’s windowwarehouses (the store ST-NAPERVILLE: Naperville, IL, US, with lat/lon), its orders + order_lines (point-of-sale tickets), inventory_positions, replenishment_orders, items, supplier_itemsa retail store to arrange a shop window for: a spring-2025 Easter line received in March and April 2025 and selling down through 2026 (past collection: months of cover, sales now under 0.8 of the four weeks before), the Pikachu and unicorn plush rising through August and September, and tickets that carry both, so a bundle’s evidence is in the store’s own baskets
Dangerous goods and luxury assetsschema recovery: goods (UN numbers, hazard classes, packing groups; serialised watches, handbags, jewellery, wine, art), mode_rules, consignments + legs (road, rail and sea), sensor_readings, incidents, declarations, loss_claims, facilities, reverse_orders, custody_events, disposal_certificates, environmental_report, and the trade side trade_measures, trade_exposures, trade_lanes, trade_providers, trade_suppliers, trade_responses; the goods are also rows of ops.itemswhich hazardous and high-value shipments are at risk right now, what happened to each, the nearest facility whose licence was valid on the day, and the certificates and report rows that close the loop. Read by the connection postgres-recovery as its own role, because face_reader already sees the 40 tables a fetch sweeps in ops. See Dangerous goods and luxury assets

Late deliveries, invoices over the contracted rate and claims filed past the carrier’s window occur at about 15%, 12% and 15%. About one asset in twelve has a service overdue.

Where the sites are

ops.warehouses and ops.yards give every warehouse and yard a postal address and coordinates, so a fetch of this source puts them on the twins Map: FACE writes a Site entity for each row, placed by the coordinates the row states (lat/lon on warehouses, latitude/longitude on yards), with the address and the docks of ops.docks on its page. FACE never geocodes an address: a site with only an address is listed, not placed.

The customer delivery sites (ops.customer_sites), the suppliers and the carriers’ depots stand at real city centroids: the U.S. Census Bureau’s 2024 Gazetteer internal point of an Illinois or Indiana city, with no street address. FACE places a destination at the coordinates the row states.

The distribution centres are authored for this quickstart, not measured. The cities, postal codes and coordinates are real places, a point in an industrial district of each city, so the Map puts each site somewhere true. No street address is a real business’s.

SiteAddressLatitude, longitude
WH-NORTH Quillmere North DC2250 Wexcombe Parkway, Elk Grove Village, IL 60007, US42.0031, -87.9706
YARD-NORTH Quillmere North Yard2290 Wexcombe Parkway, Elk Grove Village, IL 60007, US42.0043, -87.9689
WH-EAST Quillmere East DC4100 Harlowfield Drive, Plainfield, IN 46168, US39.7148, -86.3417
YARD-EAST Quillmere East Yard4150 Harlowfield Drive, Plainfield, IN 46168, US39.716, -86.3399
WH-SOUTH Quillmere South DC7300 Ostley Commerce Road, Edwardsville, IL 62025, US38.7742, -90.0389
YARD-SOUTH Quillmere South Yard7340 Ostley Commerce Road, Edwardsville, IL 62025, US38.7754, -90.0371

The same sites, with the same addresses and coordinates, are in whs (facility) and yms (yards).

expected.json in the download has the full model: every field with its logical type, the source’s native type and nullability.

How it joins the Quillmere universe

This source carries 12 of the universe’s shared keys. The same ID means the same thing in every source: a shipment here is the same shipment in the database, the event bus, the GPS track and the claim PDF.

Universe entityFields hereAlso in
asnshipments.asn_idlogisticsapi, whs
carriershipments.carrier_idd365, document, files, guidewire, mysql, objectstore, redshift, sharepoint, tms, yms
customercustomers.customer_id, orders.customer_idfiles, hubspot, kafka, oms, redshift, salesforce, sap, snowflake
customer_poorders.customer_pofiles, hubspot, kafka, oms, snowflake
devicedocks.camera_id, docks.rfid_reader_idbigquery, cctv, databricks, files, gps, iot, kafka, mqtt, rfid, servicenow
dockdocks.dock_id, shipments.dock_idcctv, excel, files, kafka, logisticsapi, sensorgateway, whs, yms
orderorder_lines.order_id, orders.order_id, shipments.order_iddocument, files, hubspot, kafka, oms, sap, snowflake
shipmentshipments.shipment_idbigquery, d365, databricks, document, excel, files, github, guidewire, iot, kafka, logisticsapi, mqtt, mysql, objectstore, redshift, salesforce, sap, servicenow, sharepoint, tms, whs, yms
skuorder_lines.sku_idims, inventory, logisticsapi, oms, sap, snowflake
trailershipments.trailer_idcctv, databricks, document, excel, iot, kafka, mqtt, servicenow, tms, yms
warehousedocks.warehouse_id, orders.warehouse_id, warehouses.warehouse_id, yards.warehouse_idinventory, oms, redshift, tms, whs, yms
yardwarehouses.yard_id, yards.yard_idyms

Evidence it holds for the planted anomalies:

  • ANOM-4, A duplicate customer PO. postgres/02_data_01.sql in postgres.zip, look for 'PO-0002-4194' (two order rows).

See all connectors at once for the walkthrough across every source.

Download

This is one of 25 zips: the 7 streaming types ship as replay recordings instead.

What is in the zip
  • postgres/01_schema.sql
  • postgres/02_data_01.sql to postgres/02_data_16.sql
  • postgres/expected.json

The whole universe is universe.json. manifest.json lists every download with its size and SHA-256, and SHA256SUMS is the same list for sha256sum -c.