• 9 min read
  • 1 code sample

Point-in-time HTS resolution with recursive CTEs

Resolving a 10-digit HTS code to its full classification lineage — chapter, then heading, then subheading, then statistical suffix — and the duty rate that was legally in force on the entry date is a graph walk over an effective-dated table, and the cleanest way to do it in PostgreSQL is a single recursive common table expression. This page builds that CTE against a bitemporal schedule table: it climbs the parent chain from the filed code up to its two-digit chapter, selects the one rate row whose validity window contains the as-of date at each level, guards the recursion against cycles and runaway depth, and hands the whole chain back to Python through an asyncpg fetch. The result is one round trip that returns an ordered, audit-ready ancestry with the correct historical rate — never today’s rate applied to a back-dated entry. It reads the same bitemporal table the loaders in asyncpg vs psycopg3 for HTS schedule bulk upserts populate, and it sits under the same design notes in HTS Schedule Database Design.

Prerequisites

This solution assumes a specific table shape and toolchain. Confirm each before applying it:

  • PostgreSQL 12+ for WITH RECURSIVE plus the SEARCH and CYCLE clauses used as depth and loop guards. The query is standard SQL and does not depend on extensions.
  • A bitemporal schedule table where every node stores its own digit-only hts_code, a parent_code (the code with the next-shorter significant length, or NULL at the chapter root), and a validity window (effective_date, expiry_date) plus general_rate. This is the contract produced by Tariff Update Ingestion Pipelines; if your table lacks parent_code, derive it once by prefix during load rather than at query time.
  • Digit-only codes end to end. The walk joins parent_code = hts_code, so a mixed 8517.62.0000 / 8517620000 corpus breaks the chain silently. Normalize dots away on write, matching the resolver in Handling missing HTS codes in ETL pipelines.
  • Python 3.10+ with asyncpg>=0.29. The fetch uses Decimal for general_rate — never float, because ISO 4217 minor-unit rounding is not representable in binary floating point — and returns a frozen dataclass per level.
  • Structured logging configured (stdlib logging with the %(asctime)s | %(levelname)s | %(name)s | %(message)s format) so every resolution, and every depth-guard trip, is greppable for CBP audit.

Implementation

The recursive CTE has two members. The anchor selects the filed code’s row that is effective on the as-of date. The recursive member joins each node to its parent_code, again constrained to the row effective on that same date, incrementing a depth counter. The CYCLE clause aborts on any repeated code, and an explicit depth < 6 predicate caps the walk at the four significant levels plus slack. asyncpg runs it in one round trip.

import asyncio
import logging
from dataclasses import dataclass
from datetime import date
from decimal import Decimal

import asyncpg

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
)
logger = logging.getLogger("hts_pit_resolver")

# One round trip: climb parent_code from the filed code to its chapter root,
# picking the row whose [effective_date, expiry_date) window contains $2 at
# every level. CYCLE guards against a malformed parent loop; depth < 6 caps the
# walk at chapter->heading->subheading->stat-suffix plus one level of slack.
PIT_LINEAGE_SQL = """
WITH RECURSIVE lineage AS (
    -- anchor: the filed code, as of the entry date
    SELECT s.hts_code,
           s.parent_code,
           s.description,
           s.general_rate,
           s.effective_date,
           s.expiry_date,
           1 AS depth
      FROM hts_schedule s
     WHERE s.hts_code = $1
       AND s.effective_date <= $2
       AND (s.expiry_date IS NULL OR s.expiry_date > $2)

    UNION ALL

    -- recursive member: step up to the parent, same as-of window
    SELECT p.hts_code,
           p.parent_code,
           p.description,
           p.general_rate,
           p.effective_date,
           p.expiry_date,
           l.depth + 1
      FROM hts_schedule p
      JOIN lineage l ON p.hts_code = l.parent_code
     WHERE l.depth < 6
       AND p.effective_date <= $2
       AND (p.expiry_date IS NULL OR p.expiry_date > $2)
)
CYCLE hts_code SET is_cycle USING path
SELECT hts_code, description, general_rate, effective_date, expiry_date, depth
  FROM lineage
 WHERE NOT is_cycle
 ORDER BY depth DESC;   -- chapter first, filed code last
"""


@dataclass(frozen=True)
class LineageNode:
    hts_code: str
    level: str                 # "chapter" | "heading" | "subheading" | "statistical"
    description: str
    general_rate: Decimal      # Decimal, never float
    effective_date: date
    expiry_date: date | None


_LEVEL_BY_LEN = {2: "chapter", 4: "heading", 6: "subheading",
                 8: "statistical", 10: "statistical"}


async def resolve_point_in_time(
    pool: asyncpg.Pool, hts_code: str, as_of: date
) -> list[LineageNode]:
    """Return the chapter->...->filed-code chain effective on `as_of`."""
    code = hts_code.strip().replace(".", "")
    if not (code.isdigit() and len(code) in (6, 8, 10)):
        raise ValueError(f"Non-filable HTS code: {hts_code!r}")

    rows = await pool.fetch(PIT_LINEAGE_SQL, code, as_of)
    if not rows:
        # Either the code does not exist or nothing was effective on as_of.
        logger.warning("No lineage for %s as of %s", code, as_of)
        raise LookupError(f"{code} has no effective row on {as_of.isoformat()}")

    chain = [
        LineageNode(
            hts_code=r["hts_code"],
            level=_LEVEL_BY_LEN.get(len(r["hts_code"]), "unknown"),
            description=r["description"],
            general_rate=r["general_rate"],   # asyncpg maps numeric -> Decimal
            effective_date=r["effective_date"],
            expiry_date=r["expiry_date"],
        )
        for r in rows
    ]

    # A complete filable chain must terminate at a 2-digit chapter root.
    if chain[0].level != "chapter":
        logger.warning(
            "Broken parent chain for %s: top node %s is %s, not a chapter.",
            code, chain[0].hts_code, chain[0].level,
        )
        raise LookupError(f"Incomplete lineage for {code} as of {as_of}")

    leaf = chain[-1]
    logger.info(
        "Resolved %s as of %s -> rate %s (chain depth %d)",
        code, as_of, leaf.general_rate, len(chain),
    )
    return chain


async def main() -> None:
    pool = await asyncpg.create_pool("postgresql://localhost/customs")
    try:
        chain = await resolve_point_in_time(pool, "8517620000", date(2023, 6, 1))
        for node in chain:
            print(f"{node.level:>11}  {node.hts_code:<10}  {node.general_rate}")
    finally:
        await pool.close()


if __name__ == "__main__":
    asyncio.run(main())

The load-bearing predicate appears three times and must never be dropped: effective_date <= $2 AND (expiry_date IS NULL OR expiry_date > $2). Applying it in the anchor, the recursive member, and (implicitly) in how the parent row is chosen is what makes this point-in-time rather than point-in-now. The half-open window [effective_date, expiry_date) guarantees exactly one row is selected per level even on a boundary day, so the walk is deterministic. That determinism is why the same batch loaded idempotently by the bulk upsert benchmarks always resolves to the same historical rate.

Recursive climb from the filed HTS code to its chapter root A four-level tree drawn bottom to top. At the bottom, depth 1, the anchor node is the filed statistical-suffix code 8517620000 with its as-of rate. An upward arrow labeled join parent_code equals hts_code leads to depth 2, the subheading 851762. Another upward arrow leads to depth 3, the heading 8517. A final upward arrow leads to depth 4, the chapter root 85, whose parent_code is null, terminating the recursion. On the right, two guard boxes: a depth guard reading depth less than 6 stops runaway recursion, and a CYCLE clause on hts_code stops any repeated-code loop. Every level applies the same as-of window predicate: effective_date on or before the entry date and expiry_date after it or null. The output is ordered chapter first, filed code last. chapter · 85 depth 4 · parent_code = NULL · stop heading · 8517 depth 3 subheading · 851762 depth 2 join parent_code = hts_code statistical · 8517620000 depth 1 · anchor · filed code + as-of rate every level: effective_date ≤ as_of AND (expiry_date > as_of OR NULL) depth guard depth < 6 → no runaway CYCLE hts_code breaks parent loops ORDER BY depth DESC chapter first · leaf last
The anchor row is the filed code; the recursive member climbs parent_code to the chapter root under one shared as-of window. A depth < 6 predicate and the CYCLE clause bound the walk, and ORDER BY depth DESC returns the chain chapter-first.

Verification steps

Run these checks against a representative schedule before trusting the resolver:

  1. Chain completeness. For a known 10-digit code, assert the returned chain has one node per significant level and terminates at a 2-digit chapter whose parent_code is NULL. A short chain means a missing parent_code link; the code raises LookupError by design rather than returning a partial lineage.
  2. As-of correctness across a rate change. Pick a code whose general_rate changed on a known date. Resolve it for the day before and the day after the change and assert the two calls return different rates. Same-rate results mean the as-of predicate was dropped from the recursive member, applying today’s rate to a back-dated entry.
  3. Boundary-day determinism. Resolve exactly on an effective_date. Because the window is half-open [effective_date, expiry_date), exactly one row must match per level — assert the fetch returns no duplicate hts_code at any depth. Two rows for one level means overlapping validity windows in the source data.
  4. Depth-guard trip. Inject a deliberately malformed row whose parent_code points back to a descendant. Confirm the CYCLE clause drops the cyclic rows (NOT is_cycle) and the depth < 6 cap prevents a runaway plan; the query must return, not hang.
  5. Non-existent as-of. Resolve a code for a date before its earliest effective_date. Assert LookupError is raised, not an empty success — an entry can never be filed against a code that was not yet in force.
  6. Decimal fidelity. Assert every general_rate in the chain is a Decimal and round-trips exactly (for example Decimal("0.0000") for a free rate), confirming asyncpg’s numeric adapter was not overridden to float.
  7. Single round trip. Enable statement logging and confirm one SELECT served the whole chain. A per-level loop in Python defeats the point of the recursive CTE and multiplies latency by the tree depth.

Edge cases & gotchas

  • Point-in-now leakage. The most damaging bug is a missing as-of predicate on the recursive member: the anchor is historical but the parents resolve to their current rows, blending eras in one chain. Apply the identical window at every level, and test it with step 2 above.
  • Overlapping validity windows. If the source ever emits two rows for the same code with overlapping [effective_date, expiry_date) ranges, the CTE returns both and the leaf rate becomes ambiguous. Enforce non-overlap as an exclusion constraint at load time; do not paper over it with LIMIT 1, which hides the data defect.
  • parent_code derived at query time. Deriving the parent by string prefix inside the recursive member (substring(hts_code, 1, length-2)) works but forbids the index on parent_code and slows every walk. Materialize parent_code as a stored column during ingestion instead.
  • Six-digit international codes. A 6-digit WCO subheading is a valid node but is not filable as a US entry. The resolver accepts 6/8/10 for lookup, but a chain that tops out or bottoms out at six digits should be flagged before it reaches entry assembly — route it the same way ambiguous codes are handled in Fallback Routing for Unmapped Codes.
  • Timezone-naive as-of dates. as_of selects the tariff window; a naive local date near a UTC boundary can pick the wrong day and therefore the wrong rate. Derive as_of from the timezone-aware entry event, never from date.today() on the server.
  • CYCLE requires PostgreSQL 14+ for the standard syntax. On 12–13 you must emulate it with an array path column and a NOT (p.hts_code = ANY(l.path)) guard. Keep the depth cap regardless; it is your backstop if the cycle guard is ever removed during a refactor.

Up: HTS Schedule Database Design