A lab acceptance is a validity range per rule, not a flag on the vendor

#database #backend #ecommerce #compliance

Most systems that store test records model laboratory status as a boolean on a vendor row. is_accredited = true. That column is wrong in a specific way: 16 CFR part 1112 grants acceptance per rule or test method, the scope can be extended later, and it can be suspended for one slice of the scope while the rest stands. A boolean cannot represent any of that.

The correct shape is a range table keyed by rule, with the test date checked against it.

The schema

CREATE TABLE cab (
  id            BIGSERIAL PRIMARY KEY,
  legal_name    TEXT NOT NULL,
  address       TEXT NOT NULL,
  -- independent | firewalled | government, per 1112.11
  cab_kind      TEXT NOT NULL CHECK (cab_kind IN ('independent','firewalled','government'))
);

CREATE TABLE cab_acceptance (
  id            BIGSERIAL PRIMARY KEY,
  cab_id        BIGINT NOT NULL REFERENCES cab(id),
  cpsc_rule_id  TEXT NOT NULL,           -- the rule, not a product category
  accepted_from DATE NOT NULL,
  accepted_to   DATE,                    -- NULL means currently open
  status        TEXT NOT NULL CHECK (status IN ('active','suspended','withdrawn','discontinued')),
  source_url    TEXT NOT NULL,           -- where the listing said so
  observed_on   DATE NOT NULL            -- the day this row was read, not asserted
);

CREATE UNIQUE INDEX acceptance_one_open_window_per_rule
  ON cab_acceptance (cab_id, cpsc_rule_id, COALESCE(accepted_to, DATE '9999-12-31'));

The observed_on column is the one people skip. Section 1112.19 has the Commission maintain a listing and update it as scopes change, so every row here is an observation with a date attached, not a fact about the world. A pipeline that overwrites observed_on with now() on every write has thrown away the only field that lets you reconstruct what was known on the test date.

The test record

CREATE TABLE test_record (
  id            BIGSERIAL PRIMARY KEY,
  product_id    BIGINT NOT NULL,
  cpsc_rule_id  TEXT NOT NULL,
  tested_on     DATE NOT NULL,
  prime_cab_id  BIGINT NOT NULL REFERENCES cab(id),
  -- 1112.25: subcontracted work must be identifiable in the prime report
  performed_by  JSONB NOT NULL DEFAULT '[]',   -- [{cab_id, tests: [...]}]
  report_uri    TEXT NOT NULL
);

The gate query

A test result counts only if the body held acceptance for that rule on that date. That is a join with a range predicate, and it has to run before the certificate row is considered complete.

SELECT t.id
  FROM test_record t
 WHERE NOT EXISTS (
         SELECT 1
           FROM cab_acceptance a
          WHERE a.cab_id = t.prime_cab_id
            AND a.cpsc_rule_id = t.cpsc_rule_id
            AND a.status = 'active'
            AND a.accepted_from <= t.tested_on
            AND (a.accepted_to IS NULL OR a.accepted_to >= t.tested_on)
       );

Zero rows is the pass condition. A non-empty result means the file cites a test whose scope line does not cover the rule, or covers it on a date outside the window. Both look identical in a system that stores is_accredited.

The same predicate is what catches a suspension that lands later. Refresh the acceptance windows on a schedule, rerun the query against existing certificates, and a report that was valid when issued can surface as invalid now. That is not a bug in the file. It is the file being current.

Subcontracting

performed_by exists because 1112.25 requires the prime contractor's report to identify which tests a CPSC-accepted body acting as a subcontractor performed. A single PDF from a trading company that consolidated three laboratories does not satisfy that as data. Model it as an array of (cab_id, tests) and let the same range predicate run over each entry rather than only over the name on the cover page.

type Attribution = { cabId: number; tests: string[] };

function unresolvedParts(t: TestRecord, windows: AcceptanceWindow[]): string[] {
  const covered = new Set<number>();
  for (const w of windows) {
    if (w.status === 'active' && w.acceptedFrom <= t.testedOn &&
        (w.acceptedTo === null || w.acceptedTo >= t.testedOn)) covered.add(w.cabId);
  }
  return t.performedBy
    .flatMap((a: Attribution) => a.tests.map((name) => ({ cabId: a.cabId, name })))
    .filter((row) => !covered.has(row.cabId))
    .map((row) => `${row.name}@${row.cabId}`);
}

unresolvedParts().length === 0 is the assertion worth putting in CI for the ingestion job, because the failure mode is silent. A missing attribution reads as a complete report.

Retention, and what it is not

Section 1112.25 makes the laboratory hold its own test reports and technical records for section 14 tests for at least five years from the date the test was conducted. That is the lab's duty on its own copies. It is not a license to delete your side after five years, and it is not a statement that a report older than five years is unusable. The date that starts the clock is the test date rather than the shipment date, so a slow-moving SKU can carry a report whose retention window closed while the goods were still on the shelf.

FulfillNexa by SBT (fulfillnexa.com) runs the inbound scan at three China facilities, floor area 13,000 square meters in Suzhou, 8,000 in Dongguan and 3,000 in Shenzhen, where one carton resolves to one product identifier before it resolves to a test file. That is the same join in the other direction. Whatever the storage layer looks like, the question at the dock is identical to the question in the query above. Which body ran this test, against which rule, on which date.

DEV Community