Designing ISO container hierarchy trees in PostgreSQL #

This guide implements the storage boundary for a maritime container tree in PostgreSQL — the ltree-backed schema, ISO 6346 and SOLAS constraints, slot-identifier normalization, and transactional insertion that turn a vessel’s Vessel → Bay → Row → Tier → Container topology into a low-latency, queryable structure that terminal operating systems (TOS), stowage planners, and customs gateways can trust.

Architecture Alignment #

This task is the persistence layer beneath the Container Hierarchy Data Models specification, which itself sits inside the Core Maritime Architecture & Taxonomy framework. This topic area defines the typed ContainerNode contract and the two coexisting storage patterns — adjacency lists for incremental deltas, materialized paths for subtree reads. This page implements the materialized-path side of that contract in PostgreSQL, enforcing the same invariants the Pydantic boundary asserts in memory as CHECK constraints and ltree path columns at the database layer, so a node that clears the application schema cannot silently violate it in storage. Commercial context still binds through the Bill of Lading Schema Mapping layer, and committed mutations feed the Port Call Workflow Design state machine as idempotent milestone events.

PostgreSQL offers several strategies for hierarchical data, but high-throughput port environments demand predictable read latency. Recursive CTEs (WITH RECURSIVE) degrade under concurrent load and thrash the buffer cache when traversing 10,000-slot trees. The production standard is the ltree extension with materialized-path indexing: a canonical path such as VES01.BAY_04.ROW_01.TIER_08.CONT_02 enables prefix matching, rapid slot-availability queries, and subtree isolation without recursive joins.

Prerequisites & Environment Setup #

• PostgreSQL 14+ with the ltree contrib module available. Enabling it needs a superuser or a pre-provisioned role (CREATE EXTENSION privilege).
• Python 3.11+ with asyncpg (async connection pooling), structlog (structured JSON logging), and pytest plus pytest-asyncio for the test tier. Install with pip install asyncpg structlog pytest pytest-asyncio.
• Environment variables: DATABASE_URL (e.g. postgresql://tos:***@yard-db:5432/hierarchy), and VESSEL_PREFIX_MAP if you resolve vessel call signs to ltree label prefixes at runtime.
• Spec references: ISO 6346 (freight container coding, the 4-alpha + 7-numeric owner/serial/check-digit format), ISO 9711-1 (stowage-position bay/row/tier notation used by BAPLIE LOC segments), and the SOLAS VGM (Verified Gross Mass) mandate under IMO SOLAS Chapter VI Regulation 2.

Step-by-step Implementation #

Step 1 — Provision the ltree extension and hierarchy table #

Enable ltree, then declare the table with the regulatory columns each node must carry. A node in a maritime tree is never purely geometric — it holds ISO 6346 identity, SOLAS VGM, IMDG hazard class, and customs state alongside its position.

-- Enable ltree extension (requires superuser or a pre-provisioned role)
CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE container_hierarchy (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    path ltree NOT NULL,
    iso_code VARCHAR(11) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PLANNED',
    vgm_weight_kg NUMERIC(8,2),
    imdg_class VARCHAR(10),
    customs_hold BOOLEAN DEFAULT FALSE,
    bl_reference VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Step 2 — Enforce ISO 6346 and SOLAS constraints at the storage boundary #

Push the invariants into the schema so no application path can bypass them. The iso_code pattern rejects anything that is not ISO 6346-shaped; nlevel(path) fixes tree depth at Vessel.Bay.Row.Tier (4) or with an explicit container position (5); the VGM range enforces the SOLAS structural window; and imdg_class accepts only IMDG classes 1–9 (with optional division) or NULL for non-hazardous cargo.

ALTER TABLE container_hierarchy
    -- ISO 6346: 4 alpha owner/category + 7 numeric serial+check
    ADD CONSTRAINT chk_iso_code CHECK (iso_code ~ '^[A-Z]{4}\d{7}$'),
    -- Vessel.Bay.Row.Tier[.Container]
    ADD CONSTRAINT chk_path_depth CHECK (nlevel(path) BETWEEN 4 AND 5),
    -- SOLAS VGM: positive and within structural limits
    ADD CONSTRAINT chk_vgm_range CHECK (vgm_weight_kg IS NULL OR vgm_weight_kg BETWEEN 1000 AND 35000),
    -- IMDG class 1-9 with optional division, or NULL
    ADD CONSTRAINT chk_imdg_class CHECK (imdg_class IS NULL OR imdg_class ~ '^[1-9](\.[0-9])?$');

-- GiST index for prefix matching and subtree queries (O(log n) traversal)
CREATE INDEX idx_container_path_gist ON container_hierarchy USING GIST (path);
-- B-tree for exact slot lookups and equality joins
CREATE INDEX idx_container_path_btree ON container_hierarchy (path);
-- Partial index for active operational queries (excludes historical/archived)
CREATE INDEX idx_active_containers ON container_hierarchy (path, status)
    WHERE status NOT IN ('DISCHARGED', 'ARCHIVED');

Step 3 — Normalize fragmented slot identifiers into canonical paths #

Format drift is guaranteed: a single vessel call may receive 0401082, BAY04-ROW01-TIER08-POS02, and 04-01-08-2 for the same physical slot, arriving from BAPLIE stow-location LOC segments, carrier JSON APIs, and OCR-scanned gate receipts. Every external identifier is mapped to a canonical ltree path before insertion. This mirrors the ISO 9711-1 positional parsing performed upstream in Document Ingestion & EDI Parsing Workflows; here it is the last gate before persistence.

import re
from typing import Optional

import structlog

logger = structlog.get_logger("maritime.hierarchy")


def normalize_slot_identifier(raw_input: str, vessel_prefix: str) -> str:
    """Parse fragmented EDI/API/OCR slot inputs into a canonical ltree path.

    Handles zero-padding inconsistencies, hyphen/space delimiters, and
    missing container-position indicators. Two unambiguous forms are accepted:
    field-name-delimited (BAY04ROW01TIER08POS02), parsed by label, and bare
    fixed-width numeric (2 bay, 2 row, 2 tier, optional 1-2 pos) so digits are
    never misallocated (0401082 -> 04/01/08/2, not 040/10/82).
    """
    cleaned = re.sub(r"[^A-Z0-9]", "", raw_input.upper())

    if any(kw in cleaned for kw in ("BAY", "ROW", "TIER")):
        match = re.match(r"BAY(\d{2,3})ROW(\d{2})TIER(\d{2})(?:(?:POS|CONT)(\d{1,2}))?$", cleaned)
    else:
        match = re.match(r"(\d{2})(\d{2})(\d{2})(\d{1,2})?$", cleaned)

    if not match:
        logger.error("invalid_slot_format", raw=raw_input, error="regex_mismatch")
        raise ValueError(f"Unparseable slot identifier: {raw_input}")

    g = match.groups()
    bay, row, tier = int(g[0]), int(g[1]), int(g[2])
    pos: Optional[int] = int(g[3]) if g[3] is not None else None

    if not (0 < bay <= 100):
        logger.warning("bay_out_of_range", bay=bay, action="clamped_to_max")
        bay = 100
    if not (0 < tier <= 12):
        logger.error("tier_exceeds_vessel_profile", tier=tier)
        raise ValueError("Tier exceeds standard vessel stacking profile")

    segments = [vessel_prefix, f"BAY_{bay:02d}", f"ROW_{row:02d}", f"TIER_{tier:02d}"]
    if pos is not None:
        segments.append(f"CONT_{pos:02d}")

    canonical = ".".join(segments)
    logger.info("slot_normalized", raw=raw_input, canonical=canonical)
    return canonical

Step 4 — Query available slots with prefix scans and fallback routing #

Stowage planners need millisecond responses. The materialized path turns “find the next open tier in this bay/row, excluding holds and IMDG conflicts” into an ltree prefix scan the GiST index answers in O(log n). Security middleware uses the same operator to lock a whole subtree — path <@ 'VES01.BAY_04'::ltree isolates every node under bay 04 without touching external systems, which is how Maritime Security Boundary Setup enforces reefer-plug and dangerous-goods zones at the row level.

-- Next available tier in a bay/row, excluding holds and IMDG conflicts
SELECT path, status, vgm_weight_kg
FROM container_hierarchy
WHERE path <@ 'VES01.BAY_04.ROW_01'::ltree
  AND status = 'AVAILABLE'
  AND customs_hold = FALSE
  AND imdg_class IS NULL
ORDER BY path ASC
LIMIT 1;

-- Fallback: if the primary bay is full, route to the adjacent bay using set
-- operations, avoiding application-level recursion
WITH primary_slots AS (
    SELECT path FROM container_hierarchy
    WHERE path <@ 'VES01.BAY_04'::ltree AND status = 'AVAILABLE'
),
fallback_slots AS (
    SELECT path FROM container_hierarchy
    WHERE path <@ 'VES01.BAY_06'::ltree AND status = 'AVAILABLE'
)
SELECT path FROM primary_slots
UNION ALL
SELECT path FROM fallback_slots
LIMIT 1;

Step 5 — Register containers transactionally with structured observability #

Wrap insertion in a transaction, reject duplicate active ISO codes before writing, and emit a structured event on commit. The INSERT casts the normalized string to ltree so the depth constraint fires at the boundary; a constraint violation surfaces as a typed exception the caller routes to quarantine rather than a silent partial write.

from typing import Any, Optional

import asyncpg
import structlog

logger = structlog.get_logger("maritime.hierarchy")


async def register_container_in_tree(
    pool: asyncpg.Pool,
    vessel_prefix: str,
    raw_slot: str,
    iso_code: str,
    vgm: Optional[float],
    imdg: Optional[str],
    bl_ref: str,
) -> dict[str, Any]:
    """Insert a container into the hierarchy tree with full regulatory validation."""
    canonical_path = normalize_slot_identifier(raw_slot, vessel_prefix)

    async with pool.acquire() as conn:
        async with conn.transaction():
            existing = await conn.fetchval(
                "SELECT id FROM container_hierarchy "
                "WHERE iso_code = $1 AND status != 'DISCHARGED'",
                iso_code,
            )
            if existing:
                logger.warning("duplicate_iso_detected", iso_code=iso_code, existing_id=str(existing))
                raise ValueError(f"Container {iso_code} already active in yard")

            try:
                result = await conn.fetchrow(
                    """
                    INSERT INTO container_hierarchy
                        (path, iso_code, vgm_weight_kg, imdg_class, bl_reference, status)
                    VALUES ($1::ltree, $2, $3, $4, $5, 'GATE_IN')
                    RETURNING id, path, created_at
                    """,
                    canonical_path, iso_code, vgm, imdg, bl_ref,
                )
            except asyncpg.CheckViolationError as exc:
                logger.error("constraint_violation", iso_code=iso_code, error=str(exc))
                raise RuntimeError("Node violates ISO 6346 / SOLAS / IMDG constraint") from exc

            logger.info(
                "container_registered",
                container_id=str(result["id"]),
                path=str(result["path"]),
                iso=iso_code,
                vgm_verified=vgm is not None,
                imdg_flag=imdg is not None,
            )
            return dict(result)

Edge Cases & Carrier Deviations #

• Ambiguous zero-padded slot codes. A bare 040108 is safe under fixed-width parsing (bay 04, row 01, tier 08), but a carrier that pads bay to three digits (004 for a 100+ bay vessel) breaks the \d{2} assumption. Detect vessel bay-count from the stowage plan header and select the numeric regex width accordingly rather than trusting a global default.
• Tier over the vessel profile. Tiers above 12 almost always signal a hold-vs-deck notation collision (deck tiers start at 82 in ISO 9711-1). normalize_slot_identifier rejects these outright; do not silently clamp, because a mis-tiered box corrupts deck-load stability calculations.
• ISO 6346 check-digit drift. The chk_iso_code constraint validates shape, not the modulo-11 check digit — deliberately. Real traffic contains physically valid boxes whose digit a legacy translator miscomputed; reject those at the DB layer and you drop a real container. Validate the check digit in a shared registry helper, flag check_digit_drift, and reconcile, exactly as the Container Hierarchy Data Models topic area prescribes.
• Orphaned subtrees from delta ordering. A BAPLIE delta can reference a bay whose header segment has not committed yet. Do not INSERT with a path whose parent labels are unresolved; route the node to a quarantine topic as ORPHAN_NODE and backfill once the plan header lands.
• Duplicate placements from AS2 retransmission. The active-ISO guard in Step 5 blocks a second GATE_IN, but downstream milestone events must also be idempotent — key them on (iso_code, milestone, event_hash) so a replay is a no-op for the Port Call Workflow Design consumer.

Verification & Testing #

Assert both the happy path and the constraint boundaries. The fixture below feeds the three drift variants for one physical slot and confirms they collapse to a single canonical path; the constraint test confirms the database rejects an over-limit VGM even when the application forgets to.

import pytest


@pytest.mark.parametrize(
    "raw, expected",
    [
        ("0401082", "VES01.BAY_04.ROW_01.TIER_08.CONT_02"),
        ("BAY04-ROW01-TIER08-POS02", "VES01.BAY_04.ROW_01.TIER_08.CONT_02"),
        ("04-01-08-2", "VES01.BAY_04.ROW_01.TIER_08.CONT_02"),
    ],
)
def test_slot_variants_converge(raw: str, expected: str) -> None:
    assert normalize_slot_identifier(raw, "VES01") == expected


def test_tier_over_profile_rejected() -> None:
    with pytest.raises(ValueError, match="stacking profile"):
        normalize_slot_identifier("040113", "VES01")  # tier 13


@pytest.mark.asyncio
async def test_vgm_over_limit_rejected(pool) -> None:
    with pytest.raises(RuntimeError, match="constraint"):
        await register_container_in_tree(
            pool, "VES01", "0401", "MSKU1234567", vgm=99000.0, imdg=None, bl_ref="BL123",
        )

A successful registration emits a single structured line — {"event": "container_registered", "path": "VES01.BAY_04.ROW_01.TIER_08", "iso": "MSKU1234567", "vgm_verified": true, "imdg_flag": false, ...} — which is the assertion target for log-based integration checks and the audit record customs and port state control expect.

Frequently Asked Questions #

Related #

• Container Hierarchy Data Models — the typed ContainerNode contract and dual storage patterns this schema implements
• Bill of Lading Schema Mapping — binds commercial context and seal numbers to physical hierarchy nodes
• Port Call Workflow Design — consumes committed hierarchy mutations as idempotent milestone events
• Maritime Security Boundary Setup — zero-trust controls that lock held subtrees at the row level
• Document Ingestion & EDI Parsing Workflows — the upstream domain that normalizes BAPLIE stow-location segments into placements

Up: Container Hierarchy Data Models — the parent model this PostgreSQL schema realizes.