Operational tables feeding the warehouse

Enumerated routing config — escalation triggers, review paths, issuance routes, requote reasons. From 06_Approval_Rules_and_Routing_Map.md.

Client contacts on an opportunity — named natural persons at the buying organisation (name/email/phone are PII).

One row per device under test (autoinjector, PFS, cartridge, inhaler, on-body injector, …). nickname is the join key across tests + pricing.

Derived similarity edges — past jobs a new opportunity resembles (drives the HistoricalContext surface).

One row per RFQ opportunity / Smithers job reference. Carries the client entity, region, study type, key dates, and the Smithers staff leads (PII). client_name + budget are confidential-commercial.

One cost line per device per proposal. rate + amount are confidential-commercial (surfaced only via the BI-only pricing mart).

One row per issued/historical proposal. Carries total_rom + contingency (confidential-commercial), lifecycle status, entity, review path, cycle time, and the Smithers staff leads (PII).

Synthetic per-test rate card (docs/smithers/rate-card-spec.md) — the reference price list an external RFQ-pricing app reads via query_records('mdt_rate_card') to cost a set of requested tests. Keyed on the canonical test names from mdt_test_methods, with raw-name aliases so any test-plan name resolves. Rates are GBP integer pence (unit_rate_minor). Confidential-commercial (no PII).

Incoming RFP/RFQ intake pipeline (MVD Category 02) — one row per incoming Request for Proposal awaiting a quote. Real RFPs (from the package RFQ artifacts, linked to their opportunity) + synthetic net-new prospects. Carries the intake-owning BDM (PII); client_name + budget are confidential-commercial.

Named Smithers subject-matter experts by site/role/domain (PII) — the peer-review pool + escalation ladder. From 07_SME_Directory.md.

ISO/ASTM/USP standards catalog (ISO 11608-1, ASTM D6653-13, ISO 7864 …), each linked to its full-text PDF blob in the mdt-standards corpus.

One row per test per device — test name, standards cited, replicates, acceptance criteria, sequence position.

One row per customer × product review, dropped as CSV into `gs://${GCS_BUCKET_NAME}/${GCS_PATH_PREFIX}` (defaults: `reviews/`). Generated by docker/seed/generate_reviews.py so customer_id and product_id line up with the seeded postgres-source rows.

Procurement accounts — the buying organisations RFX opportunities come from. One row per account, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_accounts.csv`.

The approval-workflow rules that gate proposals on discount, value, margin, and legal terms — one row per rule, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_approval_rules.csv`.

Buyer-side contacts at each procurement account, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_contacts.csv`. Carries identity PII (name, email, phone) for natural persons.

RFX opportunities — the deals we respond to. One row per RFP / RFQ / RFI, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_opportunities.csv`.

The role-based access-control matrix for the procurement domain — one row per (role × resource × action), dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_permissions.csv`.

Region- and segment-adjusted pricing entries per SKU — one row per (sku × region × segment), dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_price_book.csv`.

The catalog of products and services we quote in proposals — one row per SKU, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_products_services.csv`.

Proposals we submitted in response to opportunities — one row per proposal version, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_proposals.csv`.

The directory of subject-matter experts who own opportunities and author proposals — one row per SME, dropped as CSV into `gs://${GCS_BUCKET_NAME}/rfx/rfx_sme_directory.csv`. Carries identity PII (name, email) for employees.

The approval-workflow rules that gate operations on write-off value, hazmat handling, credit notes, and SLA waivers — one row per rule, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_approval_rules.csv`. Demonstration content only — no PII.

The brand tenants whose goods the 3PL stores — one row per client brand, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_clients.csv`. `client_id` is THE multi-tenant isolation dimension. Carries client-contact PII.

The B2C delivery recipients — the primary GDPR data subject for the DSR/erasure demo, reachable from `whse_orders.consignee_id`. One row per consignee, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_consignees.csv`. Consumer PII.

The client×site engagement carrying the SLA targets the whole operational-analytics story measures against — one row per contract, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_contracts.csv`. Every row carries a deterministic `source_uri` into `client-sla-contracts`. No PII on the structured row (the SLA contract document is PII-by-default at corpus level).

Live stock positions at SKU×site×lot grain — one row per lot, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_inventory.csv`. The spine for inventory turnover, days-of-cover, and near-expiry. Carries a denormalized `client_id` for the row policy. No PII.

The line grain behind pick accuracy and units picked — one row per order line, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_order_lines.csv`. The largest table by row count. No PII.

The outbound fulfillment spine — the largest table and the engine of OTIF/on-time/in-full. One row per order, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_orders.csv`. Carries `client_id` (the isolation key the tenant-isolation gate reads). PII indirect: `consignee_id` FK points at PII; the order row holds no direct PII.

The role-based access-control matrix for the warehousing domain — one row per (role × resource × action), dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_permissions.csv`. Demonstration content only — the enforced boundary is the persona grants + row/column policies (§5), not these rows. No PII.

The client-owned SKU catalog — one row per SKU, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_products.csv`. Drives temperature-class and hazard-class distributions. Hazardous SKUs (`hazard_class != 'none'`) carry a `source_uri` into `safety-data-sheets`. No PII.

Returns disposition and cycle time — one row per return, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_returns.csv`. Carries a denormalized `client_id` for the row policy. Feeds metric_returns_rate. No PII.

The physical warehouse network across countries/regions — one row per site, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_sites.csv`. No client key: a multi-user site stores many clients' goods. No PII.

The warehouse workforce — one row per staff member, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_staff.csv`. `units_per_labor_hr` feeds site productivity in mart_site_performance. Employee PII.

Cold-chain excursion records against chilled/frozen lots — one row per excursion, dropped as CSV into `gs://${GCS_BUCKET_NAME}/logistics/whse_temperature_events.csv`. Staging-only (no mart/metric/row-detail surface in P0/P1, INC-16). No PII, but a retention anchor: lawful_basis legal_obligation, GDP ~5yr.

Customer shipping/billing addresses with a validity window.

Support agents — humans handling tickets.

Append-only consent ledger — one row per (customer × purpose) state change. Drives lawful-basis enforcement: queries that touch a customer must check `mart_consent_current` for an active grant on the relevant purpose. Withdrawals create a new row with event_type='withdrawn'.

One row per customer in the operational DB.

Customer-facing web events (page views, cart, checkout). Not currently consumed by staging models — kept around for future engagement-funnel marts.

Outbound campaign catalog. Optional FK to a promotion.

Per-customer touchpoint with engagement timestamps. Grain (campaign, customer) is UNIQUE on the source. Sends are gated upstream on `consent_events.purpose='marketing'` — mid-window withdrawals are NOT reflected here.

Line items per order. Joins to `orders.order_id` and `products.product_id`.

Orders ↔ promotions junction with the captured `discount_amount`. UNIQUE (order_id, promotion_id).

One row per customer order.

Tokenised payment instruments per customer. Raw PAN never stored.

One row per payment transaction. Auth → capture → optional refund/void.

Daily inventory positions per (product, warehouse). UNIQUE on the source.

Product catalog.

Discount catalog. Standalone; joined to orders via `order_promotions`.

One row per product return / RMA. Keys to orders, order_items, products, and customers — the operational record of what was sent back, why, and how much was refunded. Feeds return-rate and refund-leakage analytics by product category.

Order fulfillment per shipment. One order may have multiple.

Recurring plans per (customer, product). `monthly_amount` already normalised on the source.

One row per customer support ticket. Joins to customers and agents.

Event log per ticket — created/assigned/comment/status_change/csat_submitted/closed.

Bridge applying AP payments to the vendor bills they settle.

AP payments (cash out). Statutory financial record keyed to a vendor.

Polymorphic approval steps over finance objects. `object_id` is a soft FK keyed by object_type.

Bridge applying AR receipts to the customer invoices they settle.

AR receipts (cash in). Statutory record keyed to the ecommerce `customer` subject.

Append-only audit log over finance objects. old_value/new_value are free-text PII (retain_aggregate).

Bank-account dimension. Each maps to a GL cash account and optionally a legal entity.

Bank-statement transactions. `counterparty_name` is incidental financial PII.

Vendor-bill line items (AP). `description` is incidental free-text PII.

Budget amounts per (budget, gl_account, cost_center, period).

Budget headers (FP&A). Statutory-adjacent planning record kept 7 years.

Chat-channel metadata (Slack/Teams). `topic` is free-text PII. Comms — legitimate_interest + 365d.

Chat-message metadata + body. `body_text` is free-text PII. Comms — legitimate_interest + 365d.

Polymorphic bridge "show me all comms about entity X". Both sides are soft FKs.

Cost-center org dimension. Self-referencing parent hierarchy; optional owner employee.

Customer invoices (AR). Statutory record keyed to the existing ecommerce `customer` subject.

Per-period depreciation schedule per fixed asset. Source of recurring depreciation JEs.

Email participants (to/cc/bcc). participant_email + participant_display_name are contact PII.

Email-message metadata + inline body. subject/body_text/sender_email/sender_display_name are PII.

Email-thread metadata. `subject` is free-text PII. Comms — legitimate_interest + 365d.

Expense-report line items. `merchant_name` + `description` are behavioral PII.

Expense-report headers. Keyed to the submitting `employee`. `report_title` is behavioral PII.

Finance employee identity table — the `employee` data subject. Carries identity / contact / financial PII.

Finance notes. `title` + `body_text` are free-text PII. Comms — legitimate_interest + 365d.

Fixed-asset register. Statutory financial record.

Forecast amounts per (forecast, gl_account, cost_center, period).

Forecast headers (FP&A). Planning record.

FX rate table — one row per (from, to, date, type). Translates txn currency to functional USD.

Chart of accounts. `is_control_account` flags the AR/AP control accounts subledgers tie to.

GL journal-entry headers. Statutory financial record.

GL journal lines (double-entry). Exactly one of debit/credit is non-zero per line. Statutory record.

Customer-invoice line items (AR). Optionally links to an ecommerce product.

Reporting legal entities (e.g. US-INC, EU-GMBH). Entity dimension on GL / periods / cost centers / planning.

Reconciliation match lines tying bank transactions and/or GL lines to a reconciliation.

Bank reconciliations per (bank_account, period). Statutory financial record.

Recurring-journal templates (accrual / deferral / prepaid / recurring).

Vendor bills (AP). Statutory financial record keyed to a vendor.

Vendor master — the `vendor` data subject. Carries identity / contact / financial / location PII.

One row per fiscal period — period_code (e.g. FY2025-M07), fiscal year/month, start/end dates, and close status (open / closed / locked).

One row per paragraph-shaped chunk derived from the DoclingDocument hierarchy by docker/docling-extractor. Default chunking is ~512 tokens with soft section-boundary flushes.

One row per source document, written by docker/docling-extractor. Carries extracted text + extractor metadata + Presidio entity counts (counts only, never values).