# Sydonia Toolkit — full documentation
> Concatenation of the entire Sydonia Toolkit documentation for LLM
> ingestion. Generated by scripts/gen_llms_full.py from the docs sources.
> Canonical site: https://francoischastel.github.io/sydonia-toolkit/latest/
---
:material-anchor: SYDONIA · ASYCUDA World · PostgreSQL
# Query Sydonia: write friendly, run genuine
Write customs analytics against a **friendly logical model** — `declaration`,
`declaration_item`, `hs_code`, `tax_amount` — and **compile them to genuine
ASYCUDA World (SYDONIA) SQL you can actually run**, read-only, against a real
instance. The abstraction is easy; the output is real. Underneath is a faithful,
**fully-sourced** reconstruction of the whole customs model — now the logical
layer the compiler maps *from*.
55tables across 8 modules
49 / 6documented / inferred
100%cited public sourcing
0errors on a clean load
## Why it exists
ASYCUDA World is the customs platform used by **100+ countries**, but its
internal database schema is **wide, denormalised and proprietary** — painful to
query for analytics. So this project does two things. It rebuilds an
**information-equivalent reference model** using *only* public documentation —
official public technical table descriptions, national ASYCUDA World user manuals,
and open ISO/UN/WCO standards, every table traceable to a citation or honestly
flagged as inferred. And it ships a [**query compiler**](compiler/index.md) that
lets you write against that clean model and **compile to genuine Sydonia SQL** —
so the friendly names are the ergonomics, and the runnable statement is real.
The modeled version is **ASYCUDA World (v4)** — the current standard. The
[platform section](platform/index.md) covers the full version lineage
(ASYCUDA++ → World → ASY5), the XML wire formats, and where external systems
can integrate, so you can place this model against the deployment you actually
have.
!!! quote "Scope, stated plainly"
This is a **functional/reference reconstruction** for sandbox, integration,
analytics and training use. It is **not** an attempt to obtain or reproduce
UNCTAD's proprietary internal schema, and it contains no pirated software or
data from any live customs system.
## What you can do with it
- :material-cog-transfer:{ .lg .middle } **Compile to genuine Sydonia SQL**
---
Write a query against the friendly logical model; the compiler rewrites it
into runnable ASYCUDA World SQL via a CTE prelude. **Write friendly, run
genuine.**
[:octicons-arrow-right-24: The query compiler](compiler/index.md)
- :material-database-search:{ .lg .middle } **Query a real ASYCUDA World**
---
Point the same queries and skills at a live instance — read-only, metadata
only — for analytics, feature extraction and selectivity on *your* data.
[:octicons-arrow-right-24: Querying Sydonia](querying-sydonia/index.md)
- :material-database-import:{ .lg .middle } **Stand up a customs sandbox**
---
One `psql` command loads a clean schema, seed reference data, and a fully
worked manifest→release example. PostgreSQL 14+.
[:octicons-arrow-right-24: Quickstart](getting-started/quickstart.md)
- :material-file-tree:{ .lg .middle } **Understand the customs domain**
---
A guided map of manifests, the SAD declaration, valuation build-up,
per-item taxes, selectivity lanes and the document lifecycle.
[:octicons-arrow-right-24: Customs concepts](getting-started/concepts.md)
- :material-magnify:{ .lg .middle } **Query real join paths**
---
A cookbook of the join paths that matter: declaration → item → tax line,
manifest → B/L → cargo, assessed-vs-paid reconciliation.
[:octicons-arrow-right-24: Querying guide](guides/querying.md)
- :material-source-branch:{ .lg .middle } **Extend it safely**
---
Add tables and columns while keeping the conventions and the provenance
trail intact, so your fork stays as auditable as the original.
[:octicons-arrow-right-24: Extending guide](guides/extending.md)
- :material-shield-check:{ .lg .middle } **Trust the sourcing**
---
Every `CREATE TABLE` carries a `-- src:` or `-- inferred` tag. Coverage and
an official fit/gap analysis are published in full.
[:octicons-arrow-right-24: Provenance](provenance/index.md)
- :material-robot-happy:{ .lg .middle } **Drive it with Agent Skills**
---
A suite of standard Agent Skills — installable into any agent (Claude Code,
Cursor, Codex, …) via `npx skills add` — that set up, query, seed, extend and
validate the model directly inside your own codebase.
[:octicons-arrow-right-24: Agent Skills](skills/index.md)
- :material-earth:{ .lg .middle } **Know the platform**
---
ASYCUDA itself: the version lineage (v1 → World → ASY5), the XML wire
formats, the integration doors, and the clearance process.
[:octicons-arrow-right-24: The platform](platform/index.md)
- :material-brain:{ .lg .middle } **Build ML on it**
---
The research-backed blueprint for ML on declarations and plugging a risk
engine into selectivity — prototyped on this schema.
[:octicons-arrow-right-24: ML on customs data](guides/ml-risk-engine.md)
## A 30-second taste
```bash
createdb customs_sandbox
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/seed_reference.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/examples/e2e.sql
```
```text
--- Declaration summary ---
reg | type | status | lane | total_items | total_cif_value
-------+------+----------+------+-------------+-----------------
C 427 | IM4 | released | RED | 2 | 63300.0000
--- Total assessed vs receipt ---
total_assessed | receipt_amount
----------------+----------------
12132.5000 | 12132.5000
```
A single import declaration — two items, freight and insurance apportioned to
per-item CIF, duties and VAT calculated, routed to the RED lane, inspected,
paid and released — inserts with full referential integrity.
## For large-language-model consumption
This site publishes an [`llms.txt`](llms.txt) index and a concatenated
[`llms-full.txt`](llms-full.txt) so you can hand the entire model to an LLM in
one paste. See the [Agent Skills](skills/index.md) to wire it into an
agent workflow.
---
Sydonia Toolkit is independent and reconstructed from public
documentation. ASYCUDA and SYDONIA are programmes of UNCTAD; this project is not
affiliated with or endorsed by UNCTAD.
---
# Getting started
Three short pages take you from an empty database to understanding what every
table means.
- :material-rocket-launch:{ .lg .middle } **Quickstart**
---
The fastest path: create a database, load three SQL files, and watch a full
customs declaration flow through to release.
[:octicons-arrow-right-24: Quickstart](quickstart.md)
- :material-cog:{ .lg .middle } **Installation**
---
Prerequisites, PostgreSQL versions, Docker vs local, the `asycuda` schema,
idempotent reloads, and teardown.
[:octicons-arrow-right-24: Installation](installation.md)
- :material-school:{ .lg .middle } **Customs concepts**
---
A domain primer — manifest, bill of lading, the SAD declaration, valuation,
taxes, selectivity and the lifecycle — mapped to tables.
[:octicons-arrow-right-24: Customs concepts](concepts.md)
New to the customs domain? Read **Customs concepts** first — it makes the schema
read like a story instead of 55 tables.
Once the sandbox is up, the main event is querying: **[Querying
Sydonia](../querying-sydonia/index.md)** explains the real ASYCUDA World tables,
and **[the query compiler](../compiler/index.md)** turns friendly logical queries
into genuine Sydonia SQL you can run on a live system.
---
# Quickstart
Load the model and run the end-to-end example against a throwaway PostgreSQL
database. Total time: about a minute.
## Prerequisites
- **PostgreSQL 14 or newer** (`psql`, `createdb` on your `PATH`).
- A local server you can create a database on. No superuser needed — an ordinary
role that can `CREATE DATABASE` and `CREATE SCHEMA` is enough.
!!! tip "No local PostgreSQL?"
Spin one up with Docker in one line:
```bash
docker run --name customs -e POSTGRES_PASSWORD=customs -p 5432:5432 -d postgres:16
```
Then prefix the commands below with the connection, e.g.
`psql "postgresql://postgres:customs@localhost:5432/customs_sandbox" -f ...`.
## 1 · Get the files
```bash
git clone https://github.com/FrancoisChastel/sydonia-toolkit.git
cd sydonia-toolkit
```
The SQL lives under `Sydonia/`:
```text
Sydonia/schema/asycuda.sql # the model — 55 tables, 8 modules
Sydonia/schema/seed_reference.sql # reference / code-table seed data
Sydonia/examples/e2e.sql # a full manifest → release worked example
```
## 2 · Create a database and load the model
```bash
createdb customs_sandbox
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/seed_reference.sql
```
`-v ON_ERROR_STOP=1` makes `psql` exit non-zero on the first error, so a clean
run is a real signal. The schema creates and uses a dedicated **`asycuda`**
schema (namespace) — your `public` schema stays untouched.
## 3 · Run the end-to-end example
```bash
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/examples/e2e.sql
```
You should see a declaration assembled and reconciled:
```text
--- Declaration summary ---
reg | type | status | lane | total_items | total_cif_value
-------+------+----------+------+-------------+-----------------
C 427 | IM4 | released | RED | 2 | 63300.0000
--- Items with tax totals ---
item_number | hs_code | customs_value | taxes
-------------+---------+---------------+-----------
1 | 851712 | 42200.0000 | 6541.0000
2 | 610910 | 21100.0000 | 5591.5000
--- Total assessed vs receipt ---
total_assessed | receipt_amount
----------------+----------------
12132.5000 | 12132.5000
--- Lifecycle trail ---
sort_order | code | note
------------+------------+-------------------------------------
1 | stored | Draft captured
2 | registered | Validated & registered (C 427)
3 | assessed | Assessed: duties & taxes calculated
4 | paid | Paid — receipt RCPT-2026-0427
5 | released | Release order issued
```
That is a complete import: a manifest arrives from Shanghai, a broker files an
IM4 declaration with two items, freight and insurance are apportioned to
per-item CIF, duty and VAT are assessed, selectivity routes it RED, an officer
inspects it, payment is receipted, and a release order is issued — all with
foreign keys intact.
## 4 · Look around
```bash
psql -d customs_sandbox
```
```sql
SET search_path TO asycuda, public; -- (1)!
\dt -- list the 55 tables
SELECT count(*) FROM declaration_item; -- 2
\d+ declaration -- inspect the SAD general segment
```
1. Every table lives in the `asycuda` schema. Set the `search_path` once per
session and you can use bare table names.
## Next steps
- :material-school: **Understand what you loaded** — the
[customs concepts](concepts.md) primer maps the domain to the tables.
- :material-magnify: **Start querying** — the
[querying guide](../guides/querying.md) has the join paths and an analytics
cookbook.
- :material-map: **See the shape** — the
[entity-relationship diagram](../schema/erd.md) renders every foreign key.
- :material-check-decagram: **Trust it** — the
[validation skill](../skills/index.md) re-runs the clean-load and provenance
checks on demand.
## Tear it down
```bash
dropdb customs_sandbox
```
---
# Installation
The model is plain SQL — there is nothing to compile and no runtime dependency
beyond PostgreSQL itself.
## Requirements
| Requirement | Notes |
|-------------|-------|
| **PostgreSQL 14+** | The schema deliberately avoids 15-only features so it loads on 14. Tested on 14–16. |
| `psql` + `createdb` | Ships with any PostgreSQL client install. |
| A role that can `CREATE DATABASE` / `CREATE SCHEMA` | No superuser required. |
## The three SQL files (and load order)
Order matters — objects reference each other:
```text
1. Sydonia/schema/asycuda.sql creates schema `asycuda` + 55 tables + indexes
2. Sydonia/schema/seed_reference.sql fills the ref_* / code tables
3. Sydonia/examples/e2e.sql (optional) a worked manifest → release example
```
Load them with `ON_ERROR_STOP=1` so any problem fails loudly:
```bash
createdb customs_sandbox
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/seed_reference.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/examples/e2e.sql # optional
```
## The `asycuda` schema
Everything is created inside a dedicated PostgreSQL **schema** (namespace)
called `asycuda`, not in `public`. This keeps the model self-contained and easy
to drop, and lets it coexist with your own tables.
```sql
-- from asycuda.sql
DROP SCHEMA IF EXISTS asycuda CASCADE; -- (1)!
CREATE SCHEMA asycuda;
SET search_path TO asycuda, public;
```
1. The load is **idempotent**: re-running `asycuda.sql` drops and recreates the
whole schema, so you always get a clean slate. Anything you put *inside*
`asycuda` is discarded on reload — keep your own tables elsewhere.
To use bare table names in a session, set the search path once:
```sql
SET search_path TO asycuda, public;
SELECT count(*) FROM declaration; -- instead of asycuda.declaration
```
To make it the default for a database or role:
```sql
ALTER DATABASE customs_sandbox SET search_path TO asycuda, public;
```
## Verify the load
```sql
SET search_path TO asycuda, public;
-- 55 base tables expected
SELECT count(*) FROM information_schema.tables
WHERE table_schema = 'asycuda' AND table_type = 'BASE TABLE';
-- reference data present (transport modes, statuses, lanes, …)
SELECT count(*) FROM ref_transport_mode; -- 9
SELECT code, name FROM ref_selectivity_lane ORDER BY code;
```
!!! success "What a clean install looks like"
`asycuda.sql` and `seed_reference.sql` complete with **zero errors**, the
table count is **55**, and `e2e.sql` prints the declaration summary with
`total_assessed = receipt_amount`. Anything else is a real failure — read the
`psql` output.
## Docker one-liner
```bash
docker run --name customs -e POSTGRES_PASSWORD=customs -p 5432:5432 -d postgres:16
export PGURL="postgresql://postgres:customs@localhost:5432"
createdb "$PGURL/customs_sandbox" 2>/dev/null || \
psql "$PGURL/postgres" -c 'CREATE DATABASE customs_sandbox;'
for f in schema/asycuda.sql schema/seed_reference.sql examples/e2e.sql; do
psql -v ON_ERROR_STOP=1 "$PGURL/customs_sandbox" -f "Sydonia/$f"
done
```
## Troubleshooting
| Symptom | Cause / fix |
|---------|-------------|
| `permission denied to create database` | Use a role with `CREATEDB`, or create the DB as an admin and load into it. |
| `schema "asycuda" already exists` errors mid-file | You edited the file and removed the `DROP SCHEMA` guard — restore it, or `DROP SCHEMA asycuda CASCADE;` first. |
| `relation "…" does not exist` in the seed/e2e | Files loaded out of order — always load `asycuda.sql` **first**. |
| Bare table names "not found" in `psql` | Run `SET search_path TO asycuda, public;` for the session. |
| Syntax errors on `GENERATED ALWAYS AS IDENTITY` | Your server is < PostgreSQL 10 — upgrade; the model targets 14+. |
## Automate it with an agent
The [`customs-schema-setup`](../skills/index.md) Agent Skill does all of the
above for you — creating the database, loading the files in order, and reporting
a clean/failed result — and [`customs-validate`](../skills/index.md) re-runs the
full done-condition checks. The skills install into any agent (Claude Code,
Cursor, Codex, …) via `npx skills add`.
## Tear it down
```bash
dropdb customs_sandbox
# or, to keep the database but drop just the model:
psql -d customs_sandbox -c 'DROP SCHEMA asycuda CASCADE;'
```
---
# Customs concepts
The schema reads like a story once you know the domain. This page is that story:
how goods move through customs, and which tables capture each step. Names in
`code font` are tables you can find in the [schema reference](../schema/index.md).
## The big picture
```mermaid
flowchart LR
A[Carrier files
manifest] --> B[Broker files
SAD declaration]
B --> C[Valuation
& assessment]
C --> D{Selectivity
lane}
D -->|GREEN| F[Release]
D -->|YELLOW/RED| E[Documentary /
physical check]
E --> G[Payment
& receipt]
C --> G
G --> F
```
Two documents drive everything:
1. A **manifest** — what the *carrier* says is on board the ship/plane.
2. A **declaration (the SAD)** — what the *importer/broker* declares to customs,
item by item, so duty and tax can be assessed and the goods released.
Everything else — valuation, taxes, selectivity, payment, warehousing — hangs
off those two.
## Reference / configuration backbone
Before any transaction exists, customs needs **code tables**: countries, currencies,
ports, tariff codes, tax types, procedure codes, package types, and so on. These
are the `ref_*` tables, grounded in international standards:
| Concept | Table | Standard |
|---------|-------|----------|
| Countries | `ref_country` | ISO 3166 |
| Currencies | `ref_currency` | ISO 4217 |
| Ports / places | `ref_location` | UN/LOCODE |
| Commodity codes | `ref_hs_tariff` | Harmonized System (self-referential hierarchy) |
| Procedure codes | `ref_cpc_regime` | Customs Procedure Codes |
| Package types | `ref_package_type` | UN/ECE Rec 21 |
| Container size-types | `ref_container_type` | ISO 6346 |
| Delivery terms | `ref_incoterm` | Incoterms |
Coded columns elsewhere carry a **foreign key** to one of these tables rather
than repeating the code and its name — see [Reference &
configuration](../schema/reference-config.md).
## Traders and users
An **economic operator** — importer, exporter, consignee, declarant, broker or
carrier — is a `trader`, keyed by its Tax Identification Number (TIN). One trader
can play several roles (`trader_role`). People who log into the system are
`sys_user` rows.
## Manifest and cargo
When a vessel arrives, the carrier lodges a **manifest** (`manifest`) — voyage,
ports, dates, and totals. Each consignment on board is a **bill of lading**
(`bill_of_lading`, or air waybill):
- A **master** B/L covers a whole container from carrier to carrier.
- A **house** B/L is one consignee's consignment inside it (**degroupage** /
consolidation). The model captures this with a self-reference:
`bill_of_lading.master_bl_id → bill_of_lading.id`.
Physical `container` rows (with ISO 6346 size-types and seals) and the goods
lines on each B/L (`manifest_cargo_item`) complete the cargo picture. See
[Manifest & cargo](../schema/manifest.md).
## The declaration — the SAD
The **Single Administrative Document (SAD)** is the internationally standardised
customs declaration form (54 boxes, 8 parts). ASYCUDA models it as two segments,
and so does this schema:
- A **general segment** — one per consignment — is the `declaration` table:
parties, regime, transport, invoice totals, dates, status and selectivity lane.
- Repeating **item segments** are `declaration_item` rows: one per commodity
line, with HS code, origin, mass, packages, procedure and the **statistical /
customs value** that becomes the tax base.
```mermaid
erDiagram
declaration ||--o{ declaration_item : "boxes 31–49, one per line"
declaration_item ||--o{ declaration_tax_line : "per-tax: base·rate·amount"
declaration_item ||--o| item_value_note : "apportioned CIF"
```
See [Declaration (the SAD)](../schema/declaration.md).
## Valuation — how the tax base is built
Duty and tax are charged on the **customs value**, usually **CIF** (Cost,
Insurance, Freight). But an invoice is often FOB (goods only), with freight and
insurance quoted for the *whole* shipment. The **valuation note** builds the
value up and apportions the shared costs down to each item:
- `valuation_note` — declaration-level build-up:
`invoice FOB + freight + insurance + other → total CIF`.
- `item_value_note` — the same costs **apportioned per item** (by value share)
to produce each item's CIF, which is the tax base.
In the worked example, $3,000 freight + $300 insurance on a $60,000 FOB shipment
split 2:1 across the two items, giving item CIFs of $42,200 and $21,100.
## Taxes
Each item is taxed once per applicable **tax type** (import duty, VAT, excise,
fees). A `declaration_tax_line` records, for one item and one tax:
```text
tax_base · rate_percent (or specific_amount) · tax_amount · mode_of_payment
```
Taxes can cascade — VAT is often charged on *(customs value + import duty)* —
which is why the base is stored per line rather than derived. Totals are
computed by query, not stored (see the [querying guide](../guides/querying.md)).
## Selectivity — the risk lanes
Not every declaration is inspected. **Selectivity** routes each one into a lane:
| Lane | Meaning |
|------|---------|
| :material-circle:{ style="color:#16a34a" } **GREEN** | Automatic release |
| :material-circle:{ style="color:#ca8a04" } **YELLOW** | Documentary check |
| :material-circle:{ style="color:#dc2626" } **RED** | Physical examination |
| :material-circle:{ style="color:#2563eb" } **BLUE** | Released now, audited later |
`ref_selectivity_lane` defines the lanes, `risk_criterion` the rules that trigger
them, `selectivity_result` the lane a declaration landed in, and `inspection_act`
the officer's examination record. See [Selectivity & risk](../schema/selectivity.md).
## Accounting — payment and receipt
Once assessed, the declarant pays. `payment` records the settlement (cash or
against a deferred-payment `account`), `receipt` is the issued receipt, and
`account_movement` is the ledger entry. `guarantee` holds securities/bonds used
by suspense regimes. See [Accounting](../schema/accounting.md).
## Transit and suspense
Some goods are **not** cleared for home use immediately — duty is *suspended*:
- **Warehousing** — stored in a bonded `ref_warehouse` (`warehouse_entry` /
`warehouse_exit`) until later entry.
- **Transit** — moved under customs control between offices
(`transit_declaration`, with departure/transit/destination offices and a
guarantee).
- **Temporary admission** — imported for a limited time then re-exported
(`temporary_admission`).
See [Transit & suspense](../schema/transit-suspense.md).
## The document lifecycle
A declaration walks a fixed path, recorded in `declaration_status_history`:
```mermaid
stateDiagram-v2
direction LR
[*] --> stored
stored --> registered
registered --> assessed
assessed --> paid
paid --> released
released --> [*]
registered --> queried
registered --> cancelled
```
Manifests have their own lifecycle (`manifest_status_history`), and every
significant action is written to the cross-cutting `audit_log`.
## Where to go next
- [Schema overview](../schema/index.md) — the 8 modules in one map.
- [Worked example](../guides/worked-example.md) — this whole story, as SQL.
- [Data dictionary](../schema/data-dictionary.md) — every column, defined.
- [The platform](../platform/index.md) — ASYCUDA itself: versions, wire
formats, and the real clearance state machine behind this lifecycle.
---
# Querying Sydonia
ASYCUDA World — **Sydonia** in its French-speaking installs — does not keep a
tidy relational model under the hood. Its physical database is **wide,
denormalised, and optimised for the Java engine**, not for the analyst. The
general segment is copied into every item row, HS codes are shattered across five
columns, code and name live side by side with no foreign key, and every reference
table carries a validity window. UNCTAD **does not publish the physical schema**,
so most of what follows is reconstructed from the public layer — the official
Tables Description documents (**S013 reference, S014 declaration, S015 manifest,
S016 accounting**) and the XML wire format.
This section is the part of the toolbox about **querying the real thing**. It
walks the real table families you would hit against a live Sydonia RDBMS, names
the columns that *are* publicly pinned, and is honest about the ones that are not.
!!! warning "The must-request gap — exact physical names are instance-specific"
A handful of column and table names **are** public and appear verbatim below,
because they show up in the [Tables Description](../platform/asycuda-world.md)
and the [XML messages](../platform/xml-messages.md): the `SAD_Tax`
`COD`/`BSE`/`RAT`/`AMT`/`MOP`/`TYP` roots, the HS split `TAR_HSC_NB1..5`, the
`PTY_RED`/`YEL`/`GRE`/`BLU` colour flags, `VIT_CIF`/`VIT_STV`, the `INSTANCE_ID`
engine key, and `VALID_FROM`/`VALID_TO`. **Everything else is the shape, not
the exact spelling.** The remaining column names on these pages follow AW's
prefix conventions and match the toolbox's [mock database](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/Sydonia/adapters/mock_asycuda_world.sql)
— treat them as *plausible defaults you must confirm against your instance*,
not as guaranteed identifiers. When you get real access, the physical names
are the first thing to request.
## Two ways to query
- :material-database-search:{ .lg .middle } **Directly, against the real tables**
---
Write SQL against `SAD_General_Segment`, `SAD_Item`, `SAD_Tax`, `GEN_TAB`,
`BOL_TAB`, the `UN*` reference tables — and handle the denormalisation
yourself. This section documents those tables so you can.
[:octicons-arrow-right-24: Declaration tables](declaration-tables.md)
- :material-wand:{ .lg .middle } **Via the logical model + compiler**
---
Write friendly SQL against the toolbox's clean logical names; the **query
compiler** rewrites it into genuine Sydonia SQL — dedup, HS concat, validity
filtering and all — so you never touch the gotchas by hand.
[:octicons-arrow-right-24: The query compiler](../compiler/index.md)
Both hit the same real database. Direct SQL gives you full control and nothing
between you and the engine; the compiler trades that for correctness-by-default on
the five things that reliably bite (see [joins & gotchas](joins-and-gotchas.md)).
## The real table families
The physical schema falls into four layers. Reference tables sit underneath
everything (codes + validity); manifests describe cargo that arrives; declarations
clear it; tax and selectivity hang off each declaration.
```mermaid
flowchart TD
subgraph ref["Reference — S013 (UN* / xx*TAB)"]
R1["UNCTYTAB · UNCURTAB · UNCUOTAB
UNTAXTAB · UNHS*TAB · UNCP*TAB
(code + name inline · VALID_FROM/TO)"]
end
subgraph man["Manifest — S015"]
M1["GEN_TAB
general segment"]
M2["BOL_TAB
bills of lading"]
M3["CTN_TAB / BOL_CTN_TAB
containers + goods lines"]
M1 --> M2 --> M3
end
subgraph dec["Declaration — S014"]
D1["SAD_General_Segment
one declaration (repeated per item)"]
D2["SAD_Item
per commodity · TAR_HSC_NB1..5"]
D1 --> D2
end
subgraph tax["Tax & selectivity — S014/S016"]
T1["SAD_Tax
COD/BSE/RAT/AMT/MOP/TYP"]
T2["SAD_SELECTIVITY · INSP_ACT_TAB
PTY_* colour flags"]
end
ref -.->|codes| man
ref -.->|codes| dec
man -->|write-off| dec
D2 --> T1
D1 --> T2
```
## The four sub-pages
- :material-file-document-outline:{ .lg .middle } **Declaration tables**
---
`SAD_General_Segment`, `SAD_Item`, `SAD_Tax` — the SAD spine, the HS split,
the valuation build-up and the tax roots, with real-SQL examples.
[:octicons-arrow-right-24: Declaration tables](declaration-tables.md)
- :material-ferry:{ .lg .middle } **Manifest tables**
---
`GEN_TAB`, `BOL_TAB`, `CTN_TAB` / `BOL_CTN_TAB` — the cargo manifest, its
bills of lading and containers, master ⇄ house.
[:octicons-arrow-right-24: Manifest tables](manifest-tables.md)
- :material-table-key:{ .lg .middle } **Reference tables**
---
The `UN*` / `xx*TAB` code catalogue — code + name inline (no FK) and the
`VALID_FROM`/`VALID_TO` temporal-validity pattern.
[:octicons-arrow-right-24: Reference tables](reference-tables.md)
- :material-alert-octagon-outline:{ .lg .middle } **Joins & gotchas**
---
The five things that bite — `INSTANCE_ID`, repeated general segment, HS split,
code+name inline, validity windows — each with symptom and fix.
[:octicons-arrow-right-24: Joins & gotchas](joins-and-gotchas.md)
## Related
- [ASYCUDA World — the modeled version](../platform/asycuda-world.md) — what the
real platform is and where the public/private line falls.
- [XML messages & the wire format](../platform/xml-messages.md) — the other public
window onto the field-level model.
- [How faithful is the reconstruction?](../provenance/fit.md) — the table-by-table
official-vs-toolbox fit, the source of every real name here.
- [Useful queries](../guides/useful-queries.md) — worked queries against the clean
logical model.
---
# Querying the declaration tables
The declaration — the **Single Administrative Document** — is the core of
ASYCUDA World's data. Physically it lives in three tables you will query most
often: **`SAD_General_Segment`** (consignment level), **`SAD_Item`** (per
commodity) and **`SAD_Tax`** (per tax line). These names come straight from the
official S014 Declaration Tables and are used verbatim by the toolbox's
[compiler mapping](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/compiler/mappings/asycuda-world.yml).
!!! info "What is pinned vs. instance-specific"
Column names printed in **bold** are publicly documented (S014 / the XML
layer) and appear verbatim in the [fit analysis](../provenance/fit.md) — the
`SAD_Tax` roots, `TAR_HSC_NB1..5`, `VIT_CIF`/`VIT_STV`, the `PTY_*` flags,
`INSTANCE_ID`. Plain-code names (e.g. `SGS_CUO_COD`) follow AW's prefix
conventions and match the bundled mock DB, but the exact spelling on a real
instance is *instance-specific* — confirm it.
## `SAD_General_Segment` — one declaration, repeated per item
The general segment carries the consignment-level facts: office, type, parties,
currency, totals, status and the selectivity colour flags. The catch that defines
querying it: **AW repeats the entire general segment into every item row** — so a
naïve join over items double-counts it. Deduplicate with `DISTINCT` on
**`INSTANCE_ID`** (see [joins & gotchas](joins-and-gotchas.md#repeated-general-segment)).
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | Engine key — the declaration's identity | `declaration.id` |
| `SGS_CUO_COD` | Customs office code | `declaration.office_id` |
| `SGS_TYP_COD` | Declaration model/type (IM4, EX1…) | `declaration.declaration_type_id` |
| `SGS_REG_NBR`, `SGS_REG_DAT` | Registration serial + date | `declaration.registration_number` · `registration_date` |
| `SGS_DEC_REF` | Declarant reference (LRN/UCR) | `declaration.trader_reference` |
| `SGS_EXP_COD`, `SGS_CNE_COD`, `SGS_DCL_COD` | Exporter · consignee · declarant | `declaration.exporter_id` · `consignee_id` · `declarant_id` |
| `SGS_CUR_COD` | Invoice currency | `declaration.currency_id` |
| `SGS_INV_AMT`, `SGS_CIF_AMT` | Total invoice · total CIF | `declaration.total_invoice_amount` · `total_cif_value` |
| **`STA`** | Lifecycle status code | `declaration.status_id` |
| **`PTY_RED`**, **`PTY_YEL`**, **`PTY_GRE`**, **`PTY_BLU`** | Selectivity lane colour flags (`'1'`/`'0'`) | `declaration.selectivity_lane_id` |
The **`STA`** status code drives the clearance state machine (stored →
registered → assessed → paid → released); the **`PTY_*`** flags are a
colour *domain*, not a foreign key — exactly one is set to `'1'`. Decode them with
a `CASE`:
```sql
-- Declarations registered this month, with their assigned lane.
SELECT g.INSTANCE_ID,
g.SGS_REG_NBR,
g.SGS_REG_DAT,
g.STA AS status,
CASE WHEN g.PTY_RED = '1' THEN 'RED'
WHEN g.PTY_YEL = '1' THEN 'YELLOW'
WHEN g.PTY_GRE = '1' THEN 'GREEN'
WHEN g.PTY_BLU = '1' THEN 'BLUE' END AS lane
FROM SAD_General_Segment g
WHERE g.SGS_REG_DAT >= DATE '2026-07-01'
ORDER BY g.SGS_REG_DAT;
```
## `SAD_Item` — one row per commodity line
Each item carries the HS code, origin, mass, packages and the valuation values.
Two documented shapes matter here:
- **HS is split across `TAR_HSC_NB1..5`** — five national-precision fragments you
must concatenate to get a full commodity code
([gotcha](joins-and-gotchas.md#hs-split)).
- **`VIT_CIF` is the customs value** (the tax base) and **`VIT_STV` the
statistical value**; the build-up columns **`VIT_FOB` / `VIT_FRT` / `VIT_INS`**
hold FOB + apportioned freight + insurance that sum toward CIF.
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | Item identity | `declaration_item.id` |
| `ITM_SGS_ID` | Parent general-segment key | `declaration_item.declaration_id` |
| `ITM_NBR` | Item/line number | `declaration_item.item_number` |
| **`TAR_HSC_NB1..5`** | HS code, split into 5 fragments | `declaration_item.hs_code` (concatenated) |
| **`VIT_CIF`** | Customs value (the tax base) | `declaration_item.customs_value` |
| **`VIT_STV`** | Statistical value | `declaration_item.statistical_value` |
| **`VIT_FOB`**, **`VIT_FRT`**, **`VIT_INS`** | FOB + apportioned freight + insurance → CIF | `item_value_note.item_fob` · `apportioned_freight` · `apportioned_insurance` |
| `ITM_NET_MAS`, `ITM_GRS_MAS` | Net / gross mass | `declaration_item.net_mass` · `gross_mass` |
| `ITM_ORG_COD` | Country of origin | `declaration_item.country_origin_id` |
| `ITM_PKG_NBR` | Number of packages | `declaration_item.number_of_packages` |
```sql
-- Items on one declaration, with the HS code reassembled and the value build-up.
SELECT i.ITM_NBR,
i.TAR_HSC_NB1 || i.TAR_HSC_NB2 || i.TAR_HSC_NB3
|| i.TAR_HSC_NB4 || i.TAR_HSC_NB5 AS hs_code,
i.ITM_ORG_COD AS origin,
i.VIT_FOB, i.VIT_FRT, i.VIT_INS,
i.VIT_CIF AS customs_value
FROM SAD_Item i
WHERE i.ITM_SGS_ID = 1 -- the declaration's INSTANCE_ID
ORDER BY i.ITM_NBR;
```
!!! note "The build-up is denormalised onto the item"
Unlike the toolbox's separate `item_value_note` table, the real `SAD_Item`
carries `VIT_FOB`/`VIT_FRT`/`VIT_INS`/`VIT_CIF` *inline* on the same row. The
invoice is usually FOB but duty is charged on CIF, so this is where the tax
base is assembled per item.
## `SAD_Tax` — the tax lines (the pinned roots)
`SAD_Tax` is the one table whose **column roots are fully public**. One row per
item per applicable tax; because taxes cascade (VAT on customs value + duty), the
base is stored per line rather than recomputed.
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | Tax-line identity | `declaration_tax_line.id` |
| `TAX_ITM_ID` | Parent item key | `declaration_tax_line.declaration_item_id` |
| **`COD`** | Tax type (duty / VAT / excise / fee) | `declaration_tax_line.tax_type_id` |
| **`BSE`** | Tax base (amount the rate applies to) | `declaration_tax_line.tax_base` |
| **`RAT`** | Rate (ad valorem) | `declaration_tax_line.rate_percent` |
| **`AMT`** | Computed amount | `declaration_tax_line.tax_amount` |
| **`MOP`** | Mode of payment | `declaration_tax_line.mode_of_payment` |
| **`TYP`** | Manual (`'1'`) vs auto-calculated | `declaration_tax_line.is_manual` |
```sql
-- Duty & VAT collected per declaration, from the real tax roots.
SELECT g.INSTANCE_ID,
g.SGS_REG_NBR,
sum(x.AMT) FILTER (WHERE x.COD = 'IMP') AS import_duty,
sum(x.AMT) FILTER (WHERE x.COD = 'VAT') AS vat,
sum(x.AMT) AS total_tax
FROM SAD_General_Segment g
JOIN SAD_Item i ON i.ITM_SGS_ID = g.INSTANCE_ID
JOIN SAD_Tax x ON x.TAX_ITM_ID = i.INSTANCE_ID
GROUP BY g.INSTANCE_ID, g.SGS_REG_NBR
ORDER BY total_tax DESC;
```
!!! tip "Manual taxes and totals — the derived siblings"
- Manual (officer-keyed) taxes live in **`SAD_Ask_Tax`** in the official
model; the toolbox folds them into `SAD_Tax` via the **`TYP`** flag
(`TYP = '1'`).
- **`SAD_Global_Taxes`** and **`SAD_Tax_Totals`** are *summary* tables. Prefer
to **derive** these by aggregating `SAD_Tax` (as above) rather than trusting
a possibly-stale total — the toolbox never stores them.
## Assemble a full declaration
Because the general segment repeats, dedupe it before you total taxes — or you
count the header once per item.
```sql
-- Declaration header + item lines + per-item tax, correctly deduplicated.
WITH decl AS (
SELECT DISTINCT g.INSTANCE_ID, g.SGS_REG_NBR, g.STA
FROM SAD_General_Segment g
WHERE g.SGS_DEC_REF = 'REF-2026-0001'
)
SELECT d.SGS_REG_NBR,
i.ITM_NBR,
i.TAR_HSC_NB1 || i.TAR_HSC_NB2 || i.TAR_HSC_NB3
|| i.TAR_HSC_NB4 || i.TAR_HSC_NB5 AS hs_code,
i.VIT_CIF AS customs_value,
sum(x.AMT) AS taxes
FROM decl d
JOIN SAD_Item i ON i.ITM_SGS_ID = d.INSTANCE_ID
LEFT JOIN SAD_Tax x ON x.TAX_ITM_ID = i.INSTANCE_ID
GROUP BY d.SGS_REG_NBR, i.ITM_NBR, i.TAR_HSC_NB1, i.TAR_HSC_NB2,
i.TAR_HSC_NB3, i.TAR_HSC_NB4, i.TAR_HSC_NB5, i.VIT_CIF
ORDER BY i.ITM_NBR;
```
The same query, written against the friendly logical names and compiled for you,
is on the [declaration schema page](../schema/declaration.md#example-assemble-a-declaration).
## Related
- [Manifest tables](manifest-tables.md) — the cargo the declaration writes off.
- [Reference tables](reference-tables.md) — decode `SGS_CUO_COD`, `COD`, `ITM_ORG_COD`.
- [Joins & gotchas](joins-and-gotchas.md) — the dedup, HS-concat and validity traps.
- [XML messages](../platform/xml-messages.md) — the SAD box → XML tag → column map.
- [Declaration schema](../schema/declaration.md) — the same fields, normalised.
---
# Querying the manifest tables
The **cargo manifest** is what the carrier declares is on board — the upstream
document the importer's declaration later writes off against. Physically it sits
in the S015 Manifest Tables: **`GEN_TAB`** (the manifest general segment),
**`BOL_TAB`** (bills of lading) and **`CTN_TAB`** / **`BOL_CTN_TAB`**
(containers), plus goods lines within each B/L. These table names come from the
official S015 description and the [fit analysis](../provenance/fit.md#manifest-module-official-s015-our-tables);
the XML equivalent is the **AWMDS** stream documented in
[XML messages](../platform/xml-messages.md#cargo-manifest-awmds).
!!! warning "Manifest column names are almost entirely instance-specific"
S015 pins the **table** names (`GEN_TAB`, `BOL_TAB`, `CTN_TAB`,
`BOL_CTN_TAB`) and the *field semantics* (voyage, ports, totals, parties,
seals…), but **not the physical column names** — unlike `SAD_Tax`, no public
root list exists for the manifest columns. The `GEN_*` / `BOL_*` / `CTN_*`
names below follow AW's prefix conventions and are *illustrative defaults*.
Confirm every one against your instance before running.
## `GEN_TAB` — the manifest general segment
One row per manifest: office, voyage/flight, carrier and shipping agent, ports of
loading/unloading, dates, declared totals and status. It is the parent of every
bill of lading on the voyage.
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | Manifest identity (engine key) | `manifest.id` |
| `GEN_CUO_COD` | Customs office of arrival | `manifest.office_id` |
| `GEN_REG_YER`, `GEN_REG_NBR`, `GEN_REG_DAT` | Registration year / number / date | `manifest.manifest_year` · `registration_number` |
| `GEN_VOY_NBR` | Voyage / flight number | `manifest.voyage_number` |
| `GEN_TSP_IDE` | Identity of transport (vessel) | `manifest.identity_of_transport` |
| `GEN_CAR_COD` + `GEN_CAR_NAM` | Carrier — **code and name inline** | `manifest.carrier_id` |
| `GEN_AGT_COD` + `GEN_AGT_NAM` | Shipping agent (code + name inline) | `manifest.shipping_agent_id` |
| `GEN_POL_COD`, `GEN_POD_COD` | Place of loading / unloading (UN/LOCODE) | `manifest.place_departure_id` · `place_destination_id` |
| `GEN_DEP_DAT`, `GEN_ARR_DAT` | Departure / arrival dates | `manifest.date_of_departure` · `date_of_arrival` |
| `GEN_TOT_BOL`, `GEN_TOT_PKG`, `GEN_TOT_CTN`, `GEN_TOT_GRS` | Total B/Ls · packages · containers · gross mass | `manifest.total_bols` · `total_packages` · `total_containers` · `total_gross_mass` |
| `STA` | Manifest lifecycle status | `manifest.status_id` |
!!! note "Carrier is code + name inline"
Like all AW coded fields, the carrier is stored as **`GEN_CAR_COD` and
`GEN_CAR_NAM` together on the same row** — there is no FK to a carrier table.
See [code+name inline](joins-and-gotchas.md#code-name-inline).
## `BOL_TAB` — the bills of lading
One row per transport document. A **master** B/L covers a full container moving
carrier-to-carrier; a **house** B/L is one consignee's consignment inside it
(**degroupage**), expressed by a self-reference on the previous-master B/L
reference.
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | B/L identity | `bill_of_lading.id` |
| `BOL_GEN_ID` | Parent manifest key | `bill_of_lading.manifest_id` |
| `BOL_REF` | B/L reference number | `bill_of_lading.bl_reference` |
| `BOL_LIN_NBR`, `BOL_SUB_NBR` | Line / sub-line number | — |
| `BOL_NAT_COD` | Nature (22 export / 23 import / 24 transit / 28 transhipment) | `bill_of_lading.bl_nature_id` |
| `BOL_MST_REF` | Previous **master** B/L reference (degroupage) | `bill_of_lading.master_bl_id` |
| `BOL_EXP_COD`, `BOL_CNE_COD`, `BOL_NOT_COD` | Exporter · consignee · notify party | — |
| `BOL_PKG_NBR`, `BOL_GRS_MAS`, `BOL_VOL` | Packages · gross mass · volume | `bill_of_lading.number_of_packages` · `gross_mass` |
| `BOL_FRT_AMT`, `BOL_INS_AMT` | Freight / insurance value + currency | — |
```sql
-- All bills of lading on one voyage, with package and mass totals per B/L.
SELECT b.BOL_REF,
b.BOL_NAT_COD AS nature,
b.BOL_PKG_NBR AS packages,
b.BOL_GRS_MAS AS gross_mass,
CASE WHEN b.BOL_MST_REF IS NULL THEN 'master' ELSE 'house' END AS bl_role
FROM GEN_TAB g
JOIN BOL_TAB b ON b.BOL_GEN_ID = g.INSTANCE_ID
WHERE g.GEN_VOY_NBR = 'V2026-042'
ORDER BY b.BOL_REF;
```
## `CTN_TAB` / `BOL_CTN_TAB` — containers & goods lines
Containers are attached to a B/L. Depending on the instance they are held in a
standalone **`CTN_TAB`** or a B/L-scoped **`BOL_CTN_TAB`** junction; both carry
the container reference, ISO 6346 type, empty/full flag, seals, weights, volume
and dangerous-goods flag. Goods/commodity lines (HS, description, packages) sit
within each B/L.
| Physical column | Meaning | Logical equivalent |
|-----------------|---------|--------------------|
| **`INSTANCE_ID`** | Container identity | `container.id` |
| `CTN_BOL_ID` | Parent B/L key | `container.bl_id` |
| `CTN_REF` | Container reference (ISO 6346) | `container.container_number` |
| `CTN_TYP_COD` | Container type code | `container.container_type_id` |
| `CTN_IND` | Empty / full indicator | `container.empty_full` |
| `CTN_PKG_NBR` | Packages inside | `container.number_of_packages` |
| `CTN_GRS_MAS`, `CTN_VOL` | Gross mass · volume | `container.gross_mass` · `volume` |
| `CTN_SEAL` | Seal numbers | `container.seals` |
| `CTN_DGR` | Dangerous-goods flag | `container.dangerous_goods` |
```sql
-- Containers and their B/Ls for an arriving voyage, with fill status.
SELECT b.BOL_REF,
c.CTN_REF,
c.CTN_TYP_COD AS iso_type,
CASE WHEN c.CTN_IND = 'F' THEN 'full' ELSE 'empty' END AS fill,
c.CTN_GRS_MAS AS gross_mass
FROM GEN_TAB g
JOIN BOL_TAB b ON b.BOL_GEN_ID = g.INSTANCE_ID
JOIN CTN_TAB c ON c.CTN_BOL_ID = b.INSTANCE_ID
WHERE g.GEN_VOY_NBR = 'V2026-042'
ORDER BY b.BOL_REF, c.CTN_REF;
```
!!! info "Vehicle sub-segment — a known gap"
The manifest XML also defines a **vehicle sub-segment** (chassis/VIN/engine/make
for RoRo cargo). It is documented but **not modelled** as a table in the
toolbox — noted in [Coverage](../provenance/coverage.md). If your instance
carries it, the physical name is instance-specific; request it.
## Grounded in the wire format
Each of these tables maps onto a segment of the **AWMDS** cargo XML stream
(root ``), so the same field semantics are visible two ways:
| AWMDS element | Manifest table | Toolbox table |
|---------------|----------------|---------------|
| `` | `GEN_TAB` | `manifest` |
| `` | `BOL_TAB` | `bill_of_lading` |
| `` | `CTN_TAB` / `BOL_CTN_TAB` | `container` |
| `` / `` | goods lines in `BOL_TAB` | `manifest_cargo_item` |
## Related
- [Declaration tables](declaration-tables.md) — what clears this cargo.
- [Reference tables](reference-tables.md) — decode office, nature, container type.
- [Joins & gotchas](joins-and-gotchas.md) — `INSTANCE_ID` keys and code+name inline.
- [XML messages — AWMDS](../platform/xml-messages.md#cargo-manifest-awmds) — the cargo stream.
- [Manifest schema](../schema/manifest.md) — the same fields, normalised.
---
# Querying the reference tables
Everything coded on a declaration or manifest — the office, the country of
origin, the currency, the tax type, the HS chapter, the procedure — resolves
through ASYCUDA World's **reference tables**: the S013 `UN*` / `xx*TAB` code
catalogue. Two patterns define how you query them, and both differ sharply from
the toolbox's normalised `ref_*` model.
!!! info "Two prefixes, one catalogue"
You will see both **`UN*`** (the shared UNCTAD base, e.g. `UNCTYTAB`) and
**`xx*TAB`** (national instance tables, where `xx` is the country code) for
the same domains. The domain roots (`CTY`, `CUR`, `CUO`, `TAX`, `HS`, `CP`…)
are documented in [S013](../platform/asycuda-world.md); the exact prefix on
your instance is instance-specific.
## Pattern 1 — code + name stored inline (no foreign key)
The toolbox normalises coded fields into an FK pointing at a `ref_*` table. The
**real AW schema does the opposite**: it stores the **code *and* its name
together on the same row** of the referencing table — e.g. a manifest carries
both `GEN_CAR_COD` and `GEN_CAR_NAM`. The reference table then holds the same
`_COD` + `_NAM` pair as its own two columns.
This is confirmed in the [fit analysis](../provenance/fit.md): *"store code **and**
name inline (`GEN_CAR_COD` + `GEN_CAR_NAM`)"* versus the toolbox's *"FK to a
`ref_*` table"*. Practically, it means **you often do not need to join at all** —
the human-readable name is already sitting beside the code. When you *do* join a
reference table (to filter on validity, or to pull an attribute not denormalised
onto the fact row), you join **on the code**, not on a surrogate id
([details](joins-and-gotchas.md#code-name-inline)).
## Pattern 2 — temporal validity (`VALID_FROM` / `VALID_TO`)
Every `UN*` reference table carries **`VALID_FROM`** and **`VALID_TO`** dates. A
code is only valid for a declaration if the declaration's date falls inside that
window — a currency, tariff line or tax rate can be superseded, and the old row
stays in the table so historical declarations still resolve correctly. An open
`VALID_TO` (`NULL`) means "still current".
The toolbox collapses this to an `is_active` boolean (plus explicit
`valid_from/to` on rate tables), but against the real database **you must filter
by date yourself**, or you will match retired codes and multiply rows.
```sql
-- Currently-valid customs offices (as of today).
SELECT o.CUO_COD, o.CUO_NAM
FROM UNCUOTAB o
WHERE o.VALID_FROM <= CURRENT_DATE
AND (o.VALID_TO IS NULL OR o.VALID_TO >= CURRENT_DATE)
ORDER BY o.CUO_COD;
```
```sql
-- Resolve a code as it was valid ON a specific declaration date — the correct,
-- point-in-time lookup for historical declarations.
SELECT t.TAX_COD, t.TAX_NAM
FROM UNTAXTAB t
WHERE t.TAX_COD = 'VAT'
AND t.VALID_FROM <= DATE '2026-07-06'
AND (t.VALID_TO IS NULL OR t.VALID_TO >= DATE '2026-07-06');
```
!!! warning "Filter to one valid row, or you double-count"
Skipping the validity predicate is the classic reference-table bug: a code
that was re-issued has **two rows**, so an unfiltered join returns each fact
twice. Always constrain to the row valid on the relevant date. The
[query compiler](../compiler/index.md) injects this filter automatically.
## The main reference tables (S013 crib)
Drawn from the [fit analysis](../provenance/fit.md#reference-module-official-s013-un-tables-our-ref_-tables)
direct-match list. Every one carries the code + name inline and the
`VALID_FROM`/`VALID_TO` window.
| AW table | Domain | Toolbox `ref_*` |
|----------|--------|-----------------|
| `xxCTYTAB` / `UNCTYTAB` | Countries | `ref_country` |
| `xxCURTAB` | Currencies | `ref_currency` |
| `xxRATTAB` | Exchange rates | `ref_exchange_rate` |
| `xxCUOTAB` / `UNCUOTAB` | Customs offices | `ref_customs_office` |
| `xxLOCTAB` | Locations (UN/LOCODE) | `ref_location` |
| `xxMOTTAB` | Transport modes | `ref_transport_mode` |
| `xxPKGTAB` | Package types | `ref_package_type` |
| `xxCTNTAB` | Container types | `ref_container_type` |
| `xxUOMTAB` | Units of measure | `ref_unit_of_measure` |
| `xxTODTAB` | Incoterms | `ref_incoterm` |
| `xxHS1-6TAB` / `xxTARTAB` | Tariff / HS | `ref_hs_tariff` |
| `xxCP1/3/4TAB` | Procedures (CPC / regime) | `ref_cpc_regime` |
| `xxTAXTAB` / `UNTAXTAB` | Tax types | `ref_tax_type` |
| `xxRULTAB` / `xxTAXTAR` | Tax rules / rates | `ref_tax_rate` |
| `xxATDTAB` | Document types | `ref_document_type` |
| `xxCP3TAB` | Exemption / relief codes | `ref_exemption_code` |
| `xxMODTAB` | Declaration types | `ref_declaration_type` |
| `xxNATTAB` | B/L nature | `ref_bl_nature` |
| `xxWHSTAB` | Warehouses | `ref_warehouse` |
| `xxCAR/DEC/CMP/PRPTAB` | Economic operators | `trader` (+ `trader_role`) |
!!! note "Some `UN*` tables are folded inline, not modelled separately"
Domains such as preference (`xxPRFTAB`), valuation method (`xxVAMTAB`), means
of payment (`xxMOPTAB`) and quota (`xxQUOTAB`) exist as reference tables in AW
but the toolbox keeps them as plain coded columns on the fact row rather than
as separate `ref_*` tables — see the [fit analysis](../provenance/fit.md) and
[Coverage](../provenance/coverage.md) for the full list and rationale.
## A join that resolves codes correctly
Putting both patterns together — decode a declaration's office and origin against
reference tables, valid on the registration date:
```sql
SELECT g.SGS_REG_NBR,
o.CUO_NAM AS office,
ctry.CTY_NAM AS origin_country
FROM SAD_General_Segment g
JOIN SAD_Item i ON i.ITM_SGS_ID = g.INSTANCE_ID
JOIN UNCUOTAB o ON o.CUO_COD = g.SGS_CUO_COD
AND o.VALID_FROM <= g.SGS_REG_DAT
AND (o.VALID_TO IS NULL OR o.VALID_TO >= g.SGS_REG_DAT)
JOIN UNCTYTAB ctry ON ctry.CTY_COD = i.ITM_ORG_COD
AND ctry.VALID_FROM <= g.SGS_REG_DAT
AND (ctry.VALID_TO IS NULL OR ctry.VALID_TO >= g.SGS_REG_DAT)
WHERE g.SGS_DEC_REF = 'REF-2026-0001';
```
## Related
- [Joins & gotchas](joins-and-gotchas.md) — code+name inline and validity, in depth.
- [Declaration tables](declaration-tables.md) · [Manifest tables](manifest-tables.md) — the codes that resolve here.
- [The query compiler](../compiler/index.md) — writes the validity filter for you.
- [Reference & config schema](../schema/reference-config.md) — the normalised `ref_*` side.
---
# Joins & gotchas
Querying the real ASYCUDA World database is not hard because the SQL is exotic —
it is hard because the schema is **wide and denormalised** in five specific ways
that quietly produce wrong answers. Each one below is drawn from the
["Aspect" table in the fit analysis](../provenance/fit.md#verdict) and reproduced
in the toolbox's [mock database](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/Sydonia/adapters/mock_asycuda_world.sql).
For each: the **symptom** you will see, and the **fix**.
## 1. `INSTANCE_ID` — the engine keys {: #instance-id }
AW keys rows on **`INSTANCE_ID`** (the object-engine identity), not on a
business-friendly surrogate. Child rows reference their parent's `INSTANCE_ID`
through a `*_*_ID` column — `SAD_Item.ITM_SGS_ID` → `SAD_General_Segment.INSTANCE_ID`,
`SAD_Tax.TAX_ITM_ID` → `SAD_Item.INSTANCE_ID`, `BOL_TAB.BOL_GEN_ID` →
`GEN_TAB.INSTANCE_ID`.
!!! danger "Symptom"
You join on a registration number or a reference string and get duplicates or
misses — those are not unique keys. Or you assume an auto-increment `id` and
find none.
**Fix** — always traverse via `INSTANCE_ID` and the `*_ID` parent pointers:
```sql
JOIN SAD_Item i ON i.ITM_SGS_ID = g.INSTANCE_ID -- item → general segment
JOIN SAD_Tax x ON x.TAX_ITM_ID = i.INSTANCE_ID -- tax → item
```
## 2. The general segment is repeated into every item {: #repeated-general-segment }
The single most surprising shape: **AW copies the entire general segment into
every `SAD_Item` row** (and, on the cargo side, into every `BOL_TAB` row). There
is not one header row and N item rows — there are N rows that each carry the full
header *plus* their item.
!!! danger "Symptom"
Header-level totals come out multiplied by the item count. `sum(SGS_CIF_AMT)`
over a joined result counts the CIF once per item; a declaration with 3 items
reports 3× its value.
**Fix** — deduplicate the header on `INSTANCE_ID` before aggregating:
```sql
WITH decl AS (
SELECT DISTINCT g.INSTANCE_ID, g.SGS_REG_NBR, g.SGS_CIF_AMT
FROM SAD_General_Segment g
)
SELECT sum(SGS_CIF_AMT) AS total_cif FROM decl; -- counts each declaration once
```
## 3. HS code is split across `TAR_HSC_NB1..5` {: #hs-split }
The commodity code is stored as **five national-precision fragments** —
`TAR_HSC_NB1`, `TAR_HSC_NB2`, `TAR_HSC_NB3`, `TAR_HSC_NB4`, `TAR_HSC_NB5` — not a
single `hs_code`. The first fragments give the HS-2/HS-4/HS-6 international code;
the later ones carry national precision and may be blank.
!!! danger "Symptom"
Filtering `WHERE hs_code = '851712'` finds nothing — there is no such column.
Grouping "by HS" groups by one fragment and merges unrelated commodities.
**Fix** — concatenate the fragments (and be aware trailing fragments can be
empty strings, not `NULL`):
```sql
SELECT i.TAR_HSC_NB1 || i.TAR_HSC_NB2 || i.TAR_HSC_NB3
|| i.TAR_HSC_NB4 || i.TAR_HSC_NB5 AS hs_code
FROM SAD_Item i;
-- prefix match on a 6-digit code:
-- WHERE i.TAR_HSC_NB1 || i.TAR_HSC_NB2 || i.TAR_HSC_NB3 = '851712'
```
## 4. Code and name are stored inline — there is no FK {: #code-name-inline }
AW stores a coded value as **the code and its name together on the same row**
(`GEN_CAR_COD` + `GEN_CAR_NAM`), not as an FK into a lookup table. The reference
table holds the same `_COD` + `_NAM` pair.
!!! danger "Symptom"
You look for a foreign-key `carrier_id` to join and there isn't one. Or you
join a reference table on a numeric id and match nothing — the join key is the
**code string**, and validity is unfiltered so you may match a retired row.
**Fix** — the name is usually already on the row, so *no join is needed*; when
you do join a reference table, join **on the code**, and add the validity
predicate (gotcha 5):
```sql
-- the name is inline — no join required:
SELECT g.GEN_CAR_COD, g.GEN_CAR_NAM FROM GEN_TAB g;
-- when you must join, join on the code + filter validity:
JOIN UNCTYTAB c ON c.CTY_COD = i.ITM_ORG_COD
AND c.VALID_FROM <= g.SGS_REG_DAT
AND (c.VALID_TO IS NULL OR c.VALID_TO >= g.SGS_REG_DAT)
```
## 5. Reference tables carry `VALID_FROM` / `VALID_TO` {: #validity }
Every `UN*` reference table keeps **superseded codes** alongside current ones,
distinguished by a **`VALID_FROM`/`VALID_TO`** window. A code is correct for a
declaration only if the declaration's date falls inside it; `VALID_TO IS NULL`
means still current.
!!! danger "Symptom"
A reference join returns two rows for one code (old + new), silently doubling
the fact rows. Or a historical declaration resolves against today's re-issued
code and shows the wrong name/rate.
**Fix** — constrain every reference join to the row valid on the relevant date:
```sql
JOIN UNTAXTAB t ON t.TAX_COD = x.COD
AND t.VALID_FROM <= g.SGS_REG_DAT
AND (t.VALID_TO IS NULL OR t.VALID_TO >= g.SGS_REG_DAT)
```
## The compiler handles all five for you
Every fix above is mechanical — which is exactly why the toolbox's
[**query compiler**](../compiler/index.md) bakes them in. You write friendly SQL
against clean logical names; it emits **genuine Sydonia SQL** with the
`INSTANCE_ID` traversal, the `DISTINCT` dedup, the HS `concat`, the code-keyed
reference views and the `VALID_FROM`/`VALID_TO` filter already applied — so you
never re-derive them and never ship a query that silently double-counts.
!!! example "Before / after — logical vs compiled"
**You write** (against the logical model — no gotchas visible):
```sql
SELECT hs_code, sum(customs_value) AS value
FROM declaration_item
GROUP BY hs_code;
```
**The compiler emits** (against the real `SAD_Item` — gotchas 1, 3 handled):
```sql
SELECT concat(i.TAR_HSC_NB1, i.TAR_HSC_NB2, i.TAR_HSC_NB3,
i.TAR_HSC_NB4, i.TAR_HSC_NB5) AS hs_code,
sum(i.VIT_CIF) AS value
FROM SAD_Item i
GROUP BY concat(i.TAR_HSC_NB1, i.TAR_HSC_NB2, i.TAR_HSC_NB3,
i.TAR_HSC_NB4, i.TAR_HSC_NB5);
```
The mapping that drives this rewrite —
[`compiler/mappings/asycuda-world.yml`](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/compiler/mappings/asycuda-world.yml)
— is the same one whose physical names this section documents, so the docs and
the tooling never drift apart.
## Quick reference
| # | Gotcha | Symptom | Fix |
|:-:|--------|---------|-----|
| 1 | `INSTANCE_ID` keys | Duplicates/misses on business keys | Traverse via `INSTANCE_ID` + `*_ID` pointers |
| 2 | General segment repeated | Header totals × item count | `DISTINCT` header on `INSTANCE_ID` |
| 3 | HS split `NB1..5` | No `hs_code` column; wrong grouping | Concatenate the 5 fragments |
| 4 | Code + name inline | No FK; join on wrong key | Read inline name, or join on the code |
| 5 | `VALID_FROM`/`VALID_TO` | Double rows; wrong historical code | Filter to the row valid on the date |
## Related
- [Declaration tables](declaration-tables.md) · [Manifest tables](manifest-tables.md) · [Reference tables](reference-tables.md)
- [The query compiler](../compiler/index.md) — the automated way past all five.
- [How faithful is the reconstruction?](../provenance/fit.md) — the source "Aspect" table.
- [Useful queries](../guides/useful-queries.md) — worked queries on the logical model.
---
# The query compiler
**Write friendly, run genuine.** You write analytics queries against the
toolbox's clean [logical model](../schema/index.md) — `declaration`,
`declaration_item`, `tax_amount`, `hs_code` — and the compiler rewrites them into
**genuine ASYCUDA World SQL** that executes against the real, wide, denormalised
physical schema. The friendly names are the ergonomics; the compiled statement is
what a real SYDONIA instance would run.
!!! abstract "The pivot, in one line"
This toolbox is no longer only a *reference model*. Its primary job is now to
let you **query a real ASYCUDA World** — you write against the reconstruction,
and the compiler turns that into runnable Sydonia SQL. The reconstruction
became the **logical layer** the compiler maps *from*.
## The abstraction in one picture
```text
query spec (no SQL) ──build──► LOGICAL SQL ──compile──► GENUINE SYDONIA SQL
from / where / select declaration, SAD_General_Segment,
tax_amount, hs_code SAD_Tax.AMT, TAR_HSC…
```
Two entry points, one destination:
```mermaid
flowchart LR
A[query spec
YAML · no SQL] -->|build| B[LOGICAL SQL
friendly names]
B2[LOGICAL SQL
hand-written] --> C
B -->|compile| C[GENUINE SYDONIA SQL
runs on real AW]
C -->|read-only| D[(real / mock
ASYCUDA World)]
style B fill:#0f766e22,stroke:#0f766e
style C fill:#d9770622,stroke:#d97706
```
## Why an abstraction at all
A real ASYCUDA World database is **wide, denormalised, and mostly non-public**.
Writing analytics directly against it is painful, because:
| The real schema does this… | …so a raw query must |
|----------------------------|----------------------|
| Keys are engine `INSTANCE_ID` values | know the engine key convention |
| The general segment is repeated into **every item row** | `DISTINCT` the header itself |
| The HS code is split across `TAR_HSC_NB1..5` | concatenate five columns |
| Code **and** name are stored inline (`GEN_CAR_COD` + `GEN_CAR_NAM`) | fake a foreign key by code |
| `UN*` / `xx*TAB` reference rows carry `VALID_FROM` / `VALID_TO` | filter to currently-valid rows |
| Tax roots are `COD` / `BSE` / `RAT` / `AMT` / `MOP` on `SAD_Tax` | remember the terse field roots |
The logical model hides every one of these. You write
`di.hs_code`, `sum(tl.tax_amount)` and `d.selectivity_lane_id = 'RED'`; the
compiler bakes the gotchas into the output.
## How it works — the CTE prelude
The compiler scans your query for the **logical tables** it references, then
prepends each one as a **Common Table Expression** that `SELECT`s-and-aliases
from the real ASYCUDA World tables (per the [mapping](mapping.md)). Your query is
left **untouched** — the CTEs simply make the friendly names resolve to the real
schema. The result is **one standalone statement**, runnable anywhere, needing no
privilege to create views.
## Before and after
You write this friendly logical query:
```sql
SELECT di.hs_code, sum(tl.tax_amount) AS taxes
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY di.hs_code;
```
`python -m compiler compile` turns it into this genuine Sydonia SQL — note the
`concat` of the split HS code and the `SAD_Tax` field roots, all injected by the
prelude while your `SELECT` stays exactly as written:
```sql
WITH
declaration_item AS (
SELECT
i.INSTANCE_ID AS id,
i.ITM_SGS_ID AS declaration_id,
i.ITM_NBR AS item_number,
concat(i.TAR_HSC_NB1, i.TAR_HSC_NB2, i.TAR_HSC_NB3, i.TAR_HSC_NB4, i.TAR_HSC_NB5) AS hs_code,
i.VIT_CIF AS customs_value,
i.VIT_STV AS statistical_value,
i.ITM_NET_MAS AS net_mass,
i.ITM_GRS_MAS AS gross_mass,
i.ITM_ORG_COD AS country_origin_id,
i.ITM_PKG_NBR AS number_of_packages
FROM SAD_Item i
),
declaration_tax_line AS (
SELECT
x.INSTANCE_ID AS id,
x.TAX_ITM_ID AS declaration_item_id,
x.COD AS tax_type_id,
x.BSE AS tax_base,
x.RAT AS rate_percent,
x.AMT AS tax_amount,
x.MOP AS mode_of_payment,
(x.TYP = '1') AS is_manual
FROM SAD_Tax x
)
SELECT di.hs_code, sum(tl.tax_amount) AS taxes
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY di.hs_code;
```
!!! success "The round-trip guarantee"
The **same** logical query returns the **same** results whether run on the
reconstruction sandbox (logical) or compiled and run on the mock physical
database (genuine). See [Running the compiled SQL](running.md).
## Install
The compiler is pure standard library except for **PyYAML** (the mapping is
human-edited YAML):
```bash
pip install pyyaml
python -m compiler compile my_query.sql
```
## Where to go next
- :material-code-braces:{ .lg .middle } **Write logical SQL**
---
Author queries against the friendly names, `compile` them from a file or
stdin, and see what the compiler detects and rewrites.
[:octicons-arrow-right-24: Logical SQL & compile](logical-sql.md)
- :material-form-select:{ .lg .middle } **Build without SQL**
---
A tiny YAML query spec — `from` / `join` / `where` / `select` — that becomes
logical SQL, then genuine Sydonia SQL.
[:octicons-arrow-right-24: The query builder](builder.md)
- :material-map:{ .lg .middle } **The mapping**
---
How each logical table maps to its real AW source, per-instance overrides,
and materialising persistent views with `emit-views`.
[:octicons-arrow-right-24: The mapping](mapping.md)
- :material-play-circle:{ .lg .middle } **Run it**
---
The sandbox, the mock ASYCUDA World database (proving the round-trip), and a
real instance — read-only.
[:octicons-arrow-right-24: Running the SQL](running.md)
## Related
- [Querying Sydonia](../querying-sydonia/index.md) — the wider story of running
against a real ASYCUDA World, and the [joins and gotchas](../querying-sydonia/joins-and-gotchas.md).
- [Querying the model](../guides/querying.md) and
[useful queries](../guides/useful-queries.md) — the logical queries the compiler
turns genuine.
- [Running on a real ASYCUDA World](../platform/running-on-real-asycuda.md) — the
deployment, FDW and ETL detail behind the compiled SQL.
!!! note "Scope"
The compiler targets read **analytics** queries (`SELECT` / `WITH`) — the
project's use case. It is not a general SQL transpiler and deliberately does
**not** handle writes or DDL.
---
# Writing logical SQL
**Logical SQL is just SQL against the friendly names.** The names you use — the
tables and columns of the [reconstructed schema](../schema/index.md) —
`declaration`, `declaration_item`, `declaration_tax_line`, `ref_country`,
`hs_code`, `tax_amount` — are exactly the [logical layer](../schema/index.md) the
compiler maps *from*. If a query runs on the sandbox, it compiles.
!!! tip "You already know the names"
Everything in the [querying guide](../guides/querying.md) and the
[useful queries](../guides/useful-queries.md) library **is** logical SQL. Any
of those queries can be piped straight through `compile`.
## The `compile` command
`compile` reads one logical SQL statement and prints the genuine Sydonia SQL. It
takes a **file**, or `-` for **stdin**:
```bash
# from a file
python -m compiler compile my_query.sql
# from stdin
echo "SELECT * FROM declaration WHERE selectivity_lane_id = 'RED'" \
| python -m compiler compile -
```
Add `--logical` to stop *before* compiling and just echo the logical SQL back —
handy when the SQL comes from the [builder](builder.md) and you want to inspect
it first:
```bash
python -m compiler compile my_query.sql --logical
```
!!! note "One dependency"
The compiler needs **PyYAML** (`pip install pyyaml`) to read the mapping.
Nothing else — it is otherwise pure standard library.
## What the compiler detects and rewrites
The mechanism is the **CTE prelude**. The compiler:
1. **Scans** your query for logical table names — literal- and comment-aware, so
a table name inside a string or a `--` comment never triggers a false match.
2. **Emits a CTE** for each referenced table, `SELECT`-ing and aliasing from the
real ASYCUDA World source per the [mapping](mapping.md), in the mapping's
declaration order (stable, dependency-friendly output).
3. **Prepends** those CTEs to your query. If your query already starts with
`WITH`, the compiler *splices* its CTEs in front of your list rather than
nesting a second `WITH`.
4. **Drops** a leading `SET search_path …;` — irrelevant once the friendly names
are resolved by CTEs.
Your original `SELECT` body is otherwise left byte-for-byte unchanged.
## The gotchas it bakes in
Each CTE quietly handles a shape difference so your query never sees it:
| Real-schema gotcha | What the CTE does |
|--------------------|-------------------|
| Engine `INSTANCE_ID` keys | aliased `AS id` so surrogate-PK joins keep working |
| General segment repeated per item row | `declaration` is emitted `SELECT DISTINCT` — one row per declaration |
| HS code split `TAR_HSC_NB1..5` | `concat(...)` into a single `hs_code` |
| Code + name stored inline | `ref_*` CTEs are code-keyed (`id` := the business code) |
| `VALID_FROM` / `VALID_TO` on reference rows | a `WHERE now()::date BETWEEN …` validity filter |
| Colour flags `PTY_RED/YEL/GRE/BLU` | a `CASE` collapses them to a `selectivity_lane_id` code |
## A worked example — declaration header
The `declaration` logical table demaps the header's repetition and the colour
flags. This logical query:
```sql
SELECT id, registration_number, selectivity_lane_id
FROM declaration
WHERE selectivity_lane_id = 'RED';
```
compiles to (note `SELECT DISTINCT` and the injected `CASE`):
```sql
WITH
declaration AS (
SELECT DISTINCT
g.INSTANCE_ID AS id,
g.SGS_CUO_COD AS office_id,
g.SGS_TYP_COD AS declaration_type_id,
g.SGS_REG_NBR AS registration_number,
g.SGS_REG_DAT AS registration_date,
g.SGS_DEC_REF AS trader_reference,
g.SGS_CNE_COD AS consignee_id,
g.SGS_DCL_COD AS declarant_id,
g.SGS_EXP_COD AS exporter_id,
g.SGS_CUR_COD AS currency_id,
g.SGS_INV_AMT AS total_invoice_amount,
g.SGS_CIF_AMT AS total_cif_value,
g.STA AS status_id,
CASE WHEN g.PTY_RED = '1' THEN 'RED' WHEN g.PTY_YEL = '1' THEN 'YELLOW'
WHEN g.PTY_GRE = '1' THEN 'GREEN' WHEN g.PTY_BLU = '1' THEN 'BLUE' END AS selectivity_lane_id
FROM SAD_General_Segment g
)
SELECT id, registration_number, selectivity_lane_id
FROM declaration
WHERE selectivity_lane_id = 'RED';
```
## A worked example — a reference table
Reference tables carry the validity filter automatically. This logical query:
```sql
SELECT c.name FROM ref_country c WHERE c.id = 'CN';
```
compiles to — the code-keyed `id`, the code-keyed `iso_alpha2`, and the
`VALID_FROM`/`VALID_TO` filter, all injected:
```sql
WITH
ref_country AS (
SELECT
c.CTY_COD AS id,
c.CTY_COD AS iso_alpha2,
c.CTY_NAM AS name
FROM UNCTYTAB c
WHERE now()::date BETWEEN c.VALID_FROM AND coalesce(c.VALID_TO, DATE '9999-12-31')
)
SELECT c.name FROM ref_country c WHERE c.id = 'CN';
```
!!! info "Placeholders warn, not fail"
If the active mapping (base + [overrides](mapping.md)) still contains an
unfilled `{{placeholder}}`, `compile` prints the SQL and emits a
`-- warning:` on stderr naming the placeholders — a nudge to supply a
per-instance overrides file, not a hard error.
## Test the result read-only
Compiled SQL is genuine Sydonia SQL — so validate it **before** you trust it on
real data. Both paths are **privacy-preserving**: they check the query against
schema metadata and a read-only plan, and never return row data.
```bash
# the bundled shell tester (read-only, metadata only)
skills/customs-query/scripts/test_query.sh ""
```
The [`customs-query`](../skills/index.md) skill drives the same
**customs-query-tester** MCP from plain English — safe against a database holding
real customs declarations. See [Running the compiled SQL](running.md) for how to
point it at the sandbox, the mock database, or a real instance.
## Related
- [The query builder](builder.md) — generate logical SQL from a no-SQL spec.
- [The mapping](mapping.md) — what each logical name resolves to, and how to
override it per instance.
- [Querying the model](../guides/querying.md) ·
[Useful queries](../guides/useful-queries.md) — the logical query library.
---
# The query builder
The **builder** turns a small, no-SQL **query spec** into logical SQL — which the
[compiler](logical-sql.md) then turns into genuine Sydonia SQL. It is deliberately
thin: a convenience over logical SQL for people who would rather describe a query
than write it, **not** a new query language.
```text
query spec (YAML) ──build──► LOGICAL SQL ──compile──► GENUINE SYDONIA SQL
```
## The spec shape
A spec is YAML (or JSON). It maps directly onto the clauses of a `SELECT`:
```yaml
from: declaration
join:
- declaration_item on declaration_item.declaration_id = declaration.id
- declaration_tax_line on declaration_tax_line.declaration_item_id = declaration_item.id
where:
- declaration.selectivity_lane_id = 'RED'
select:
- declaration_item.hs_code
- sum(declaration_tax_line.tax_amount) as taxes
group_by: [declaration_item.hs_code]
order_by: [taxes desc]
limit: 10
```
### The keys
Only `from` and `select` are required; everything else is optional. Each key
accepts a single value or a list.
| Key | Accepts | Becomes |
|-----|---------|---------|
| `from` | a logical table name (**required**) | `FROM ` |
| `select` | expression(s) (**required**) | `SELECT , …` |
| `join` | `" on "` **or** a `{table, on, type}` object | `JOIN ON ` |
| `where` | condition(s), AND-ed | `WHERE … AND …` |
| `group_by` | expression(s) | `GROUP BY …` |
| `having` | condition(s), AND-ed | `HAVING … AND …` |
| `order_by` | expression(s) | `ORDER BY …` |
| `limit` | an integer | `LIMIT n` |
!!! note "Two ways to write a join"
The string form `"declaration_item on declaration_item.declaration_id = declaration.id"`
always produces an inner `JOIN`. For an outer join, use the object form and
set `type`:
```yaml
join:
- table: inspection_act
on: inspection_act.declaration_id = declaration.id
type: left join
```
## Worked example — spec to logical to genuine
Run the builder on the spec above with `--logical` to see the generated
**logical SQL**:
```bash
python -m compiler build my_spec.yml --logical
```
```sql
SELECT declaration_item.hs_code,
sum(declaration_tax_line.tax_amount) as taxes
FROM declaration
JOIN declaration_item ON declaration_item.declaration_id = declaration.id
JOIN declaration_tax_line ON declaration_tax_line.declaration_item_id = declaration_item.id
WHERE declaration.selectivity_lane_id = 'RED'
GROUP BY declaration_item.hs_code
ORDER BY taxes desc
LIMIT 10;
```
Drop the `--logical` flag and the same command runs it through the compiler to
produce **genuine Sydonia SQL** — the three referenced logical tables become
CTEs over `SAD_General_Segment`, `SAD_Item` and `SAD_Tax`, and your body is
left as written:
```bash
python -m compiler build my_spec.yml
```
```sql
WITH
declaration AS (
SELECT DISTINCT
g.INSTANCE_ID AS id,
…
CASE WHEN g.PTY_RED = '1' THEN 'RED' … END AS selectivity_lane_id
FROM SAD_General_Segment g
),
declaration_item AS (
SELECT
i.INSTANCE_ID AS id,
i.ITM_SGS_ID AS declaration_id,
concat(i.TAR_HSC_NB1, i.TAR_HSC_NB2, i.TAR_HSC_NB3, i.TAR_HSC_NB4, i.TAR_HSC_NB5) AS hs_code,
…
FROM SAD_Item i
),
declaration_tax_line AS (
SELECT
x.TAX_ITM_ID AS declaration_item_id,
x.AMT AS tax_amount,
…
FROM SAD_Tax x
)
SELECT declaration_item.hs_code,
sum(declaration_tax_line.tax_amount) as taxes
FROM declaration
JOIN declaration_item ON declaration_item.declaration_id = declaration.id
JOIN declaration_tax_line ON declaration_tax_line.declaration_item_id = declaration_item.id
WHERE declaration.selectivity_lane_id = 'RED'
GROUP BY declaration_item.hs_code
ORDER BY taxes desc
LIMIT 10;
```
The builder and the compiler share the same `--mapping` and `--overrides`
options, so a spec targets a specific instance exactly the way raw logical SQL
does — see [the mapping](mapping.md).
## When to prefer raw logical SQL instead
The builder is intentionally minimal. Reach for **[logical SQL](logical-sql.md)**
directly whenever a query needs anything the spec can't express, including:
- **CTEs / `WITH`** — window-function preludes, pre-aggregation before a join
(as in the [effective-rate](../guides/useful-queries.md) and league-table
queries).
- **`LATERAL`** subqueries — e.g. the assessed-vs-paid reconciliation.
- **Window functions**, `FILTER (WHERE …)`, `percentile_cont`, `DISTINCT ON`, set
operations, or anything else beyond the flat clause list above.
There is no loss of power in dropping to logical SQL — the compiler treats both
identically. The spec is simply the easiest on-ramp for straightforward
`from / join / where / select / group_by` reports.
## Related
- [Writing logical SQL](logical-sql.md) — the target of the builder, and the
richer queries the spec can't cover.
- [The mapping](mapping.md) — how referenced tables resolve, and per-instance
overrides.
- [Running the compiled SQL](running.md) — where to actually run the output.
---
# The mapping
The **mapping** is the single source of truth the compiler reads to build every
CTE — and the same file drives the persistent-view adapter. It lives at
[`compiler/mappings/asycuda-world.yml`](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/compiler/mappings/asycuda-world.yml)
and maps each of the toolbox's friendly logical tables to its real ASYCUDA World
source: the physical table, and for every logical column the physical expression
that produces it.
## The structure
Each entry under `tables:` describes one logical table. A typical entry:
```yaml
tables:
declaration:
source: SAD_General_Segment
alias: g
distinct: true # AW repeats the general segment per item -> one row per declaration
columns:
id: g.INSTANCE_ID
office_id: g.SGS_CUO_COD
registration_number: g.SGS_REG_NBR
status_id: g.STA
selectivity_lane_id: >-
CASE WHEN g.PTY_RED = '1' THEN 'RED' WHEN g.PTY_YEL = '1' THEN 'YELLOW'
WHEN g.PTY_GRE = '1' THEN 'GREEN' WHEN g.PTY_BLU = '1' THEN 'BLUE' END
```
| Field | Meaning |
|-------|---------|
| `source` | the real AW physical table this logical table reads from |
| `alias` | the alias bound to that source inside the CTE (default `t`) |
| `columns` | a map of **logical → physical**: each logical column name to the SQL expression that produces it (`i.VIT_CIF`, a `concat(...)`, a `CASE`, …) |
| `distinct` | emit `SELECT DISTINCT` — used for `declaration`, whose header is repeated into every item row |
| `valid` | `{from:, to:}` — adds a `now()::date BETWEEN … AND coalesce(…, DATE '9999-12-31')` validity filter for `UN*`/`xx*TAB` reference tables |
| `where` | an extra predicate AND-ed into the CTE |
| `raw` | a literal CTE body used for tables with **no real catalogue** — e.g. selectivity lanes and declaration statuses are materialised from a `VALUES` list |
The compiler renders each entry as a CTE (`build_cte`) — that's the whole
mechanism. Column expressions are copied verbatim into the `SELECT`, aliased to
the logical name.
## What the default targets
The default mapping targets the **publicly-documented physical shape**. Some names
are pinned because the public docs fix them; the rest follow AW's prefix
conventions:
| Pinned from public docs | Following AW conventions (instance-specific) |
|-------------------------|----------------------------------------------|
| `SAD_Tax` roots `COD` / `BSE` / `RAT` / `AMT` / `MOP` / `TYP` | the `SGS_*` general-segment columns |
| `TAR_HSC_NB1..5` HS split | `SAD_STATUS_LOG`, `INSP_ACT_TAB`, `SEL_PARAM_TAB` names |
| `PTY_*` colour flags | the `UN*TAB` / `xx*TAB` reference table names |
| `VIT_CIF` / `VIT_STV` valuation build-up | operator/trader source `UNOPTAB` |
| `VALID_FROM` / `VALID_TO` on reference rows | |
!!! warning "Exact physical names are instance-specific"
A **real** deployment's physical table and column names are
version/instance-specific and **not public**. The defaults here are what the
bundled [mock database](running.md) uses; for a real instance, pin the true
names in a **per-instance overrides file**.
## Per-instance overrides
Overrides are a second YAML file **deep-merged over the base** mapping — you
override **only what differs**, and everything else is inherited. See
[`compiler/mappings/overrides.example.yml`](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/compiler/mappings/overrides.example.yml):
```yaml
# overrides.example.yml — only the differences
tables:
declaration:
source: SAD_GEN # this instance calls it SAD_GEN
columns:
office_id: g.GEN_OFFICE # override just this column's expression
declaration_tax_line:
source: SAD_TAXATION # and the tax table SAD_TAXATION
# COD/BSE/RAT/AMT/MOP are documented roots — usually no override needed
```
Pass it with `--overrides` on either `compile` or `build`:
```bash
python -m compiler compile q.sql --overrides compiler/mappings/myinstance.yml
```
The merge is recursive: setting `columns.office_id` for `declaration` replaces
**only that column's expression**, leaving every other column and every other
table exactly as the base mapping defines them.
!!! tip "Use the mock as your worked example"
The default names line up with the bundled mock ASYCUDA World database. Get
your compiled SQL running against the mock first; then write the overrides for
the real instance — you are only changing names, not logic.
## Materialise persistent views with `emit-views`
The compiler prelude is per-query and needs no privileges. If instead you want
**persistent compatibility views** — logical tables physically present as
`CREATE VIEW` objects over the real schema, so *any* tool can query them —
`emit-views` renders the whole mapping as `CREATE OR REPLACE VIEW`:
```bash
python -m compiler emit-views > Sydonia/adapters/asycuda_world_compat.sql
python -m compiler emit-views --overrides compiler/mappings/myinstance.yml > my_compat.sql
```
It emits the schema preamble and one view per logical table (same CTE body,
wrapped as a view):
```sql
-- Generated from compiler/mappings by `python -m compiler emit-views`.
CREATE SCHEMA IF NOT EXISTS asycuda;
SET search_path TO asycuda, public;
CREATE OR REPLACE VIEW ref_country AS
SELECT
c.CTY_COD AS id,
c.CTY_COD AS iso_alpha2,
c.CTY_NAM AS name
FROM UNCTYTAB c
WHERE now()::date BETWEEN c.VALID_FROM AND coalesce(c.VALID_TO, DATE '9999-12-31');
```
This is the same adapter documented at
[running on a real ASYCUDA World](../platform/running-on-real-asycuda.md) — the
CTE prelude and the persistent views are two renderings of **one** mapping, so
they always agree.
## Related
- [Writing logical SQL](logical-sql.md) — the queries these mappings resolve.
- [Running the compiled SQL](running.md) — the sandbox, the mock database, and a
real instance.
- [Running on a real ASYCUDA World](../platform/running-on-real-asycuda.md) — the
deployment / FDW / ETL detail behind the persistent views.
- [Schema overview](../schema/index.md) — the logical layer these expressions map
*from*.
---
# Running the compiled SQL
The compiler produces one standalone genuine Sydonia statement. This page is
about **where you run it**. There are three targets, each with a different job:
```mermaid
flowchart LR
A[sandbox
reconstruction schema] -->|develop| B[logical SQL]
B -->|compile| C[genuine Sydonia SQL]
C -->|prove| D[mock ASYCUDA World
documented physical shape]
C -->|run read-only| E[(real instance)]
style A fill:#0f766e22,stroke:#0f766e
style E fill:#d9770622,stroke:#d97706
```
| Target | Job | Runs |
|--------|-----|------|
| **Sandbox** | develop and verify logical queries | logical SQL |
| **Mock AW DB** | prove the compiled SQL actually runs and matches | genuine SQL |
| **Real instance** | run against your own declarations, read-only | genuine SQL |
## 1 · The reconstruction sandbox — develop logical queries
Load the toolbox's normalised `asycuda` schema as usual, and write **logical
SQL** against it directly. This is where you iterate — the friendly names, the
seeded [end-to-end example](../guides/worked-example.md), fast feedback:
```bash
createdb customs_sandbox
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/schema/seed_reference.sql
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/examples/e2e.sql
```
Everything in the [querying guide](../guides/querying.md) and the
[useful queries](../guides/useful-queries.md) library runs here. When a logical
query gives the answer you want, `compile` it for the targets below.
## 2 · The mock ASYCUDA World database — prove the round-trip
[`Sydonia/adapters/mock_asycuda_world.sql`](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/Sydonia/adapters/mock_asycuda_world.sql)
is a **mock ASYCUDA World physical database**: tables in the documented
wide/denormalised AW shape (`SAD_General_Segment`, `SAD_Item`, `SAD_Tax`, the
`UN*` reference tables), with the exact column names the default mapping targets,
seeded from the toolbox's end-to-end example reshaped into that physical shape.
It is the executable stand-in for a real deployment — no non-public instance
required.
```bash
createdb aw_mock
psql -v ON_ERROR_STOP=1 -d aw_mock -f Sydonia/adapters/mock_asycuda_world.sql
# compile a logical query and run the genuine SQL straight into the mock DB
python -m compiler compile my_query.sql \
| psql -d aw_mock -c 'SET search_path TO aw, public;' -f -
```
!!! success "The round-trip guarantee (verified in CI)"
The **same** logical query returns the **same** results whether run on the
reconstruction sandbox (logical) or compiled and run on the mock physical
database (genuine). Run the friendly query on the sandbox, `compile | psql`
it into the mock, and compare — the numbers match. That is the proof the
abstraction is faithful: **write friendly, run genuine.**
## 3 · A real ASYCUDA World instance — read-only
Two ways to run against a real deployment; both are read-only.
**Run compiled SQL directly.** Compile with your
[per-instance overrides](mapping.md) so the CTEs address the real physical names,
then run the output against the live database:
```bash
python -m compiler compile my_query.sql \
--overrides compiler/mappings/myinstance.yml \
| psql "$CUSTOMS_DB"
```
**Or load persistent views once.** Materialise the mapping as compatibility views
with [`emit-views`](mapping.md) and load them into the real database, so any tool
can address the friendly names without a compile step:
```bash
python -m compiler emit-views --overrides compiler/mappings/myinstance.yml \
| psql "$CUSTOMS_DB"
```
Point **`CUSTOMS_DB`** at the real DSN (ideally a **`SELECT`-only role** on a
**read-replica**), and set `CUSTOMS_SCHEMA` to `asycuda` (or wherever you created
the views).
!!! note "Read-only via the tester — metadata only, never row data"
Validate compiled SQL through the **customs-query-tester** MCP — driven by
the [`customs-query`](../skills/index.md) skill, or the bundled
`skills/customs-query/scripts/test_query.sh`. It returns column names/types,
an aggregate row count and duration — and **never** row data — so it is safe
against a database holding real customs declarations. The compiled SQL and the
views are read-only by construction, so the privacy guarantees hold.
## Deployment, FDW and ETL
Pointing at a real instance raises questions this page doesn't: cross-dialect
deployments (Oracle / MS SQL Server / MySQL), foreign-data-wrapper front-ends
(`oracle_fdw` / `tds_fdw`), the `SELECT`-only role and read-replica setup, and the
**ETL-into-the-reference-model** alternative for bulk analytics and model
training.
All of that is covered in depth in
[**Running on a real ASYCUDA World**](../platform/running-on-real-asycuda.md) —
the compiler is the easiest path to *running the queries*; that page is the
deployment detail behind it.
## The recipes at a glance
| I want to… | Command |
|------------|---------|
| Develop a logical query | run it on the **sandbox** (`customs_sandbox`) |
| Prove the compiled SQL runs | `python -m compiler compile q.sql \| psql -d aw_mock -c 'SET search_path TO aw, public;' -f -` |
| Run against a real instance | `python -m compiler compile q.sql --overrides …myinstance.yml \| psql "$CUSTOMS_DB"` |
| Install persistent views once | `python -m compiler emit-views --overrides …myinstance.yml \| psql "$CUSTOMS_DB"` |
| Validate without reading rows | the [`customs-query`](../skills/index.md) skill / `test_query.sh` (metadata only) |
## Related
- [The mapping](mapping.md) — overrides and `emit-views`.
- [Writing logical SQL](logical-sql.md) — authoring and testing the queries.
- [Running on a real ASYCUDA World](../platform/running-on-real-asycuda.md) — the
FDW / ETL / privacy deep dive.
- [Querying Sydonia](../querying-sydonia/index.md) ·
[joins and gotchas](../querying-sydonia/joins-and-gotchas.md).
---
# Schema overview
One file, `Sydonia/schema/asycuda.sql`, defines **55 tables** across **8
modules** and loads top-to-bottom into a dedicated `asycuda` schema. This page
is the map; each module has its own page, and every column is defined in the
[data dictionary](data-dictionary.md).
!!! tip "This is now the logical layer"
This reconstructed model is the **friendly logical layer** the
[query compiler](../compiler/index.md) maps *from*: you write queries against
the table and column names below, and the compiler rewrites them into genuine
ASYCUDA World SQL you can run. **Write friendly, run genuine.**
The modeled system is **ASYCUDA World (v4)**, grounded in the official public
technical table descriptions — see
[the platform](../platform/index.md) for the version lineage and
[ASYCUDA World in depth](../platform/asycuda-world.md) for how this normalised
model relates to the real (unpublished) physical schema.
## Provenance at a glance
Every `CREATE TABLE` is tagged. A table is either grounded in a cited public
source or honestly marked as a modelling inference — never left ambiguous.
documented · 49
grounded in a cited source (`-- src: `)
inferred · 6
introduced by modelling judgement (`-- inferred`)
The six inferred tables are the RBAC set (`sys_role`, `sys_permission`,
`sys_user_role`, `sys_role_permission`) and the `trader_role` junction — see
[Coverage](../provenance/coverage.md).
## The eight modules
```mermaid
flowchart TD
R[1 · Reference / config
ref_* code tables]
T[1 · Traders & users
trader · sys_user]
M[2 · Manifest & cargo
manifest · bill_of_lading]
D[3 · Declaration — the SAD
declaration · declaration_item]
S[4 · Selectivity & risk
selectivity_result]
A[5 · Accounting
payment · receipt]
X[6 · Transit & suspense
warehouse · transit]
L[7 · Audit / workflow
audit_log]
R --> T
R --> M
R --> D
T --> M
T --> D
M --> D
D --> S
D --> A
D --> X
D --> L
style R fill:#0f766e22,stroke:#0f766e
style D fill:#d9770622,stroke:#d97706
```
| # | Module | Tables | What it captures |
|---|--------|:------:|------------------|
| 1 | [Reference & configuration](reference-config.md) | 26 | Code tables (countries, currencies, HS, taxes, offices…) + traders & users |
| 2 | [Manifest & cargo](manifest.md) | 5 | Carrier manifest, bills of lading, containers, cargo lines |
| 3 | [Declaration (the SAD)](declaration.md) | 9 | The declaration general + item segments, valuation, taxes, documents |
| 4 | [Selectivity & risk](selectivity.md) | 3 | Risk criteria, lane assignment, inspection acts |
| 5 | [Accounting](accounting.md) | 5 | Accounts, payments, receipts, ledger movements, guarantees |
| 6 | [Transit & suspense](transit-suspense.md) | 5 | Warehousing, transit, temporary admission, warehouses |
| 7 | [Audit & workflow](audit.md) | 2 | Cross-cutting audit log and status-history pattern |
*(Module 1 is split into a reference-tables group and a small traders/users group
on the same page; counts sum to 55.)*
## Conventions
The model follows a small, consistent set of rules — the same ones you should
keep when [extending it](../guides/extending.md):
| Rule | Choice |
|------|--------|
| Namespace | everything in schema `asycuda` |
| Naming | `snake_case`; `ref_` for code tables, `sys_` for system/RBAC |
| Primary keys | surrogate `bigint GENERATED ALWAYS AS IDENTITY` |
| Business keys | real codes (HS, office, TIN, receipt no.) kept `UNIQUE NOT NULL` |
| Coded columns | a foreign key to a `ref_*` table (not an inline code+name pair) |
| Money | `numeric(18,4)` |
| Mass / quantity | `numeric(18,3)` |
| Status lifecycles | a `ref_*_status` table + a `*_status_history` child |
| Provenance | `-- src: ` or `-- inferred` on every `CREATE TABLE` |
## See the whole shape
- **[Entity-relationship diagram](erd.md)** — every foreign key, rendered from
the loaded schema.
- **[Data dictionary](data-dictionary.md)** — every table and column with type,
nullability and source.
---
# Reference & configuration
mostly documented
5 inferred (RBAC + trader_role)
The backbone. Before any manifest or declaration exists, customs needs **code
tables** — countries, currencies, tariff codes, tax types, procedure codes — plus
the **traders** who transact and the **users** who operate the system.
*(GOAL §4.1.)*
## The `ref_*` code tables
Each coded value elsewhere in the model is a **foreign key** into one of these,
rather than an inline code+name pair. Most are grounded directly in the ISO/UN/WCO
standard the customs form references.
| Table | Purpose | Standard / source |
|-------|---------|-------------------|
| `ref_country` | Countries (origin, export, destination, nationality) | ISO 3166 · S008, S013 |
| `ref_currency` | Currencies for invoice / freight / insurance | ISO 4217 · S008, S013 |
| `ref_exchange_rate` | Rate to convert invoice currency (SAD box 23) | S013 (`xxRATTAB`) |
| `ref_customs_office` | Customs offices | S008, S013 |
| `ref_location` | Places / ports of loading, unloading, departure | UN/LOCODE · S008 |
| `ref_transport_mode` | Mode of transport (1 Sea … 9 Unknown) | S008 (full code list) |
| `ref_package_type` | Kind of packages | UN/ECE Rec 21 · S008 |
| `ref_container_type` | Container size-type | ISO 6346 · S008 |
| `ref_unit_of_measure` | Supplementary / statistical units (box 41) | S013 (`xxUOMTAB`) |
| `ref_incoterm` | Delivery terms (box 20) | Incoterms · S012, S003 |
| `ref_hs_tariff` | Commodity codes, self-referential hierarchy (box 33) | Harmonized System · S003, S008 |
| `ref_cpc_regime` | Customs Procedure Codes / regimes (box 37) | S003 (`xxCP*TAB`) |
| `ref_tax_type` | Duty / tax / fee types (box 47) | S003 (`xxTAXTAB`) |
| `ref_tax_rate` | Applicable rate per tax / commodity / origin | S013 (`xxRULTAB`) |
| `ref_document_type` | Attached / supporting document types (box 44) | S003, S008 |
| `ref_exemption_code` | Additional National Codes granting relief (box 37) | S003 |
| `ref_declaration_type` | Declaration type codes (box 1: IM4, EX1…) | S003 |
| `ref_declaration_status` | Declaration lifecycle statuses | S002 |
| `ref_manifest_status` | Manifest lifecycle statuses | S015 |
| `ref_bl_nature` | Transport-document nature (imports/exports/transit) | S008 |
| `ref_selectivity_lane` | Green / yellow / red / blue lanes | S002, S005 |
| `ref_warehouse` | Bonded / customs warehouses (box 49) | S003, S013 |
!!! note "The reference-table pattern"
Code tables carry a surrogate `id` PK **and** the real business `code`
(`UNIQUE NOT NULL`). Rate-like tables (`ref_exchange_rate`, `ref_tax_rate`)
add `valid_from` / `valid_to`; the rest use an `is_active` boolean. This mirrors
the official `VALID_FROM` / `VALID_TO` temporal pattern seen in the UNCTAD
reference tables, simplified for a reference model.
## Traders and economic operators
| Table | Purpose | Provenance |
|-------|---------|------------|
| `trader` | Economic operators keyed by TIN — importer, exporter, consignee, declarant, broker, carrier | documented S003, S008 |
| `trader_role` | The role(s) a trader may act in | inferred normalisation |
A single `trader` can appear as exporter on one declaration and consignee on
another; `trader_role` normalises the roles rather than duplicating the party.
## System users and access control
| Table | Purpose | Provenance |
|-------|---------|------------|
| `sys_user` | People who log in — customs staff, brokers, traders | documented S002 |
| `sys_role`, `sys_permission`, `sys_user_role`, `sys_role_permission` | Role-based access control | inferred |
ASYCUDA World has role-based menus, but the reference docs publish no user/role
schema — so the RBAC tables are an honest modelling inference (see
[Coverage](../provenance/coverage.md)).
## Resolving a coded column
Because coded columns are foreign keys, "human-readable" queries join to the
`ref_*` table:
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number,
ty.code AS decl_type,
off.name AS office,
cur.iso_code AS currency
FROM declaration d
JOIN ref_declaration_type ty ON ty.id = d.declaration_type_id
JOIN ref_customs_office off ON off.id = d.office_id
JOIN ref_currency cur ON cur.id = d.currency_id;
```
See every column in the [data dictionary](data-dictionary.md#module-reference-configuration-goal-41).
---
# Manifest & cargo
documented — grounded in the official
UNCTAD manifest XML spec (S008) and national manifest manuals (S006, S007, S010,
S011) and the official `GEN_TAB` / `BOL_TAB` / `CTN_TAB` descriptions (S015).
What the **carrier** declares is on board. This is the upstream document the
importer's declaration later writes off against. *(GOAL §4.2.)*
## Tables
| Table | Purpose |
|-------|---------|
| `manifest` | General segment: carrier, voyage/flight, ports, dates, totals, office |
| `bill_of_lading` | A transport document (B/L / AWB). Master ⇄ house via self-reference |
| `container` | Physical containers on a B/L — ISO 6346 type, seals, weights, reefer |
| `manifest_cargo_item` | Goods/commodity lines within a B/L |
| `manifest_status_history` | Manifest lifecycle transitions |
```mermaid
erDiagram
manifest ||--o{ bill_of_lading : "carries"
bill_of_lading ||--o{ bill_of_lading : "master ⇄ house"
bill_of_lading ||--o{ container : "stuffed in"
bill_of_lading ||--o{ manifest_cargo_item : "goods lines"
container ||--o{ manifest_cargo_item : "packed as"
manifest ||--o{ manifest_status_history : "lifecycle"
```
## Master vs house — degroupage
A **master** bill of lading covers a full container moving carrier-to-carrier; a
**house** bill of lading is one consignee's consignment inside it. Consolidation
(**degroupage**) is modelled with a self-reference:
```sql
-- a house B/L points at its master
bill_of_lading.master_bl_id → bill_of_lading.id
bill_of_lading.is_master boolean
```
## Key columns — `manifest`
| Column | Type | Meaning |
|--------|------|---------|
| `office_id` | FK | Customs office of arrival |
| `manifest_year`, `registration_number` | int | Registration serial |
| `voyage_number`, `identity_of_transport` | text | Voyage / vessel identity |
| `transport_mode_id`, `nationality_id` | FK | Mode + carrier nationality |
| `carrier_id`, `shipping_agent_id` | FK → `trader` | Parties |
| `place_departure_id`, `place_destination_id` | FK → `ref_location` | Ports |
| `date_of_departure`, `date_of_arrival` | date | Voyage dates |
| `total_bols`, `total_packages`, `total_containers`, `total_gross_mass` | num | Declared totals |
| `status_id` | FK | Current lifecycle status |
## Example — a manifest with its consignments
```sql
SET search_path TO asycuda, public;
SELECT m.voyage_number,
bl.bl_reference,
nat.name AS bl_nature,
bl.number_of_packages,
bl.gross_mass,
count(ci.id) AS goods_lines
FROM manifest m
JOIN bill_of_lading bl ON bl.manifest_id = m.id
JOIN ref_bl_nature nat ON nat.id = bl.bl_nature_id
LEFT JOIN manifest_cargo_item ci ON ci.bl_id = bl.id
WHERE m.voyage_number = 'V2026-042'
GROUP BY m.voyage_number, bl.bl_reference, nat.name,
bl.number_of_packages, bl.gross_mass;
```
Full columns in the [data dictionary](data-dictionary.md#module-manifest-cargo-goal-42).
!!! info "Known gap"
The manifest XML also defines a **vehicle sub-segment** (chassis/VIN/engine for
RoRo cargo). It is documented in the research log but not modelled as a table,
as it sits off the end-to-end path. Tracked in [Coverage](../provenance/coverage.md).
---
# Declaration — the SAD
documented — the **Single
Administrative Document**, grounded box-by-box in S003 (FSM declaration guide),
S001 (SAD overview) and the official `SAD_General_Segment` / `SAD_Item` /
`SAD_Tax` descriptions (S014).
The core of the model. The **SAD** is the internationally standardised customs
declaration — one **general segment** per consignment plus repeating **item
segments**, one per commodity line. *(GOAL §4.3.)*
## Tables
| Table | Purpose |
|-------|---------|
| `declaration` | General segment — parties, regime, transport, invoice totals, status, lane |
| `declaration_item` | Item segment — HS code, origin, mass, procedure, values (the tax base) |
| `valuation_note` | Declaration-level value build-up → total CIF |
| `item_value_note` | Freight/insurance apportioned per item → item CIF |
| `declaration_tax_line` | Per item, per tax: base · rate · amount · mode of payment |
| `declaration_attached_document` | Invoice, licence, permit, certificate (box 44) |
| `declaration_previous_document` | Write-off against a manifest B/L or prior declaration (box 40) |
| `declaration_status_history` | Lifecycle transitions (stored → … → released) |
```mermaid
erDiagram
declaration ||--o{ declaration_item : "1..n items"
declaration ||--o| valuation_note : "value build-up"
declaration_item ||--o| item_value_note : "apportioned CIF"
declaration_item ||--o{ declaration_tax_line : "per-tax"
declaration ||--o{ declaration_attached_document : "box 44"
declaration ||--o{ declaration_previous_document : "box 40 write-off"
declaration ||--o{ declaration_status_history : "lifecycle"
declaration_item ||--o{ declaration_attached_document : "item-level docs"
```
## The general segment — `declaration`
One row per consignment. Selected columns (see the
[data dictionary](data-dictionary.md#module-declaration-the-sad-goal-43) for all):
| Column | SAD box | Meaning |
|--------|:------:|---------|
| `office_id` | 29 | Office of entry |
| `declaration_type_id` | 1 | Model/type (IM4, EX1…) |
| `cpc_id` | 37 | Customs Procedure Code / regime |
| `registration_serial`, `registration_number`, `registration_date` | A | Registration identity |
| `exporter_id`, `consignee_id`, `declarant_id`, `financial_id` | 2/8/14/9 | Parties |
| `country_export_id`, `country_origin_id`, `country_destination_id` | 15/34/17 | Countries |
| `incoterm_id`, `delivery_place` | 20 | Delivery terms |
| `total_invoice_amount`, `currency_id`, `exchange_rate` | 22/23 | Invoice value |
| `total_freight`, `total_insurance`, `total_cif_value` | — | Value totals |
| `selectivity_lane_id` | — | Assigned lane (GREEN/YELLOW/RED/BLUE) |
| `status_id` | — | Current lifecycle status |
| `manifest_id` | — | Links to the arriving manifest |
## The item segment — `declaration_item`
One row per commodity line (SAD boxes 31–49). The **customs value** on each item
is the tax base:
| Column | SAD box | Meaning |
|--------|:------:|---------|
| `item_number` | 32 | Line number |
| `hs_id`, `hs_code` | 33 | Commodity code |
| `country_origin_id` | 34 | Origin |
| `cpc_id`, `national_procedure` | 37 | Procedure |
| `number_of_packages`, `package_type_id`, `marks_and_numbers` | 31 | Packaging |
| `gross_mass`, `net_mass` | 35/38 | Mass |
| `supplementary_qty`, `supplementary_uom_id` | 41 | Statistical quantity |
| `item_price`, `valuation_method_code` | 42/43 | Price & method |
| `statistical_value`, `customs_value` | 46/— | **The tax base** (CIF) |
## Valuation — building the tax base
Invoices are usually **FOB** (goods only), but duty is charged on **CIF**. The
valuation note builds the value up, then apportions shared freight/insurance down
to each item by value share:
```text
valuation_note : invoice FOB + freight + insurance + other = total CIF
item_value_note: item FOB + apportioned freight + apportioned insurance = item CIF
```
In the worked example, $3,000 freight + $300 insurance on a $60,000 FOB shipment
split 2:1, giving item CIFs of **$42,200** and **$21,100**.
## Taxes — `declaration_tax_line`
One row per item, per applicable tax. Because taxes cascade (VAT is charged on
*customs value + import duty*), the base is stored per line rather than derived:
| Column | `SAD_Tax` | Meaning |
|--------|:---------:|---------|
| `tax_type_id` | `COD` | Which tax (import duty, VAT, excise, fee) |
| `tax_base` | `BSE` | Amount the rate applies to |
| `rate_percent` | `RAT` | Ad-valorem rate |
| `specific_amount` | — | Specific (per-unit) component |
| `tax_amount` | `AMT` | Calculated amount |
| `mode_of_payment` | `MOP` | Cash / account / … |
| `is_manual` | `TYP` | Manually entered vs automatically calculated |
## Documents
- `declaration_attached_document` — supporting documents at header **or** item
level (invoice `380`, bill of lading `705`, licence `911`…), SAD box 44.
- `declaration_previous_document` — SAD box 40: writes each item off against the
**manifest bill of lading** (`bl_id`) or a **previous declaration**
(`prev_declaration_id`), with packages/mass written off.
## Example — assemble a declaration
```sql
SET search_path TO asycuda, public;
SELECT di.item_number,
di.hs_code,
di.customs_value,
sum(tl.tax_amount) AS taxes
FROM declaration d
JOIN declaration_item di ON di.declaration_id = d.id
LEFT JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
WHERE d.trader_reference = 'REF-2026-0001'
GROUP BY di.item_number, di.hs_code, di.customs_value
ORDER BY di.item_number;
```
See the [worked example](../guides/worked-example.md) for the full flow, and the
[querying guide](../guides/querying.md) for more join paths.
---
# Selectivity & risk
documented — lanes and inspection
grounded in S002 and S005; the risk-criteria catalogue in the official
`SEL_*_PARAM_TAB` selectivity tables (S014).
Customs cannot inspect every consignment. **Selectivity** routes each declaration
into a risk **lane**, balancing facilitation against control. *(GOAL §4.6.)*
## The four lanes
| Lane | Requires exam | Meaning |
|------|:-------------:|---------|
| :material-circle:{ style="color:#16a34a" } **GREEN** | no | Automatic release; customs reserves the right to examine |
| :material-circle:{ style="color:#ca8a04" } **YELLOW** | yes | Documentary check by an assigned officer |
| :material-circle:{ style="color:#dc2626" } **RED** | yes | Physical examination of the goods |
| :material-circle:{ style="color:#2563eb" } **BLUE** | no | Released now, selected for post-clearance audit |
## Tables
| Table | Purpose |
|-------|---------|
| `ref_selectivity_lane` | The lane catalogue (defined in [Reference & config](reference-config.md)) |
| `risk_criterion` | Rules that target a lane (e.g. high-risk HS chapter, first-time importer) |
| `selectivity_result` | The lane a specific declaration was routed to, when and by which criterion |
| `inspection_act` | The officer's examination record for yellow/red declarations |
```mermaid
erDiagram
declaration ||--o{ selectivity_result : "routed to a lane"
ref_selectivity_lane ||--o{ selectivity_result : "which lane"
risk_criterion ||--o{ selectivity_result : "why"
ref_selectivity_lane ||--o{ risk_criterion : "targets"
declaration ||--o{ inspection_act : "examined by"
```
The declaration also carries its current lane inline on
`declaration.selectivity_lane_id` for fast filtering, while `selectivity_result`
preserves the full routing history and reason.
## Example — inspection outcomes by lane
```sql
SET search_path TO asycuda, public;
SELECT lane.code AS lane,
count(*) AS declarations,
count(ia.id) AS inspected,
count(*) FILTER (WHERE ia.result = 'conform') AS conform
FROM declaration d
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id
LEFT JOIN inspection_act ia ON ia.declaration_id = d.id
GROUP BY lane.code
ORDER BY lane.code;
```
Full columns in the [data dictionary](data-dictionary.md#module-selectivity-risk-goal-46).
---
# Accounting
documented — grounded in the official
accounting tables (S016: receipts, `TAX_TAB`, account transactions in/out) and
S003 boxes 48/B.
Once a declaration is assessed, the declarant **pays**, a **receipt** is issued,
and the money is recorded against an **account** ledger. *(GOAL §4.4.)*
## Tables
| Table | Purpose |
|-------|---------|
| `account` | Trader deferred-payment / credit / prepayment / guarantee account (box 48) |
| `payment` | Settlement of an assessed declaration — cash or against an account (box B) |
| `receipt` | The receipt issued on payment |
| `account_movement` | Ledger debit/credit against an account, with running balance |
| `guarantee` | Security / bond backing suspense regimes (box 52) |
```mermaid
erDiagram
declaration ||--o{ payment : "settles"
payment ||--o| receipt : "issues"
account ||--o{ payment : "charged to"
account ||--o{ account_movement : "ledger"
payment ||--o{ account_movement : "posts"
trader ||--o{ account : "holds"
trader ||--o{ guarantee : "lodges"
```
Payment mode is flexible: a `NULL` `account_id` on `payment` means **cash**;
otherwise it draws on the trader's `account`, and an `account_movement` records
the debit and the resulting balance.
## Example — assessed vs paid reconciliation
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number,
assessed.total_tax,
p.amount AS paid,
r.receipt_number
FROM declaration d
JOIN LATERAL (
SELECT sum(tl.tax_amount) AS total_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
WHERE di.declaration_id = d.id
) assessed ON true
LEFT JOIN payment p ON p.declaration_id = d.id
LEFT JOIN receipt r ON r.payment_id = p.id
WHERE d.trader_reference = 'REF-2026-0001';
```
A healthy declaration has `total_tax = paid = receipt.total_amount`. Full columns
in the [data dictionary](data-dictionary.md#module-accounting-goal-44).
---
# Transit & suspense
documented — grounded in the
Suspense Declarations manual (S019), the official warehouse/transit tables
(`SUS_WH_IN`, `MAN_TRANSIT_TAB`; S014/S015) and S003 boxes 49–53.
Not all goods are cleared for home use on arrival — duty can be **suspended**
while goods are warehoused, moved under control, or temporarily imported.
*(GOAL §4.5.)*
## The suspense regimes
| Regime | What happens | Tables |
|--------|--------------|--------|
| **Warehousing** | Goods stored in a bonded warehouse until later entry | `ref_warehouse`, `warehouse_entry`, `warehouse_exit` |
| **Transit** | Goods moved under customs control between offices | `transit_declaration` |
| **Temporary admission** | Imported for a limited time, then re-exported | `temporary_admission` |
All three lean on `guarantee` (from [Accounting](accounting.md)) to secure the
suspended duty.
```mermaid
erDiagram
declaration ||--o| transit_declaration : "transit extension"
declaration ||--o{ warehouse_entry : "placed in warehouse"
warehouse_entry ||--o{ warehouse_exit : "removed (write-off)"
ref_warehouse ||--o{ warehouse_entry : "stored at"
declaration ||--o| temporary_admission : "TA regime"
guarantee ||--o{ transit_declaration : "secures"
guarantee ||--o{ temporary_admission : "secures"
```
## Key details
- **`transit_declaration`** extends a declaration with the principal (box 50),
the offices of departure / transit / destination (boxes 51/53), the securing
`guarantee` (box 52), itinerary, seals and a time limit.
- **`warehouse_entry` / `warehouse_exit`** track goods into and out of a
`ref_warehouse`; the exit is the ex-warehouse write-off.
- **`temporary_admission`** records the time limit and links to the eventual
re-export declaration (`re_export_declaration_id`).
## Example — goods currently in a warehouse
```sql
SET search_path TO asycuda, public;
SELECT w.code AS warehouse,
we.entry_date,
we.packages,
we.gross_mass
FROM warehouse_entry we
JOIN ref_warehouse w ON w.id = we.warehouse_id
LEFT JOIN warehouse_exit wx ON wx.warehouse_entry_id = we.id
WHERE wx.id IS NULL -- not yet removed
ORDER BY we.entry_date;
```
Full columns in the [data dictionary](data-dictionary.md#module-transit-suspense-goal-45).
---
# Audit & workflow
documented (audit log · S013 `LogTable`)
history shape inferred
Cross-cutting bookkeeping: **who did what, when**, and how each document moved
through its lifecycle. *(GOAL §4.7.)*
## The audit log
`audit_log` is a single, generic who/what/when trail. Rather than a foreign key
per entity, it stores the affected entity by name and surrogate id, so any table
can be audited uniformly:
| Column | Meaning |
|--------|---------|
| `entity_name` | Table / document type affected (e.g. `declaration`) |
| `entity_id` | Surrogate id of the affected row |
| `action` | `insert` / `update` / `status_change` / `print` … |
| `actor_id` | The `sys_user` responsible |
| `occurred_at` | Timestamp (`timestamptz`, defaults to `now()`) |
| `detail` | Free-text description |
## The status-history pattern
Lifecycles are modelled consistently across the schema: a `ref_*_status` catalogue
plus a `*_status_history` child that records each transition. Two instances exist:
| History table | Tracks | Status catalogue |
|---------------|--------|------------------|
| `declaration_status_history` | stored → registered → assessed → paid → released | `ref_declaration_status` |
| `manifest_status_history` | stored → registered → amended → closed | `ref_manifest_status` |
```mermaid
erDiagram
declaration ||--o{ declaration_status_history : "transitions"
ref_declaration_status ||--o{ declaration_status_history : "status"
sys_user ||--o{ declaration_status_history : "changed by"
sys_user ||--o{ audit_log : "acted"
```
This keeps the **current** status on the parent (`declaration.status_id`) for
fast filtering while preserving the **full trail** in the history child.
## Example — the lifecycle trail of a declaration
```sql
SET search_path TO asycuda, public;
SELECT st.sort_order,
st.code,
h.changed_at,
u.login_name AS changed_by,
h.note
FROM declaration_status_history h
JOIN ref_declaration_status st ON st.id = h.status_id
LEFT JOIN sys_user u ON u.id = h.changed_by
JOIN declaration d ON d.id = h.declaration_id
WHERE d.trader_reference = 'REF-2026-0001'
ORDER BY h.changed_at;
```
Full columns in the [data dictionary](data-dictionary.md#module-audit-workflow-cross-cutting-goal-47).
---
# Entity-relationship diagram
This diagram is generated from the **loaded schema's foreign keys**, so it always
matches `Sydonia/schema/asycuda.sql`. The `ref_*` / `sys_*` tables are the
code/config backbone; the manifest and declaration clusters are the operational
core. Attributes are abbreviated to primary/business keys for legibility — see
the [data dictionary](data-dictionary.md) for the full column list.
```mermaid
erDiagram
account {
bigint id
character_varying_20_ account_number
}
account_movement {
bigint id
}
audit_log {
bigint id
}
bill_of_lading {
bigint id
integer line_number
character_varying_35_ bl_reference
character_varying_3_ bl_type_code
}
container {
bigint id
character_varying_17_ ctn_reference
character_varying_10_ dangerous_goods_code
}
declaration {
bigint id
integer registration_number
character_varying_35_ trader_reference
character_varying_20_ assessment_number
}
declaration_attached_document {
bigint id
character_varying_60_ document_reference
}
declaration_item {
bigint id
smallint item_number
character_varying_12_ hs_code
character_varying_3_ preference_code
character_varying_17_ container_reference
character_varying_3_ valuation_method_code
}
declaration_previous_document {
bigint id
character_varying_60_ reference
}
declaration_status_history {
bigint id
}
declaration_tax_line {
bigint id
}
guarantee {
bigint id
character_varying_30_ reference
}
inspection_act {
bigint id
}
item_value_note {
bigint id
}
manifest {
bigint id
integer registration_number
character_varying_17_ voyage_number
}
manifest_cargo_item {
bigint id
integer line_number
character_varying_6_ hs_code
}
manifest_status_history {
bigint id
}
payment {
bigint id
}
receipt {
bigint id
character_varying_20_ receipt_number
}
ref_bl_nature {
bigint id
character_varying_2_ code
}
ref_container_type {
bigint id
character_varying_4_ code
}
ref_country {
bigint id
character_varying_2_ iso_alpha2
character_varying_3_ iso_alpha3
character_varying_3_ numeric_code
}
ref_cpc_regime {
bigint id
character_varying_7_ cpc_code
}
ref_currency {
bigint id
character_varying_3_ iso_code
character_varying_3_ numeric_code
}
ref_customs_office {
bigint id
character_varying_5_ office_code
}
ref_declaration_status {
bigint id
character_varying_15_ code
}
ref_declaration_type {
bigint id
character_varying_4_ code
}
ref_document_type {
bigint id
character_varying_6_ code
}
ref_exchange_rate {
bigint id
}
ref_exemption_code {
bigint id
character_varying_6_ code
}
ref_hs_tariff {
bigint id
character_varying_12_ hs_code
}
ref_incoterm {
bigint id
character_varying_3_ code
}
ref_location {
bigint id
character_varying_5_ unlocode
}
ref_manifest_status {
bigint id
character_varying_15_ code
}
ref_package_type {
bigint id
character_varying_2_ code
}
ref_selectivity_lane {
bigint id
character_varying_6_ code
}
ref_tax_rate {
bigint id
}
ref_tax_type {
bigint id
character_varying_6_ code
}
ref_transport_mode {
bigint id
character_varying_3_ code
}
ref_unit_of_measure {
bigint id
character_varying_6_ code
}
ref_warehouse {
bigint id
character_varying_10_ code
}
risk_criterion {
bigint id
character_varying_20_ code
}
selectivity_result {
bigint id
}
sys_permission {
bigint id
character_varying_40_ code
}
sys_role {
bigint id
character_varying_30_ code
}
sys_role_permission {
bigint role_id
bigint permission_id
}
sys_user {
bigint id
character_varying_40_ login_name
}
sys_user_role {
bigint user_id
bigint role_id
}
temporary_admission {
bigint id
}
trader {
bigint id
character_varying_20_ tin
}
trader_role {
bigint id
}
transit_declaration {
bigint id
}
valuation_note {
bigint id
}
warehouse_entry {
bigint id
}
warehouse_exit {
bigint id
}
account ||--o{ account_movement : ""
account ||--o{ payment : ""
bill_of_lading ||--o{ bill_of_lading : "self"
bill_of_lading ||--o{ container : ""
bill_of_lading ||--o{ declaration_previous_document : ""
bill_of_lading ||--o{ manifest_cargo_item : ""
container ||--o{ manifest_cargo_item : ""
declaration ||--o{ declaration_attached_document : ""
declaration ||--o{ declaration_item : ""
declaration ||--o{ declaration_previous_document : ""
declaration ||--o{ declaration_status_history : ""
declaration ||--o{ inspection_act : ""
declaration ||--o{ payment : ""
declaration ||--o{ selectivity_result : ""
declaration ||--o{ temporary_admission : ""
declaration ||--o{ transit_declaration : ""
declaration ||--o{ valuation_note : ""
declaration ||--o{ warehouse_entry : ""
declaration ||--o{ warehouse_exit : ""
declaration_item ||--o{ declaration_attached_document : ""
declaration_item ||--o{ declaration_previous_document : ""
declaration_item ||--o{ declaration_tax_line : ""
declaration_item ||--o{ item_value_note : ""
guarantee ||--o{ temporary_admission : ""
guarantee ||--o{ transit_declaration : ""
manifest ||--o{ bill_of_lading : ""
manifest ||--o{ declaration : ""
manifest ||--o{ manifest_status_history : ""
payment ||--o{ account_movement : ""
payment ||--o{ receipt : ""
ref_bl_nature ||--o{ bill_of_lading : ""
ref_container_type ||--o{ container : ""
ref_country ||--o{ declaration : ""
ref_country ||--o{ declaration_item : ""
ref_country ||--o{ manifest : ""
ref_country ||--o{ ref_customs_office : ""
ref_country ||--o{ ref_location : ""
ref_country ||--o{ ref_tax_rate : ""
ref_country ||--o{ trader : ""
ref_cpc_regime ||--o{ declaration : ""
ref_cpc_regime ||--o{ declaration_item : ""
ref_cpc_regime ||--o{ ref_tax_rate : ""
ref_currency ||--o{ account : ""
ref_currency ||--o{ bill_of_lading : ""
ref_currency ||--o{ declaration : ""
ref_currency ||--o{ guarantee : ""
ref_currency ||--o{ payment : ""
ref_currency ||--o{ ref_exchange_rate : ""
ref_currency ||--o{ valuation_note : ""
ref_customs_office ||--o{ declaration : ""
ref_customs_office ||--o{ manifest : ""
ref_customs_office ||--o{ ref_warehouse : ""
ref_customs_office ||--o{ sys_user : ""
ref_customs_office ||--o{ transit_declaration : ""
ref_declaration_status ||--o{ declaration : ""
ref_declaration_status ||--o{ declaration_status_history : ""
ref_declaration_type ||--o{ declaration : ""
ref_document_type ||--o{ declaration_attached_document : ""
ref_exemption_code ||--o{ declaration_item : ""
ref_hs_tariff ||--o{ declaration_item : ""
ref_hs_tariff ||--o{ ref_hs_tariff : "self"
ref_hs_tariff ||--o{ ref_tax_rate : ""
ref_incoterm ||--o{ declaration : ""
ref_location ||--o{ bill_of_lading : ""
ref_location ||--o{ declaration : ""
ref_location ||--o{ manifest : ""
ref_manifest_status ||--o{ manifest : ""
ref_manifest_status ||--o{ manifest_status_history : ""
ref_package_type ||--o{ bill_of_lading : ""
ref_package_type ||--o{ declaration_item : ""
ref_package_type ||--o{ manifest_cargo_item : ""
ref_selectivity_lane ||--o{ declaration : ""
ref_selectivity_lane ||--o{ risk_criterion : ""
ref_selectivity_lane ||--o{ selectivity_result : ""
ref_tax_type ||--o{ declaration_tax_line : ""
ref_tax_type ||--o{ ref_tax_rate : ""
ref_transport_mode ||--o{ declaration : ""
ref_transport_mode ||--o{ manifest : ""
ref_unit_of_measure ||--o{ declaration_item : ""
ref_unit_of_measure ||--o{ ref_hs_tariff : ""
ref_warehouse ||--o{ declaration_item : ""
ref_warehouse ||--o{ warehouse_entry : ""
risk_criterion ||--o{ selectivity_result : ""
sys_permission ||--o{ sys_role_permission : ""
sys_role ||--o{ sys_role_permission : ""
sys_role ||--o{ sys_user_role : ""
sys_user ||--o{ audit_log : ""
sys_user ||--o{ declaration : ""
sys_user ||--o{ declaration_status_history : ""
sys_user ||--o{ inspection_act : ""
sys_user ||--o{ manifest_status_history : ""
sys_user ||--o{ payment : ""
sys_user ||--o{ selectivity_result : ""
sys_user ||--o{ sys_user_role : ""
trader ||--o{ account : ""
trader ||--o{ bill_of_lading : ""
trader ||--o{ declaration : ""
trader ||--o{ guarantee : ""
trader ||--o{ manifest : ""
trader ||--o{ ref_warehouse : ""
trader ||--o{ sys_user : ""
trader ||--o{ trader_role : ""
trader ||--o{ transit_declaration : ""
warehouse_entry ||--o{ warehouse_exit : ""
```
---
# Data dictionary
Every table and column in the model, grouped by module. Each entry lists the
column's type, nullability, and — at the table level — its provenance
(`-- src: ` or `-- inferred`). This page is generated from the live
PostgreSQL catalog after loading `Sydonia/schema/asycuda.sql`, so it is exact.
!!! tip "Looking for one table?"
Use the search box (press /) or your browser's find — every table
name is a heading anchor. For the visual shape, see the
[entity-relationship diagram](erd.md).
## Module: REFERENCE / CONFIGURATION (GOAL §4.1)
### `ref_country`
*Source:* `src: S013, S008`
*Purpose:* ISO 3166 countries; used for origin, export, destination, nationality (S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| iso_alpha2 | character varying(2) | NOT NULL | |
| iso_alpha3 | character varying(3) | nullable | |
| numeric_code | character varying(3) | nullable | |
| name | character varying(100) | NOT NULL | |
| is_active | boolean | NOT NULL | |
### `ref_currency`
*Source:* `src: S013, S008`
*Purpose:* ISO 4217 currencies for invoice / freight / insurance values (S008, S003 box 22).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| iso_code | character varying(3) | NOT NULL | |
| numeric_code | character varying(3) | nullable | |
| name | character varying(80) | NOT NULL | |
| minor_units | smallint | NOT NULL | |
| is_active | boolean | NOT NULL | |
### `ref_exchange_rate`
*Source:* `src: S013`
*Purpose:* Exchange rate applied to convert invoice currency (SAD box 23); table shape inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| currency_id | bigint | NOT NULL | |
| rate | numeric(18,6) | NOT NULL | |
| valid_from | date | NOT NULL | |
| valid_to | date | nullable | |
### `ref_customs_office`
*Source:* `src: S013, S008`
*Purpose:* Customs offices (S008 customs_office_code AN5; S003 office of entry box 29).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| office_code | character varying(5) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| country_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `ref_location`
*Source:* `src: S013, S008`
*Purpose:* UN/LOCODE places/ports of loading, unloading, departure, destination (S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| unlocode | character varying(5) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| country_id | bigint | nullable | |
| is_port | boolean | NOT NULL | |
### `ref_transport_mode`
*Source:* `src: S013, S008`
*Purpose:* Mode of transport 1=Sea 2=Rail 3=Road 4=Air 5=Postal 6=Multimodal 7=Fixed 8=Inland waterways 9=Unknown (S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(3) | NOT NULL | |
| name | character varying(40) | NOT NULL | |
### `ref_package_type`
*Source:* `src: S013, S008`
*Purpose:* Kind-of-packages codes, UN/ECE Rec 21 alpha-2 (S008 package_type_code; S003 box 31).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(2) | NOT NULL | |
| name | character varying(80) | NOT NULL | |
### `ref_container_type`
*Source:* `src: S013, S008`
*Purpose:* Container size-type per ISO 6346:1995 (S008 type_of_container).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(4) | NOT NULL | |
| name | character varying(80) | NOT NULL | |
### `ref_unit_of_measure`
*Source:* `src: S013`
*Purpose:* Units for supplementary quantity / statistical units (SAD box 41, S003); values inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(6) | NOT NULL | |
| name | character varying(60) | NOT NULL | |
### `ref_incoterm`
*Source:* `src: S013, S003, S012`
*Purpose:* Incoterms delivery-terms codes (SAD box 20, S003; S012).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(3) | NOT NULL | |
| name | character varying(60) | NOT NULL | |
| edition | character varying(9) | nullable | |
### `ref_hs_tariff`
*Source:* `src: S013, S003`
*Purpose:* Harmonized System commodity codes with self-referential hierarchy (SAD box 33, S003; S008 HS 6-digit).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| hs_code | character varying(12) | NOT NULL | |
| parent_id | bigint | nullable | |
| description | character varying(400) | NOT NULL | |
| uom_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `ref_cpc_regime`
*Source:* `src: S013, S003`
*Purpose:* Customs Procedure Codes / regimes (SAD box 37, S003): requested(2)+previous(2) extended code.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| cpc_code | character varying(7) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| regime_group | character varying(30) | nullable | |
| is_suspense | boolean | NOT NULL | |
| is_active | boolean | NOT NULL | |
### `ref_tax_type`
*Source:* `src: S013, S003`
*Purpose:* Duty/tax/fee types calculated per item (SAD box 47, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(6) | NOT NULL | |
| name | character varying(80) | NOT NULL | |
| is_ad_valorem | boolean | NOT NULL | |
### `ref_tax_rate`
*Source:* `src: S013`
*Purpose:* Applicable rate per tax type / commodity / regime / origin (SAD box 47 implies rates); table shape inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| tax_type_id | bigint | NOT NULL | |
| hs_id | bigint | nullable | |
| cpc_id | bigint | nullable | |
| origin_id | bigint | nullable | |
| rate_percent | numeric(9,4) | nullable | |
| specific_amount | numeric(18,4) | nullable | |
| valid_from | date | NOT NULL | |
| valid_to | date | nullable | |
### `ref_document_type`
*Source:* `src: S013, S003, S008`
*Purpose:* Attached/supporting document types (SAD box 44, S003; S008 attached_document_code).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(6) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
### `ref_exemption_code`
*Source:* `src: S013, S003`
*Purpose:* Additional National Codes granting special duty/tax treatment (SAD box 37 national procedure, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(6) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| description | character varying(300) | nullable | |
### `ref_declaration_type`
*Source:* `src: S013, S003`
*Purpose:* Declaration type codes (SAD box 1, S003): first letters model, digit = extended procedure.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(4) | NOT NULL | |
| name | character varying(80) | NOT NULL | |
| direction | character varying(10) | NOT NULL | |
### `ref_declaration_status`
*Source:* `src: S014, S002`
*Purpose:* Declaration lifecycle: stored->registered->assessed->paid->released (+queried/cancelled) (S002).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(15) | NOT NULL | |
| name | character varying(60) | NOT NULL | |
| sort_order | smallint | NOT NULL | |
### `ref_manifest_status`
*Source:* `src: S015`
*Purpose:* Manifest lifecycle (stored/registered/amended/closed); inferred from S006/S011 workflow.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(15) | NOT NULL | |
| name | character varying(60) | NOT NULL | |
| sort_order | smallint | NOT NULL | |
### `ref_bl_nature`
*Source:* `src: S013, S008`
*Purpose:* Transport-document nature codes (S008 Bol_nature).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(2) | NOT NULL | |
| name | character varying(60) | NOT NULL | |
### `ref_selectivity_lane`
*Source:* `src: S014, S002, S005`
*Purpose:* Selectivity lanes green/yellow/red/blue (S002, S005).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(6) | NOT NULL | |
| name | character varying(40) | NOT NULL | |
| requires_exam | boolean | NOT NULL | |
| description | character varying(200) | nullable | |
## Module: TRADERS & SYSTEM USERS (GOAL §4.1)
### `trader`
*Source:* `src: S013, S003, S008`
*Purpose:* Economic operators: importer/exporter/consignee/declarant/broker/carrier, keyed by TIN (S003, S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| tin | character varying(20) | NOT NULL | |
| name | character varying(140) | NOT NULL | |
| address | character varying(200) | nullable | |
| country_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `trader_role`
*Source:* `inferred`
*Purpose:* Roles a trader may act in; inferred normalisation of the single trader/economic-operator concept.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| trader_id | bigint | NOT NULL | |
| role | character varying(20) | NOT NULL | |
### `sys_user`
*Source:* `src: S002`
*Purpose:* System users (customs staff, brokers, traders); registration required (S002).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| login_name | character varying(40) | NOT NULL | |
| full_name | character varying(120) | NOT NULL | |
| trader_id | bigint | nullable | |
| office_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `sys_role`
*Source:* `inferred`
*Purpose:* RBAC roles; inferred (ASYCUDA World has role-based menus but no public schema).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(30) | NOT NULL | |
| name | character varying(80) | NOT NULL | |
### `sys_permission`
*Source:* `inferred`
*Purpose:* RBAC permissions; inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(40) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
### `sys_user_role`
*Source:* `inferred`
| Column | Type | Null | Notes |
|--------|------|------|-------|
| user_id | bigint | NOT NULL | |
| role_id | bigint | NOT NULL | |
### `sys_role_permission`
*Source:* `inferred`
| Column | Type | Null | Notes |
|--------|------|------|-------|
| role_id | bigint | NOT NULL | |
| permission_id | bigint | NOT NULL | |
## Module: MANIFEST / CARGO (GOAL §4.2)
### `manifest`
*Source:* `src: S015, S008, S006`
*Purpose:* Cargo manifest general segment: carrier, voyage, ports, dates, totals, office (S008, S006).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| office_id | bigint | NOT NULL | |
| manifest_year | smallint | NOT NULL | |
| registration_number | integer | nullable | |
| voyage_number | character varying(17) | NOT NULL | |
| transport_mode_id | bigint | NOT NULL | |
| identity_of_transport | character varying(40) | nullable | |
| nationality_id | bigint | nullable | |
| registration_ref | character varying(35) | nullable | |
| master_name | character varying(70) | nullable | |
| carrier_id | bigint | nullable | |
| shipping_agent_id | bigint | nullable | |
| place_departure_id | bigint | nullable | |
| place_destination_id | bigint | nullable | |
| date_of_departure | date | nullable | |
| date_of_arrival | date | nullable | |
| date_of_last_discharge | date | nullable | |
| total_bols | integer | nullable | |
| total_packages | numeric(18,3) | nullable | |
| total_containers | integer | nullable | |
| total_gross_mass | numeric(18,3) | nullable | |
| tonnage_net | numeric(18,3) | nullable | |
| tonnage_gross | numeric(18,3) | nullable | |
| status_id | bigint | nullable | |
| created_at | timestamp with time zone | NOT NULL | |
### `bill_of_lading`
*Source:* `src: S015, S008, S006`
*Purpose:* Transport document (B/L/AWB). House B/L = a consignment; master_bl_id models consolidation/degroupage (S008, S006, S010).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| manifest_id | bigint | NOT NULL | |
| line_number | integer | NOT NULL | |
| bl_reference | character varying(35) | NOT NULL | |
| bl_nature_id | bigint | NOT NULL | |
| bl_type_code | character varying(3) | nullable | |
| is_master | boolean | NOT NULL | |
| master_bl_id | bigint | nullable | |
| exporter_id | bigint | nullable | |
| consignee_id | bigint | nullable | |
| notify_id | bigint | nullable | |
| place_loading_id | bigint | nullable | |
| place_unloading_id | bigint | nullable | |
| number_of_packages | numeric(18,3) | nullable | |
| package_type_id | bigint | nullable | |
| gross_mass | numeric(18,3) | nullable | |
| goods_description | character varying(2000) | nullable | |
| volume_m3 | numeric(18,3) | nullable | |
| freight_indicator | character varying(2) | nullable | |
| freight_value | numeric(18,4) | nullable | |
| freight_currency_id | bigint | nullable | |
| customs_value | numeric(18,4) | nullable | |
| insurance_value | numeric(18,4) | nullable | |
### `container`
*Source:* `src: S015, S008`
*Purpose:* Containers per B/L; reference/size-type follow ISO 6346 (S008 ctn_segment).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| bl_id | bigint | NOT NULL | |
| ctn_reference | character varying(17) | NOT NULL | |
| container_type_id | bigint | nullable | |
| number_of_packages | integer | nullable | |
| empty_full | character varying(10) | nullable | |
| seal1 | character varying(10) | nullable | |
| seal2 | character varying(10) | nullable | |
| empty_weight | numeric(18,3) | nullable | |
| goods_weight | numeric(18,3) | nullable | |
| volume_m3 | numeric(18,3) | nullable | |
| dangerous_goods_code | character varying(10) | nullable | |
| goods_description | character varying(500) | nullable | |
### `manifest_cargo_item`
*Source:* `src: S015, S008`
*Purpose:* Goods/commodity lines within a transport document (S008 Goods_segment/Commodity_Segment).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| bl_id | bigint | NOT NULL | |
| line_number | integer | NOT NULL | |
| hs_code | character varying(6) | nullable | |
| goods_description | character varying(500) | NOT NULL | |
| number_of_packages | numeric(18,3) | nullable | |
| package_type_id | bigint | nullable | |
| gross_mass | numeric(18,3) | nullable | |
| container_id | bigint | nullable | |
### `manifest_status_history`
*Source:* `inferred`
*Purpose:* Manifest status transitions; inferred lifecycle table.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| manifest_id | bigint | NOT NULL | |
| status_id | bigint | NOT NULL | |
| changed_at | timestamp with time zone | NOT NULL | |
| changed_by | bigint | nullable | |
| note | character varying(200) | nullable | |
## Module: DECLARATION (THE SAD) (GOAL §4.3)
### `declaration`
*Source:* `src: S014, S003, S001`
*Purpose:* SAD general segment — one per consignment: parties, regime, transport, invoice totals, status, selectivity (S003 boxes 1-49/B, S001).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| office_id | bigint | NOT NULL | |
| declaration_type_id | bigint | NOT NULL | |
| cpc_id | bigint | nullable | |
| registration_serial | character varying(2) | nullable | |
| registration_number | integer | nullable | |
| registration_date | date | nullable | |
| trader_reference | character varying(35) | nullable | |
| exporter_id | bigint | nullable | |
| consignee_id | bigint | nullable | |
| declarant_id | bigint | nullable | |
| financial_id | bigint | nullable | |
| country_export_id | bigint | nullable | |
| country_origin_id | bigint | nullable | |
| country_destination_id | bigint | nullable | |
| country_last_consign_id | bigint | nullable | |
| trading_country_id | bigint | nullable | |
| incoterm_id | bigint | nullable | |
| delivery_place | character varying(120) | nullable | |
| transport_mode_border_id | bigint | nullable | |
| transport_mode_inland_id | bigint | nullable | |
| border_transport_identity | character varying(60) | nullable | |
| place_of_discharge_id | bigint | nullable | |
| total_items | smallint | nullable | |
| total_packages | numeric(18,3) | nullable | |
| currency_id | bigint | nullable | |
| total_invoice_amount | numeric(18,4) | nullable | |
| exchange_rate | numeric(18,6) | nullable | |
| total_freight | numeric(18,4) | nullable | |
| total_insurance | numeric(18,4) | nullable | |
| total_cif_value | numeric(18,4) | nullable | |
| selectivity_lane_id | bigint | nullable | |
| status_id | bigint | NOT NULL | |
| assessment_number | character varying(20) | nullable | |
| assessment_date | date | nullable | |
| manifest_id | bigint | nullable | |
| created_by | bigint | nullable | |
| created_at | timestamp with time zone | NOT NULL | |
### `declaration_item`
*Source:* `src: S014, S003`
*Purpose:* SAD item segment (boxes 31-49): commodity, origin, mass, procedure, valuation, statistical value = tax base (S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| item_number | smallint | NOT NULL | |
| hs_id | bigint | nullable | |
| hs_code | character varying(12) | NOT NULL | |
| goods_description | character varying(500) | nullable | |
| country_origin_id | bigint | nullable | |
| cpc_id | bigint | nullable | |
| national_procedure | character varying(3) | nullable | |
| exemption_id | bigint | nullable | |
| preference_code | character varying(3) | nullable | |
| number_of_packages | numeric(18,3) | nullable | |
| package_type_id | bigint | nullable | |
| marks_and_numbers | character varying(200) | nullable | |
| container_reference | character varying(17) | nullable | |
| gross_mass | numeric(18,3) | nullable | |
| net_mass | numeric(18,3) | nullable | |
| supplementary_qty | numeric(18,3) | nullable | |
| supplementary_uom_id | bigint | nullable | |
| item_price | numeric(18,4) | nullable | |
| valuation_method_code | character varying(3) | nullable | |
| adjustment_indicator | numeric(9,4) | nullable | |
| statistical_value | numeric(18,4) | nullable | |
| customs_value | numeric(18,4) | nullable | |
| quota | character varying(20) | nullable | |
| warehouse_id | bigint | nullable | |
| warehouse_days | integer | nullable | |
### `valuation_note`
*Source:* `src: S003, S017`
*Purpose:* Declaration-level value build-up: freight + insurance + other -> CIF customs value (S003 valuation note).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| invoice_currency_id | bigint | nullable | |
| total_invoice_fob | numeric(18,4) | nullable | |
| external_freight | numeric(18,4) | nullable | |
| internal_freight | numeric(18,4) | nullable | |
| insurance | numeric(18,4) | nullable | |
| other_costs | numeric(18,4) | nullable | |
| total_cif | numeric(18,4) | nullable | |
### `item_value_note`
*Source:* `src: S014, S003`
*Purpose:* Freight/insurance apportioned per item to produce item CIF, the tax base (S003 valuation note; box 46).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_item_id | bigint | NOT NULL | |
| item_fob | numeric(18,4) | nullable | |
| apportioned_freight | numeric(18,4) | nullable | |
| apportioned_insurance | numeric(18,4) | nullable | |
| apportioned_other | numeric(18,4) | nullable | |
| item_cif | numeric(18,4) | nullable | |
### `declaration_tax_line`
*Source:* `src: S014, S003`
*Purpose:* Per-item per-tax calculation: base, rate, amount, mode of payment (SAD box 47, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_item_id | bigint | NOT NULL | |
| tax_type_id | bigint | NOT NULL | |
| tax_base | numeric(18,4) | NOT NULL | |
| rate_percent | numeric(9,4) | nullable | |
| specific_amount | numeric(18,4) | nullable | |
| tax_amount | numeric(18,4) | NOT NULL | |
| mode_of_payment | character varying(6) | nullable | |
| is_manual | boolean | NOT NULL | |
### `declaration_attached_document`
*Source:* `src: S014, S003, S008`
*Purpose:* Attached/supporting documents at header or item level (SAD box 44, S003; S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| declaration_item_id | bigint | nullable | |
| document_type_id | bigint | NOT NULL | |
| document_reference | character varying(60) | nullable | |
| document_date | date | nullable | |
### `declaration_previous_document`
*Source:* `src: S014, S003, S008`
*Purpose:* SAD box 40: links items to the manifest B/L or a previous declaration (write-off) (S003, S008).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| declaration_item_id | bigint | nullable | |
| bl_id | bigint | nullable | |
| prev_declaration_id | bigint | nullable | |
| reference | character varying(60) | nullable | |
| written_off_packages | numeric(18,3) | nullable | |
| written_off_mass | numeric(18,3) | nullable | |
### `declaration_status_history`
*Source:* `src: S014, S002`
*Purpose:* Declaration status transitions over its lifecycle (S002).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| status_id | bigint | NOT NULL | |
| changed_at | timestamp with time zone | NOT NULL | |
| changed_by | bigint | nullable | |
| note | character varying(200) | nullable | |
## Module: SELECTIVITY / RISK (GOAL §4.6)
### `risk_criterion`
*Source:* `src: S014`
*Purpose:* Risk/selectivity criteria driving lane assignment; inferred (S002 references criteria, no public schema).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(20) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| target_lane_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `selectivity_result`
*Source:* `src: S014, S002, S005`
*Purpose:* Lane assigned to a declaration when selectivity is triggered (S002, S005).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| lane_id | bigint | NOT NULL | |
| criterion_id | bigint | nullable | |
| triggered_at | timestamp with time zone | NOT NULL | |
| officer_id | bigint | nullable | |
| note | character varying(300) | nullable | |
### `inspection_act`
*Source:* `src: S014, S002, S005`
*Purpose:* Inspection/examination act for red/yellow declarations, feeding release (S002, S005).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| officer_id | bigint | nullable | |
| inspected_at | timestamp with time zone | nullable | |
| result | character varying(20) | nullable | |
| findings | character varying(1000) | nullable | |
## Module: ACCOUNTING (GOAL §4.4)
### `account`
*Source:* `src: S016, S003`
*Purpose:* Trader deferred-payment/credit/prepayment accounts (SAD box 48, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| account_number | character varying(20) | NOT NULL | |
| trader_id | bigint | nullable | |
| account_type | character varying(20) | NOT NULL | |
| currency_id | bigint | nullable | |
| balance | numeric(18,4) | NOT NULL | |
| is_active | boolean | NOT NULL | |
### `payment`
*Source:* `src: S016, S003, S005`
*Purpose:* Payment of assessed amount, cash or against an account (S003 box B, S005).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| account_id | bigint | nullable | |
| amount | numeric(18,4) | NOT NULL | |
| currency_id | bigint | nullable | |
| mode_of_payment | character varying(20) | NOT NULL | |
| paid_at | timestamp with time zone | NOT NULL | |
| paid_by | bigint | nullable | |
### `receipt`
*Source:* `src: S016, S003`
*Purpose:* Receipt issued on payment of a declaration (SAD box B, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| payment_id | bigint | NOT NULL | |
| receipt_number | character varying(20) | NOT NULL | |
| receipt_date | date | NOT NULL | |
| total_amount | numeric(18,4) | NOT NULL | |
### `account_movement`
*Source:* `src: S016`
*Purpose:* Ledger movements against an account; inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| account_id | bigint | NOT NULL | |
| payment_id | bigint | nullable | |
| movement_type | character varying(10) | NOT NULL | |
| amount | numeric(18,4) | NOT NULL | |
| balance_after | numeric(18,4) | nullable | |
| moved_at | timestamp with time zone | NOT NULL | |
### `guarantee`
*Source:* `src: S019, S003`
*Purpose:* Security/guarantee for suspense regimes (SAD box 52 references it, S003); table shape inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| reference | character varying(30) | NOT NULL | |
| trader_id | bigint | nullable | |
| guarantee_type | character varying(20) | nullable | |
| amount | numeric(18,4) | NOT NULL | |
| currency_id | bigint | nullable | |
| valid_from | date | nullable | |
| valid_to | date | nullable | |
| is_active | boolean | NOT NULL | |
## Module: TRANSIT & SUSPENSE (GOAL §4.5)
### `transit_declaration`
*Source:* `src: S014, S019, S003`
*Purpose:* Transit declaration extension: principal, offices, guarantee, itinerary (SAD boxes 50-53, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| principal_id | bigint | nullable | |
| office_departure_id | bigint | nullable | |
| office_transit_id | bigint | nullable | |
| office_destination_id | bigint | nullable | |
| guarantee_id | bigint | nullable | |
| itinerary | character varying(300) | nullable | |
| seals_affixed | character varying(200) | nullable | |
| time_limit_date | date | nullable | |
### `ref_warehouse`
*Source:* `src: S013, S019, S003`
*Purpose:* Customs/bonded warehouses (SAD box 49 warehouse identification, S003).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| code | character varying(10) | NOT NULL | |
| name | character varying(120) | NOT NULL | |
| office_id | bigint | nullable | |
| keeper_id | bigint | nullable | |
| is_active | boolean | NOT NULL | |
### `warehouse_entry`
*Source:* `src: S014, S019`
*Purpose:* Goods placed under warehousing regime; inferred from S003 box 49 suspense.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| warehouse_id | bigint | NOT NULL | |
| declaration_id | bigint | nullable | |
| entry_date | date | NOT NULL | |
| packages | numeric(18,3) | nullable | |
| gross_mass | numeric(18,3) | nullable | |
### `warehouse_exit`
*Source:* `src: S014, S019`
*Purpose:* Removal of goods from warehouse (write-off); inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| warehouse_entry_id | bigint | NOT NULL | |
| declaration_id | bigint | nullable | |
| exit_date | date | NOT NULL | |
| packages | numeric(18,3) | nullable | |
| gross_mass | numeric(18,3) | nullable | |
### `temporary_admission`
*Source:* `src: S019`
*Purpose:* Temporary admission suspense regime with time limit and re-export link; inferred (S003 box 49).
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| declaration_id | bigint | NOT NULL | |
| guarantee_id | bigint | nullable | |
| time_limit_date | date | nullable | |
| re_export_declaration_id | bigint | nullable | |
## Module: AUDIT / WORKFLOW (cross-cutting) (GOAL §4.7)
### `audit_log`
*Source:* `src: S013`
*Purpose:* Cross-cutting who/what/when audit trail; inferred.
| Column | Type | Null | Notes |
|--------|------|------|-------|
| id | bigint | NOT NULL | |
| entity_name | character varying(60) | NOT NULL | |
| entity_id | bigint | nullable | |
| action | character varying(20) | NOT NULL | |
| actor_id | bigint | nullable | |
| occurred_at | timestamp with time zone | NOT NULL | |
| detail | character varying(1000) | nullable | |
---
# The ASYCUDA platform
The [Schema](../schema/index.md) section documents **our reconstruction**. This
section documents **ASYCUDA itself** — the software the reconstruction models —
so you can place the toolbox against the real system you are working with:
which generation it is, how its data leaves the system, and where an external
system can plug in.
!!! success "Which version does this toolbox model?"
**ASYCUDA World (v4)** — the current standard, used by 100+ countries. The
schema is grounded in public UNCTAD/ASYCUDA programme table descriptions plus
ASYCUDA World national manuals and message specs (see
[Sources](../provenance/sources.md)). If your deployment is ASYCUDA World,
this model is a direct reference; if it is ASYCUDA++ or ASY5, see the
[version lineage](versions.md) for what changes.
## Which version do *you* have?
Three quick fingerprints (details on the [versions page](versions.md)):
| If you see… | You are on |
|-------------|-----------|
| A **Java** client launched via `.jnlp` (Java Web Start), declarations exported as **XML** files | **ASYCUDA World (v4)** — what this toolbox models |
| A **C++** thick client, **UN/EDIFACT** messages (CUSDEC/CUSRES), Oracle/Informix/Sybase | **ASYCUDA++ (v3)** — legacy, same SAD data model, different exchange layer |
| Cloud-native microservices (Quarkus, Kubernetes, Kafka), REST/event APIs | **ASY5 / New Generation** — phased rollout (Angola live Jan 2026) |
The SAD declaration model — general segment + item segments — is stable across
++ and World, so the [schema](../schema/index.md) remains a valid reference for
both; what changes between generations is the technology and the integration
surfaces.
## Explore
- :material-timeline-clock:{ .lg .middle } **Version lineage**
---
Five generations from 1981 to ASY5 — what each changed, and how to
fingerprint the one in front of you.
[:octicons-arrow-right-24: Versions](versions.md)
- :material-earth:{ .lg .middle } **ASYCUDA World**
---
The modeled version in depth: the XML e-document model, the client, the
closed protocol, and how our schema relates to the real one.
[:octicons-arrow-right-24: ASYCUDA World](asycuda-world.md)
- :material-xml:{ .lg .middle } **XML messages & wire format**
---
The SAD Box → XML tag → toolbox column map, the `
` wire-format
gotcha, the AWMDS manifest stream, WCO DM and EDIFACT.
[:octicons-arrow-right-24: XML messages](xml-messages.md)
- :material-connection:{ .lg .middle } **Integration surfaces**
---
The doors that actually exist — RDBMS/ETL, ASYHUB, Cargo-XML, XML import,
ASY5 — and the specs you must request.
[:octicons-arrow-right-24: Integration](integration.md)
- :material-traffic-light:{ .lg .middle } **Selectivity & clearance**
---
The clearance state machine (C/L/PRN serials) and the four-lane risk model,
mapped to our tables.
[:octicons-arrow-right-24: Selectivity & clearance](selectivity-clearance.md)
- :material-book-open-variant:{ .lg .middle } **Further reading**
---
The curated public-document map — official manuals, national field guides,
real XML samples, ML datasets — and what is restricted.
[:octicons-arrow-right-24: Resources](resources.md)
trade statistics"]
V2["v2 / v2.7 · 1985–95
full clearance"]
V3["ASYCUDA++ v3 · 1992+
C++ · EDIFACT · legacy"]
V4["ASYCUDA World v4 · 2002+
Java · XML · current standard"]
V5["ASY5 / New Generation · 2024–26
cloud-native · Angola live Jan 2026"]
V1 --> V2 --> V3 --> V4 --> V5
style V4 fill:#d9770622,stroke:#d97706
```
ASYCUDA World (v4) is the current standard — around **90%** of the 100+ using
countries and territories are on it or migrating to it. ASYCUDA++ is legacy but
still live in a few countries mid-migration; the new generation ("ASY5") is in
phased rollout, with **Angola live since January 2026**.
## Comparison across generations
| Version | Era | Tech stack | Database | Architecture | Data model | Who runs it |
|---|---|---|---|---|---|---|
| **v1** | 1981–84 | DOS-era micro | Flat files | Standalone micro | Trade-statistics capture | First ECOWAS countries — statistics only |
| **v2 / v2.7** | 1985–95 | C, DOS/UNIX | Flat-file / Informix | LAN client + file server | Full clearance | Many developing countries in the 1990s; some legacy holdouts |
| **ASYCUDA++ (v3)** | 1992–present (legacy) | **C++**, UN/EDIFACT | **Oracle / Informix / Sybase** | Thick client-server | Relational; EDIFACT (CUSDEC); MODSEL selectivity | Rolled out late-90s–2000s; a few countries still live, mid-migration |
| **ASYCUDA World (v4)** | 2002–present (**current standard**) | **100% Java**, XML documents, Java Web Start client | Oracle, MS SQL Server, MySQL, PostgreSQL, DB2, Sybase, Informix | Web / store-and-forward, N-tier | **XML e-document model**; SAD = general segment + item segments | 100+ countries/territories; ~90% on or migrating to it |
| **ASY5 / New Generation** | 2024–26 (Angola live Jan 2026) | Cloud-native microservices: **Quarkus, Kubernetes, Kafka, Keycloak, VueJS**; open-source | Cloud data platform | Modular microservices, REST / event | Data-driven, WCO-DM aligned; native + third-party AI risk | Phased rollout; Angola first |
## How to tell which version you have
The three current generations leave distinct fingerprints. If you know the
client and the wire format, you know the version.
- :material-language-java:{ .lg .middle } **ASYCUDA World (v4)**
---
100% Java. The client is a **Java Web Start thick client** — a `.jnlp`
launch file pulling ~130 JARs over HTTPS, main class
`so.kernel.client.DesktopMain`, connecting to the server on a custom TCP
port (e.g. `//host:2016/`). Business data moves as **non-namespaced XML
e-documents**. If you see a JNLP launcher and XML exports, it is v4.
- :material-language-cpp:{ .lg .middle } **ASYCUDA++ (v3)**
---
A **C++ thick client** in a client-server deployment, backed by a relational
**Oracle / Informix / Sybase** database. Data exchange is **UN/EDIFACT** —
the `CUSDEC` message is the declaration. Selectivity runs through the MODSEL
module. No XML e-documents, no Java client.
- :material-cloud-outline:{ .lg .middle } **ASY5 / New Generation**
---
**Cloud-native microservices** — Quarkus, Kubernetes, Kafka, Keycloak, a
VueJS front end — and open-source. Integration is REST and event-based
(Kafka topics), with a first-class hook for native and third-party AI risk
signals. In phased rollout; Angola went live in January 2026.
## Modules
Across ASYCUDA World and the new generation, functional modules extend the core
clearance system:
- **ASYHUB** — external-system data hub (an API for authorised entities).
- **ASYFCI** — Fast Cargo Integration (the cargo-manifest / AWMDS handler).
- **ASYADN** — advance data (pre-arrival manifest information).
- **ASYREC** — relief consignments (humanitarian).
- **eCITES** — endangered-species trade permits.
- **ASYCUDA Single Window** — connects partner government agencies for licences,
permits and certificates.
## An accuracy caveat
Not every developing country runs ASYCUDA. Region is not a reliable signal:
**Senegal never adopted SYDONIA** — it built its own **GAINDE** system — and some
countries run other platforms entirely. Confirm the target administration before
you assume it is on ASYCUDA at all.
!!! note "This toolbox models ASYCUDA World (v4)"
The Sydonia Toolkit reconstructs the data model of **ASYCUDA World
(v4)** specifically — the current standard, and the version whose field-level
model is public through its XML layer and national manuals. It does **not**
model the ASYCUDA++ relational schema or the ASY5 microservice data platform.
See [ASYCUDA World — the modeled version](asycuda-world.md) for what that
means for the schema, and [Sources](../provenance/sources.md) for the
official documents behind it.
---
# ASYCUDA World — the modeled version
This is the version the Sydonia Toolkit models. Everything in the
[Schema](../schema/index.md) tab is a reconstruction of **ASYCUDA World (v4)** —
so it is worth knowing what the real platform is, how it stores data, and where
the line falls between what is public and what is not.
## What ASYCUDA World is
ASYCUDA World has been UNCTAD's current standard since **2002**, running in
**100+ countries and territories**. It is **100% Java**, built as a **web /
store-and-forward N-tier** system. The store-and-forward design is deliberate:
the client does not need a permanent connection to the server, which is what
makes it workable in countries with weak or intermittent telecoms — a declaration
can be captured offline and forwarded when a connection returns.
It is database-agnostic. A national instance can run on **Oracle, MS SQL Server,
MySQL, PostgreSQL, DB2, Sybase, or Informix**. The application layer is the same
Java stack regardless of which database sits underneath.
## The XML e-document model
ASYCUDA World does not store business objects the way you might expect a
relational system to. Every business object — a declaration, a manifest, a
receipt — is a **non-namespaced XML document**. The Java client can **Export XML
File** and **Import XML File** for these documents, and this XML layer is the
public window onto the field-level model.
The declaration — the Single Administrative Document (SAD) — is structured as two
kinds of segment:
- A **general segment**: one per consignment, carrying parties, transport,
valuation totals and identification.
- Repeating **item segments**: one per commodity line, each with HS code, origin,
mass, value and taxes.
- Plus up to five **additional forms** extending the item detail.
Field values follow a small set of data types:
| Type | Meaning |
|------|---------|
| `INT` | integer |
| `N#` | decimal number |
| `AN#` | alphanumeric |
| `DATE` | date, `yyyy-MM-dd` |
| `TIME` | time, `hh:mm:ss` |
| `BOOLEAN` | true / false |
One declaration per file; multiple items per declaration; extra tags are ignored
on import. The mechanics of the wire format, the `` root and the
Box → tag map are covered in [XML messages](xml-messages.md).
## The client and its closed protocol
The ASYCUDA World client is a **Java Web Start thick client**: a `.jnlp` launch
file pulls roughly **130 JARs** over HTTPS, the main class is
`so.kernel.client.DesktopMain`, and it connects to the national server over a
**custom TCP port** (for example `//host:2016/`). That connection is a
**proprietary object/XML protocol — not SOAP, not REST.**
!!! warning "Do not integrate at the client protocol"
The client-to-server socket (`so.kernel`, port ~2016) is a closed,
proprietary protocol. It is not a supported integration surface, and building
against it is fragile and unsanctioned. Real integration happens elsewhere —
the RDBMS, ASYHUB, IATA Cargo-XML, or XML file import. See
[Integration surfaces](integration.md) for the doors that actually exist.
## What this toolbox models
The toolbox reconstructs the ASYCUDA World data model as a clean, normalised
PostgreSQL schema. It is grounded in public UNCTAD/ASYCUDA programme table
descriptions covering the four table families:
| Family | Covers |
|--------|--------|
| Reference tables | the `UN*` code-table catalogue |
| Declaration tables | `SAD_General_Segment`, `SAD_Item`, `SAD_Tax`, selectivity, inspection… |
| Manifest tables | `GEN_TAB`, `BOL_TAB`, containers, transit |
| Accounting tables | receipts, `TAX_TAB`, account transactions |
See [Sources](../provenance/sources.md) for the full registry.
There is a deliberate difference between the official model and ours. ASYCUDA
World's **official physical schema is wide and denormalised** — and it is **not
published** by UNCTAD. What this toolbox provides is an **information-equivalent
reference**: a normalised model that carries the same field-level information,
restructured to be readable and queryable. It is not a byte-for-byte copy of the
real tables; it is a faithful reconstruction of what they hold.
The table-by-table mapping between our reconstruction and the official tables —
including the deliberate structural differences — is in
[Official fit & gap](../provenance/fit.md), and the schema itself starts at the
[Schema overview](../schema/index.md).
## Where the public docs live
The field-level model is public *only through the XML layer and national
manuals* — never as a published database schema. The documentation lives in a few
predictable places:
- **Programme materials:** the public UNCTAD/ASYCUDA introductory and SAD
processing manuals and general platform documentation.
- **National customs portals:** each country documents its own instance. These
SAD field guides and broker manuals are the richest public window onto the
model.
- **Restricted (member customs only):** gated learning platforms, credentialed
developer repositories and the core functional and technical reference manuals.
Request access through a national customs ASYCUDA project team or the UNCTAD
ASYCUDA programme.
More on the shape of this landscape is on the [Resources](resources.md) page.
## Next
- [XML messages](xml-messages.md) — the `` document format and the
Box → tag map.
- [Integration surfaces](integration.md) — the real doors for reading and
writing ASYCUDA data.
- [Selectivity & clearance](selectivity-clearance.md) — the risk lanes and the
clearance state machine.
---
# XML messages & the wire format
In ASYCUDA World (v4) every business object — a declaration, a cargo manifest —
is an **XML e-document**. The Java client's *Export XML File* / *Import XML File*
buttons round-trip these files, and XML is the lingua franca between AW and any
system that speaks the WCO Data Model.
That matters here for one reason: **UNCTAD does not publish ASYCUDA's physical
database schema.** The field-level model is public *only through the XML layer*
(plus national customs manuals). So the XML is the one public window into the
same fields this toolbox reconstructs as PostgreSQL tables — which is why the
three-way map below (SAD box → AW XML tag → our column) is the core of this page.
!!! warning "Spec vs. reality — the wire-format trap"
The idealised UNCTAD specification documents a tree rooted at `` /
`` / ``. **Real exported/imported AW instances use a different
root and naming** — verified identical across recovered schema definitions,
national sample XMLs, and independent integrator codebases:
- Root element is **``** (not ``).
- Adds top-level ``, ``, ``,
and **``** (holding `Sad_flow` I/E, ``, ``,
`Place_of_declaration`, `Date_of_declaration`).
- The item tariff block is **``** (not ``).
- `` carries ``, ``, ``
(each with `Serial_number` / `Number` / `Date`).
**Practical rule:** use the **spec for field semantics/formats** and a
**real sample for exact structure**. Target the ``-rooted format —
take the exact structure from a real exported declaration or a recovered
schema definition rather than from the idealised spec.
Every declaration has **two segments**: a **general segment** (consignment-level
— parties, transport, valuation totals) and repeating **item segments** (one per
commodity — HS code, origin, value, quantity, taxes). Both map cleanly onto our
[`declaration`](../schema/declaration.md) and `declaration_item` tables.
## The three-way field map
Columns: **SAD box** · **field** · **AW XML tag** (spec naming) · **Open Customs
Toolbox column** in the [`asycuda`](../schema/data-dictionary.md) schema. Where
our reconstruction has no counterpart, the cell reads **—**.
### General segment (consignment level)
| SAD box | Field | AW XML tag | Toolbox column |
|:-------:|-------|------------|----------------|
| A | Customs office | `Identification/Office_segment/Customs_clearance_office_code` | `declaration.office_id` |
| A | Registration identity | `Identification/Registration` (Serial/Number/Date) | `declaration.registration_serial` · `registration_number` · `registration_date` |
| 1 | Declaration type / procedure | `Identification/Type/Type_of_declaration`, `General_procedure_code` | `declaration.declaration_type_id` |
| 2 | Exporter / consignor | `Traders/Exporter/Exporter_code`, `Exporter_name` | `declaration.exporter_id` |
| 3 | Forms (X of Y) | — | — |
| 5 | Total items | (count of `- `) | `declaration.total_items` |
| 6 | Total packages | `Identification/Total_number_of_packages` | `declaration.total_packages` |
| 7 | Declarant reference (LRN/UCR) | `Declarant/Reference/Number` | `declaration.trader_reference` |
| 8 | Importer / consignee | `Traders/Consignee/Consignee_code`, `Consignee_name` | `declaration.consignee_id` |
| 9 | Financial-settlement party | `Traders/Financial/Financial_code` | `declaration.financial_id` |
| 10 | Country of last consignment | `General_information/Country/...` | `declaration.country_last_consign_id` |
| 11 | Trading country | `General_information/Country/Trading_country` | `declaration.trading_country_id` |
| 12 | Value details | `General_information/Value_details` | — |
| 14 | Declarant / representative | `Declarant/Declarant_code`, `Declarant_representative` | `declaration.declarant_id` |
| 15 | Country of dispatch/export | `General_information/Country/Export/Export_country_code` | `declaration.country_export_id` |
| 16 | Country of origin (header) | `General_information/Country/...` | `declaration.country_origin_id` |
| 17 | Country of destination | `General_information/Country/Destination/Destination_country_code` | `declaration.country_destination_id` |
| 18 | Transport identity & nationality (dep/arr) | `Transport/Means_of_transport/Departure_arrival_information/Identity`, `Nationality` | `declaration.border_transport_identity` |
| 19 | Container flag (0/1) | `Transport/Container_flag` | — |
| 20 | Delivery terms (INCOTERMS) | `Transport/Delivery_terms/Code`, `Place` | `declaration.incoterm_id` · `delivery_place` |
| 21 | Border transport identity & nationality | `Transport/Means_of_transport/Border_information/Identity`, `Nationality` | `declaration.border_transport_identity` |
| 22 | Currency & total invoiced | `Valuation/Invoice/Currency_code`, `Amount_foreign_currency` | `declaration.currency_id` · `total_invoice_amount` |
| 23 | Exchange rate (system-filled) | `Currency_rate` | `declaration.exchange_rate` |
| 24 | Nature of transaction | `Financial/Financial_transaction/Code_1`, `Code_2` | — |
| 25 | Border mode of transport | `Transport/.../Border_information/Mode` | `declaration.transport_mode_border_id` |
| 26 | Inland mode | `Transport/.../Inland_mode_of_transport` | `declaration.transport_mode_inland_id` |
| 27 | Place of loading/unloading | `Transport/Place_of_loading/Code`, `Name` | `declaration.place_of_discharge_id` |
| 28 | Financial & banking data | `Financial/Bank`, `Financial/Terms` | — |
| 29 | Office of entry/exit | `Transport/Border_office/Code` | `declaration.office_id` |
| 30 | Location of goods | `Transport/Location_of_goods` | — |
| B | Accounting (assessment) | `Identification/Assessment` (Serial/Number/Date) | `declaration.assessment_number` · `assessment_date` |
### Item segment (per commodity, repeats)
| SAD box | Field | AW XML tag | Toolbox column |
|:-------:|-------|------------|----------------|
| 31 | Packages & goods description | `Packages/Number_of_packages`, `Marks1/2_of_packages`, `Kind_of_packages_code` | `declaration_item.number_of_packages` · `package_type_id` · `marks_and_numbers` · `goods_description` |
| 32 | Item number | (position of `
- `) | `declaration_item.item_number` |
| 33 | Commodity code (HS) | `Tariff/Harmonized_system/Commodity_code`, `Precision_1..4` | `declaration_item.hs_id` · `hs_code` |
| 34 | Country of origin | `Goods_description/Country_of_origin_code` | `declaration_item.country_origin_id` |
| 35 | Gross mass (kg) | `Valuation_item/Weight/Gross_weight_itm` | `declaration_item.gross_mass` |
| 36 | Preference code | `Tariff/Preference_code` | `declaration_item.preference_code` |
| 37 | Procedure (CPC: extended + national) | `Tariff/Extended_customs_procedure`, `National_customs_procedure` | `declaration_item.cpc_id` · `national_procedure` |
| 38 | Net mass (kg) | `Valuation_item/Weight/Net_weight_itm` | `declaration_item.net_mass` |
| 39 | Quota | `Tariff/Quota/Quota_code` | `declaration_item.quota` |
| 40 | Previous document / summary declaration | `Previous_document/Summary_declaration`, `Previous_document_reference` | `declaration_previous_document.reference` (+ `bl_id` / `prev_declaration_id`) |
| 41 | Supplementary units | `Tariff/Supplementary_unit/Supplementary_unit_quantity`, `_code` | `declaration_item.supplementary_qty` · `supplementary_uom_id` |
| 42 | Item price | `Valuation_item/Invoice/Amount_foreign_currency`, `Currency_code` | `declaration_item.item_price` |
| 43 | Valuation method (WTO 1–6) | `Tariff/Valuation_method_code` | `declaration_item.valuation_method_code` |
| 44 | Additional info / documents | `Attached_documents/Attached_document`, `Additional_information/Licence_number` | `declaration_attached_document.*` |
| 45 | Adjustment factor | `Valuation_item/Rate_of_adjustment` | `declaration_item.adjustment_indicator` |
| 46 | Statistical / customs value | `Statistical_value` | `declaration_item.statistical_value` · `customs_value` |
| 47 | Calculation of taxes | `Taxation/Taxation_line` (see below) | `declaration_tax_line.*` |
| 48 | Deferred payment | `Financial/Deffered_payment_reference` | — |
| 49 | Warehouse identification | `Warehouse/Identification`, `Delay` | `declaration_item.warehouse_id` · `warehouse_days` |
!!! note "Transit & official boxes"
Transit boxes 50–53 + C/D map to `Transit/Principal`, `Financial/Guarantee`,
`Transit/Destination/Office`, `Transit/Signature` — see
[transit & suspense](../schema/transit-suspense.md). Box B (accounting) and
D/J (control results) are official/server-side; only the assessment identity
is reconstructed (`declaration.assessment_number`, `assessment_date`).
### The taxation-line sub-table (box 47)
Each `
- ` carries one `` plus one `` with repeating
``. Confirmed from real declaration XML and a recovered schema
definition:
| AW XML field | Meaning | Toolbox column |
|--------------|---------|----------------|
| `Duty_tax_code` | Tax type (DOG/DDI duty; TVA/TGC VAT; DA excise; RS statistical…) | `declaration_tax_line.tax_type_id` |
| `Duty_tax_Base` | Tax base | `declaration_tax_line.tax_base` |
| `Duty_tax_rate` | Rate (ad valorem, e.g. `0.2`) | `declaration_tax_line.rate_percent` |
| `Duty_tax_amount` | Computed amount | `declaration_tax_line.tax_amount` |
| `Duty_tax_MP` | Mode of payment (1 payable / 0 guaranteed) | `declaration_tax_line.mode_of_payment` |
| `Duty_tax_Type_of_calculation` | Calculation type | `declaration_tax_line.is_manual` (manual vs auto flag) |
| `Item_taxes_amount`, `Global_taxes` | Item / declaration totals | — (derived) |
!!! tip "Assessed lines are server-generated"
Computed taxation lines and the assigned selectivity lane are **generated
server-side at assessment** — they appear in the fuller CUSDEC/CUSRES export,
not in a trader's inbound import-XML. See
[selectivity & clearance](selectivity-clearance.md).
## Valuation build-up
Invoices are usually FOB but duty is charged on CIF, so AW carries a value
build-up. Each `` sub-element (`Invoice`, `External_freight`,
`Internal_freight`, `Insurance`, `Other_cost`, `Deduction`) carries an
`Amount_foreign_currency` + `Currency_code`; the declaration-level ``
adds `Calculation_working_mode` (0 = apportion per value / 1 = per weight /
2 = none). This maps directly onto our two value-note tables:
- `valuation_note` — declaration-level: `total_invoice_fob` + `external_freight`
+ `internal_freight` + `insurance` + `other_costs` → `total_cif`.
- `item_value_note` — per item: `item_fob` + `apportioned_freight` +
`apportioned_insurance` + `apportioned_other` → `item_cif` (the tax base).
See [declaration → valuation](../schema/declaration.md#valuation-building-the-tax-base)
for the worked split.
## Cargo manifest — AWMDS
The cargo manifest is a separate XML stream, handled by the **ASYFCI** module
(ASYCUDA Fast Cargo Integration) and validated by **`Awmds.xsd`**. Root element
**``** ("ASYCUDA World Manifest Data Stream"), two segments:
- **``** — voyage/office IDs, totals, transport info (carrier,
shipping agent), load/unload UN/LOCODEs, base64 ``
(RFC 2045), coloader, previous-manifest id → our
[`manifest`](../schema/manifest.md) header.
- **``** (1..∞, one per bill of lading) — `Bol_nature`
(22 = exports / 23 = imports / 24 = in-transit / 28 = transhipment), traders
(exporter / notify / consignee), a repeating `` (container type,
empty/full, marks, temperature, dangerous-goods) and `` /
`` (HS) → our `bill_of_lading`, `container` and
`manifest_cargo_item` rows.
Companion streams share ``: **Degroupage** (``,
`Awbolds.xsd`) and **Coloader** (``, `Awmcds.xsd`). These XSDs ship
inside the ASYFCI client — they are not canonical UNCTAD downloads; national
portals mirror the spec PDFs.
| AWMDS element | Toolbox table |
|---------------|---------------|
| `` | `manifest` |
| `` | `bill_of_lading` (`bl_nature_id` ← `Bol_nature`) |
| `` | `container` |
| `` / `` | `manifest_cargo_item` |
## Standards lineage
- **WCO Data Model**
ASYCUDA is "compatible with the WCO data model." WCO DM **v3.6.0 (May 2016)**
shipped a conformance report of the ASYCUDA information model to the WCO DM
for SAD import/export data — the mapping bridge. Current WCO DM is **v4.2.0
(July 2025)**; UNCTAD's public page still references "version 3" (stale).
!!! note "Evidence gap"
No public source confirms ASYCUDA natively implements the full **GOVCBR**
message envelope — conformance is documented at the data-element/SAD
level, not as a GOVCBR message-structure implementation.
- **EDIFACT lineage (ASYCUDA++)**
ASYCUDA++ used UN/EDIFACT; AW moved primary exchange to XML while staying
++-compatible. Relevant messages: **CUSDEC** (declaration = SAD), **CUSRES**
(response), **CUSCAR** (cargo report), **CUSREP** (conveyance), **CONTRL**
(syntax ack). Public EDIFACT CUSDEC XSDs are generic UN/EDIFACT, not
ASYCUDA-specific.
- **IATA Cargo-XML (air cargo)**
An official IATA + UNCTAD program. AW accepts **XFFM** (flight manifest),
**XFWB** (master AWB), **XFZB** (house AWB) and returns **XFNM** (response:
Processed/Received/Rejected). Requires AW **v4.3.2+**; transport is
deployment-dependent — **SOAP** or **SMTP/email** for the same message set.
## Related
- [Declaration — the SAD](../schema/declaration.md) · [Manifest & cargo](../schema/manifest.md)
- [Data dictionary](../schema/data-dictionary.md) · [Querying guide](../guides/querying.md)
- [Integration surfaces](integration.md) · [Selectivity & clearance](selectivity-clearance.md)
- [How faithful is the reconstruction?](../provenance/fit.md)
---
# Integration surfaces
There is **no public REST API**, **no single master WSDL**, and the thick-client
protocol is closed. And yet ASYCUDA World is integrated with banks, port
community systems, partner-government agencies and external risk engines every
day — through four or five real doors, not the front one. This page maps those
doors, the gaps you must formally request, and how this reconstruction stands in
for the biggest gap of all.
!!! warning "Do not integrate at the client protocol"
The AW desktop client is a **Java Web Start thick client**
(`ASYCUDAWorld.jnlp`, ~130 JARs over HTTPS, main class
**`so.kernel.client.DesktopMain`**) that connects to the server on a **custom
TCP port** (e.g. `//host:2016/`) with a **proprietary object/XML protocol —
NOT SOAP or REST**. The `so.kernel.*` / `so.util.*` namespace is UNCTAD's
internal kernel; this channel is effectively closed to third parties. **Do
not build an integration by speaking the client protocol.**
## The integration doors (ranked by how documented/proven they are)
| Surface | What it is | Fit |
|---------|------------|-----|
| **Direct RDBMS / ETL** | AW stores everything in Oracle/MSSQL/MySQL/PostgreSQL. BI and warehouses pull via SQL / replication / ETL against a read-replica. | **Best for training-data extraction** — but the schema is not public (needs DBA + schema discovery). |
| **ASYHUB** | UNCTAD's open, cloud-native, microservice data hub: *"provides an API for authorized entities to access customs data and documents,"* supports pre-arrival risk analysis, extendable connectors, ETL + monitoring dashboard. Aligned to WCO DM v3.8.1. | **Best sanctioned real-time path** — the intended external-integration door. API spec not public (request it). |
| **IATA Cargo-XML (SOAP/SMTP)** | Machine interface for air-cargo manifests (XFFM/XFWB/XFZB → XFNM), per-filer login/password, per-country endpoints. | Viable **pre-arrival cargo feed**; endpoints are credential-gated. |
| **XML file import** | External systems generate conformant `` XML, loaded via the client's *Import XML File*. Some integrators UI-automate the client. | Declaration in/out **without a DB** — but not a real-time API. |
| **ASY5 event/REST** | New-generation microservices expose Kafka topics + REST; the risk pipeline ingests external "signals." | **Friendliest** once the target is on ASY5 (Angola only, Jan 2026); specs not yet public. |
!!! note "DTI is not an API"
**Direct Trader Input (DTI)** — prepare/validate/print/submit a SAD and get a
registration/acceptance reference — is a **UI capability of the thick
client**, not a REST endpoint. Programmatic declaration submission goes
through the XML file path (SAD XML) or the SOAP services, not DTI. AW also
ships **built-in reporting / Business-Intelligence alerts** — useful for
aggregates, less so for row-level training data.
## Architecture at a glance
```mermaid
flowchart LR
subgraph EXT["External systems"]
ML["Risk / ML engine"]
CARGO["Cargo reporter"]
PGA["PGA / bank / port"]
end
subgraph DOORS["Integration doors"]
DB[("Direct RDBMS / ETL")]
HUB["ASYHUB API"]
CXML["IATA Cargo-XML (SOAP/SMTP)"]
XML["XML file import"]
ASY5["ASY5 event/REST"]
end
subgraph AW["ASYCUDA World"]
SRV["AW server (so.kernel, TCP ~2016)"]
RDBMS[("Customs DB")]
SRV --- RDBMS
end
ML --> DB
ML --> HUB
CARGO --> CXML
CARGO --> XML
PGA --> HUB
ML -. future .-> ASY5
DB --> RDBMS
HUB --> SRV
CXML --> SRV
XML --> SRV
ASY5 --> SRV
```
## Where this toolbox fits
The single most important non-public gap is the **physical DB schema** — the one
thing you need to design an ETL job, a warehouse target, an integration test, or
the shape of your training data *before* you get real access.
**This reconstruction is the public stand-in for that gap.** 55 tables in the
[`asycuda`](../schema/index.md) schema, provenance-tagged against the public XML
and manuals, let you:
- Design ETL and warehouse targets against a concrete, credible column model
instead of guessing — then remap to the real schema once a DBA hands it over.
- Build and run integration tests against realistic data before you have a
sandbox: [load the schema](../guides/loading.md) into any Postgres.
- Prototype training-data shapes and feature pipelines against the same fields
the real system exposes.
The [skills](../skills/index.md) (`customs-query`, `customs-seed`,
`customs-extend`) drive exactly this: query, populate and adapt the model to your
deployment's quirks before you connect anything real.
## What you must request
None of the following is publicly documented. Request from your national customs
administration or UNCTAD:
1. **Physical DB schema / ERD** — the biggest gap for both training-data
extraction and write-back.
2. **ASYHUB API specification** (OpenAPI/WSDL, auth, message catalog) — the
single most important unknown for sanctioned real-time integration.
3. **ASY5 "third-party AI → risk profile" signal payload format & endpoint.**
4. **Asysel admin data model** — criterion operators, priority, validity, AND/OR
syntax, score→lane thresholds (deliberately hidden to prevent gaming).
5. **The exact selectivity-injection write-path** (criteria table? trader
profile? signal table? DB trigger?).
6. **Tax-rules "customs taxation language"** grammar + the national code lists
(tariff, tax-type, CPC/ANC, exemptions, offices, currencies).
7. **Inspection-Act read access** (illicit flag + recovered revenue) — required
for ML labels and the feedback loop.
**Channels to obtain restricted docs:** your national customs ASYCUDA project
team or the UNCTAD ASYCUDA programme — via its gated learning platforms and
credentialed developer repositories for member customs administrations.
!!! tip "Confirm these three before building"
- Which **generation** — ASYCUDA++, World, or ASY5? (ASY5/ASYHUB are far
friendlier.)
- **When does selectivity fire** — before or after assessment?
- **Real-time vs batch** scoring, and **pre-arrival vs at-assessment**?
## Bespoke integrations (no published spec)
**Single Window** connects traders ↔ customs ↔ partner-government agencies for
LPCO admissibility documents via WCO-DM XML (eCITES for endangered-species
permits is a concrete example). **e-Payment** generates a reference number, the
trader pays 24×7, and success is posted back to ASYCUDA in real time — bespoke
per bank and country. **NII/scanner** and **port-community** integrations exist
in deployments but carry no published, standardized interface spec.
## Related
- [Selectivity & clearance](selectivity-clearance.md) — the lane model and where
a risk score is injected.
- [The ML risk engine path](../guides/ml-risk-engine.md) — reading, scoring,
injecting and feeding back.
- [XML messages & the wire format](xml-messages.md) — the file-import door in
detail.
- [Resources & documents](resources.md) — where the public and restricted specs
live.
---
# Running on a real ASYCUDA World
**The goal of this whole toolbox is to help you put your own ASYCUDA data to
work — for analytics, machine-learning risk models, and smarter selectivity.**
The reference schema, the queries and the skills are the vehicle; your live
ASYCUDA World database is the destination. This page is the bridge between them.
!!! tip "The easiest path: the query compiler"
You usually don't need to hand-write the adapter below. The
[**query compiler**](../compiler/index.md) compiles logical queries into
genuine ASYCUDA World SQL on the fly (a CTE prelude, no view-creation
privilege needed), and can also emit these persistent compatibility views for
you (`emit-views`, with your per-instance overrides). Start there; this page is
the deployment, FDW and ETL detail behind it — read on when you need the bulk
ETL alternative or the cross-dialect / read-replica specifics.
!!! abstract "Why you're here"
You want to analyse real declarations, engineer features, train a
fraud / valuation model, or feed risk signals back into the clearance lanes —
on *your* data. Put the bridge below in place once, and the
[useful queries](../guides/useful-queries.md), the
[`customs-query` skill](../skills/index.md), and the
[ML risk-engine blueprint](../guides/ml-risk-engine.md) all run against the
real system unchanged.
Everything in this toolbox is written against a **normalised reference model**:
`declaration`, `declaration_item`, `declaration_tax_line`, a wall of `ref_*`
code tables, snake_case names, surrogate `id` keys, foreign keys. That model is
readable and queryable — it is exactly what you want for analytics and ML.
A **real** ASYCUDA World database is the opposite. It is a **wide, denormalised**
physical schema optimised for the AW engine: `SAD_General_Segment`, `SAD_Item`,
`SAD_Tax`, `GEN_TAB`/`BOL_TAB`/`CTN_TAB`, `UN*`/`xx*TAB` reference tables. Codes
and their names are stored **inline** on the row (`GEN_CAR_COD` *and*
`GEN_CAR_NAM`), the general segment is **repeated into every item row**, keys are
engine `INSTANCE_ID` values, and the commodity code is **split across
`TAR_HSC_NB1..NB5`**.
And the deepest problem: **ASYCUDA World's physical schema is not public.** The
field-level model is documented only through the XML layer and national manuals
([XML messages](xml-messages.md)); the exact physical table and column names are
the [#1 "must-request" gap](integration.md). So this page describes an approach
that stays honest about that gap — you supply the last mile, once.
Two ways to bridge the two worlds — both in service of getting your data into
analytics, ML and selectivity:
- **Approach 1 — compatibility views** — run our queries, skills and feature
extraction **live** on the real database.
- **Approach 2 — ETL into the reference model** — copy into a clean snapshot for
**bulk analytics and model training**.
---
## Approach 1 — compatibility views (recommended for running our queries live)
Create **read-only views**, in schema `asycuda`, named **exactly** like our
tables, each one `SELECT`-ing and aliasing from the real AW tables. Because our
queries address tables unqualified via `search_path=asycuda,public`, and the
views carry our column names, **every query we write — and the whole
`customs-query` skill — runs unchanged** against the live system. No rewrites.
A worked mini-example. The declaration header comes from
`SAD_General_Segment`; note the de-duplication (AW repeats the general segment
into each item row) and the inline office code exposed as our `office_id`:
```sql
CREATE SCHEMA IF NOT EXISTS asycuda;
SET search_path TO asycuda, public;
-- our `declaration` <- AW SAD_General_Segment
CREATE OR REPLACE VIEW declaration AS
SELECT DISTINCT ON (g.INSTANCE_ID)
g.INSTANCE_ID AS id, -- engine key -> our surrogate id
g.SGS_CUO_COD AS office_id, -- inline office code -> code-keyed ref_customs_office.id
g.SGS_REG_NBR AS registration_number,
g.SGS_REG_DAT AS registration_date,
g.STA AS status_id, -- lifecycle STA code
CASE -- PTY colour flags -> our lane code
WHEN g.PTY_RED = '1' THEN 'RED'
WHEN g.PTY_YEL = '1' THEN 'YELLOW'
WHEN g.PTY_GRE = '1' THEN 'GREEN'
ELSE NULL
END AS selectivity_lane_id
-- … the remaining columns of our declaration contract …
FROM SAD_General_Segment g -- TODO(instance): confirm real table name
ORDER BY g.INSTANCE_ID;
```
The tax lines come from `SAD_Tax`, whose field roots **`COD` / `BSE` / `RAT` /
`AMT` / `MOP`** are documented ([FIT.md](../provenance/fit.md),
[box 47](xml-messages.md)), so they map directly — no guessing:
```sql
-- our `declaration_tax_line` <- AW SAD_Tax
CREATE OR REPLACE VIEW declaration_tax_line AS
SELECT
x.INSTANCE_ID AS id,
x.TAX_ITM_ID AS declaration_item_id, -- TODO(instance): confirm link column
x.COD AS tax_type_id, -- SAD_Tax COD -> code-keyed ref_tax_type.id
x.BSE AS tax_base, -- SAD_Tax BSE
x.RAT AS rate_percent, -- SAD_Tax RAT (ad valorem)
x.AMT AS tax_amount, -- SAD_Tax AMT
x.MOP AS mode_of_payment, -- SAD_Tax MOP (1 payable / 0 guaranteed)
(x.TYP = '1') AS is_manual -- SAD_Tax TYP manual/automatic flag
FROM SAD_Tax x; -- TODO(instance): confirm real table name
```
The full template — every core table (`ref_*`, `trader`, `declaration`,
`declaration_item`, `declaration_tax_line`, `manifest`, `bill_of_lading`,
`container`) — is in
[`Sydonia/adapters/asycuda_world_compat.sql`](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/Sydonia/adapters/asycuda_world_compat.sql),
with a [README](https://github.com/FrancoisChastel/sydonia-toolkit/blob/master/Sydonia/adapters/README.md)
covering the workflow.
!!! warning "The template will not run as-is"
The physical column names are **instance/version-specific and non-public**.
Every place the public docs don't pin an exact name is marked
`-- TODO(instance):` for a DBA to fill once, using the real schema/ERD you
[request](integration.md). The names that *are* pinned (`SAD_Tax`
`COD`/`BSE`/`RAT`/`AMT`/`MOP`; the `GEN_*`/`BOL_*`/`SGS_*` prefixes) come
from `FIT.md`/`xml-messages.md` and are your worked example.
!!! note "Read-only, so the privacy guarantees hold"
Views are read-only; the adapter never writes to AW's own objects. So the
[`customs-query-tester`](../skills/index.md) — which returns metadata only,
never rows — stays safe even when `CUSTOMS_DB` points at a database holding
real declarations.
**Not on PostgreSQL?** ASYCUDA World is database-agnostic (Oracle, MS SQL Server,
MySQL, PostgreSQL, DB2…). If the instance is on Oracle/MSSQL, either create the
equivalent views there in AW's own dialect, or stand up a Postgres front-end that
imports the real tables as **foreign tables** via `oracle_fdw` / `tds_fdw`, then
build these views on top. The view **contracts** (the column set each must
expose) are identical whichever route you take.
---
## Approach 2 — ETL into the reference model (for bulk analytics / ML)
Compatibility views are ideal for **running our queries live** on modest result
sets. For **bulk analytics or training data** — where you want a full local copy
in the clean normalised shape — copy the data instead of viewing through it:
1. **Extract** — from a **read-replica** (never primary), or from `` /
AWMDS XML exports produced by the client's *Export XML File* path.
2. **Transform** — reshape to our schema using the *same* `FIT.md` /
`xml-messages.md` mapping. The adapter's `SELECT … AS …` lines are, in effect,
the transform written out.
3. **Load** — into a separate Postgres carrying our `asycuda` schema
([load it](../guides/loading.md)).
4. **Query the copy** — now every skill, notebook and feature pipeline runs
against a stable, normalised snapshot, decoupled from the live engine.
This is the path the [ML risk-engine guide](../guides/ml-risk-engine.md) assumes
for reading, feature-building and scoring at volume.
---
## The shape differences you must handle
Every divergence below is drawn from [`FIT.md`](../provenance/fit.md); the
adapter handles each one explicitly so your queries never have to.
| Aspect | Real ASYCUDA World | How the adapter handles it |
|--------|--------------------|----------------------------|
| **Table names** | terse codes — `GEN_TAB`, `BOL_TAB`, `SAD_General_Segment`, `SAD_Item`, `xxCTYTAB` | a view named like *our* table (`manifest`, `declaration`, `ref_country`) selects from each real source |
| **Coded fields** | code **and** name stored **inline** (`GEN_CAR_COD` + `GEN_CAR_NAM`) — no FK | `ref_*` views are **code-keyed** (`id` := the business code); operational views expose the inline code as our `*_id`, so `office_id = ref_customs_office.id` resolves on the code — no fabricated surrogate keys |
| **General segment repeated** | the general segment is copied into **every** `SAD_Item` row | the `declaration` header view **de-duplicates** (`SELECT DISTINCT ON (INSTANCE_ID)` / keyed header source) to one row per declaration |
| **Keys** | engine `INSTANCE_ID` / `InstanceId` | aliased **`AS id`** so our surrogate-PK joins keep working |
| **HS code** | split across `TAR_HSC_NB1..NB5` (national precision) | `declaration_item` **concatenates** the five parts into our single `hs_code` |
| **Reference validity** | `UN*`/`xx*TAB` rows carry `VALID_FROM` / `VALID_TO` | each `ref_*` view filters to current rows: `WHERE now()::date BETWEEN valid_from AND coalesce(valid_to, DATE '9999-12-31')` |
| **Taxes** | `SAD_Tax` with roots `COD`/`BSE`/`RAT`/`AMT`/`MOP`/`TYP` (+ `SAD_Ask_Tax`, `SAD_Global_Taxes`, `SAD_Tax_Totals`) | `declaration_tax_line` maps `COD→tax_type_id`, `BSE→tax_base`, `RAT→rate_percent`, `AMT→tax_amount`, `MOP→mode_of_payment`, `TYP→is_manual`; declaration/global totals stay **derived by query** |
---
## Privacy and safety
The adapter is read-only by construction, which is what makes it safe to point at
a database holding real declarations — trader TINs, invoice values, inspection
findings:
- **Views only** — no writes, no DDL against AW's own objects.
- Point **`CUSTOMS_DB`** at the real database, ideally via a **`SELECT`-only
role** and a **read-replica**. A grant snippet ships at the bottom of the SQL
template.
- The [`customs-query-tester`](../skills/index.md) then returns **metadata
only** — column names/types, an aggregate row count, duration — and *never*
row data. The model gets an oracle, not a window.
Set `CUSTOMS_DB` to the real DSN and `CUSTOMS_SCHEMA=asycuda` (or `compat` if you
created the views in a `compat` schema), and every skill behaves as it does on
the sandbox.
---
## What you must still obtain
The adapter is a faithful, well-annotated **contract** — but two things are
genuinely non-public and only your deployment can supply them:
1. **The real physical schema / column names** — the #1 gap. This is what turns
every `-- TODO(instance)` into a real name. Request it via the channels on
the [integration surfaces](integration.md) page.
2. **The deployment generation / version** — ASYCUDA++, World, or ASY5 — because
the physical names differ between them. Confirm it against
[version lineage](versions.md).
Once you have both, filling the template is mechanical, and the
`customs-query-tester` will confirm — **metadata only, no rows read** — that each
view's column set and join paths resolve against the live database.
---
## Related
- [Official fit & gap](../provenance/fit.md) — the table-by-table mapping the
crib is grounded in.
- [XML messages & the wire format](xml-messages.md) — the SAD box → AW XML tag →
our column three-way map.
- [Integration surfaces](integration.md) — the real doors, and what you must
request.
- [Version lineage](versions.md) — telling which generation a deployment runs.
- [Querying guide](../guides/querying.md) ·
[Useful queries](../guides/useful-queries.md) — the queries this adapter lets
you run live.
---
# Selectivity & clearance
This page describes how a declaration actually **moves through ASYCUDA World**:
the clearance **state machine** and the **four-lane risk routing** performed by
the selectivity module (internally **"Asysel"**). It is the platform behaviour.
For *our* tables — `ref_selectivity_lane`, `risk_criterion`,
`selectivity_result`, `inspection_act` — see
[Selectivity & risk](../schema/selectivity.md) in the schema section.
!!! note "Where these facts come from"
The lane model and criterion structure are grounded in public ASYCUDA
programme material and national brokers' manuals; the status model comes
chiefly from national declaration-processing manuals. The **Asysel admin data
model** (operators, priorities, score→lane thresholds) is deliberately **not
public** — kept hidden to prevent gaming. Everything below is reconstructed
from the public layer.
## The clearance state machine
The central object is the **SAD** (the declaration). It moves through a fixed
lifecycle, colour-coded in the ASYCUDA "Finder". Three statuses stamp a
reference with a serial prefix — the fingerprints you see on paperwork:
| Status | Reference prefix | Meaning |
|--------|:---------------:|---------|
| **STORED** | — | Captured, freely amendable before assessment |
| **REGISTERED** | **C** | Legal status + Customs Reference No. assigned |
| **ASSESSED** (liquidated) | **L** | Duties computed; amendments locked |
| **PAID** | **PRN** | ASYCUDA receipt issued (Payment Reference No.) |
| **SELECTED** | — | Red/Yellow held pending checks |
| **QUERIED** | — | Officer raises a question in the Inspection Act; broker responds |
| **RELEASED** | — | Checks done → Release Order (automatic for Green/Blue) |
| **EXITED** | — | Goods gate-out |
| **CANCELLED** | — | Assessment voided (supports refund) |
```mermaid
stateDiagram-v2
[*] --> STORED : Store
STORED --> STORED : Retrieve / modify
STORED --> REGISTERED : Validate / Register (C)
REGISTERED --> ASSESSED : Assess / liquidate (L)
ASSESSED --> PAID : Pay (PRN)
PAID --> routed : Trigger selectivity
ASSESSED --> routed : Trigger selectivity
state routed <>
routed --> RELEASED : GREEN / BLUE (auto)
routed --> SELECTED : YELLOW / RED (hold)
SELECTED --> SELECTED : Examine / doc-check (Inspection Act)
SELECTED --> QUERIED : Query
QUERIED --> SELECTED : Query response
SELECTED --> RELEASED : Clear (validate Inspection Act)
RELEASED --> EXITED : Gate-out
EXITED --> [*]
STORED --> CANCELLED : Cancel
REGISTERED --> CANCELLED : Cancel
ASSESSED --> CANCELLED : Cancel
CANCELLED --> [*]
```
The French vocabulary you will meet in SYDONIA installs runs in parallel:
*saisie → stockée → enregistrée → liquidée → acquittée → circuit → mainlevée /
BAE → sortie*. **AMENDED / RECTIFIED** is an event (retrieve + modify), not a
terminal state.
## The four lanes
Selectivity routes every declaration — **typically after assessment/payment** —
into one of four lanes, configured nationally by the Customs Risk Management
Unit. Legacy ASYCUDA++/SYDONIA had only three circuits (green/yellow/red);
**BLUE is an ASYCUDA World addition**.
| Lane | Requires exam | Meaning |
|------|:-------------:|---------|
| :material-circle:{ style="color:#16a34a" } **GREEN** | no | Auto-release; customs still reserves the right to examine |
| :material-circle:{ style="color:#ca8a04" } **YELLOW** | yes | Documentary check only |
| :material-circle:{ style="color:#dc2626" } **RED** | yes | Documentary check + physical examination; examiner completes the **Inspection Act** |
| :material-circle:{ style="color:#2563eb" } **BLUE** | no | Released now, **Post-Clearance Audit** verifies later |
Risk tiers commonly map Green (lowest) → Blue (low) → Yellow (medium) → Red
(high).
## The criteria model
A **criterion** is *"an instruction to control the content of some fields of the
declaration"* — a condition on fields mapped to a control channel (lane). Two
things make it powerful for ML integration:
- **Two-level scoring.** Criteria fire at **declaration level** *and* at
**trader level** — a profile keyed to the importer **TIN**. A strong trader
profile can override a declaration-level flag; AEOs go to Red only by
mandatory low-rate random selection.
- **Any element is usable.** *"All the data elements of the declaration and of
the B/L are usable by the selectivity"* — HS/tariff code, origin, importer /
exporter / declarant TINs, office, declared value versus a reference-price DB,
Incoterms, currency, goods description, Box 44 permit references, CPC, any
manifest / bill-of-lading field. This is what extends control to pre-arrival.
Two scoring generations coexist. **(A) Classic rule-based** — per-criterion
weights configured nationally (a typical scheme classes importer / origin /
tariff each low/med/high by fraud rate → Red if ≥1 high or ≥2 medium; Yellow if
1 medium; else Green). **(B) ML "Dynamic Selectivity"** (AW v4.4+, ~2021) —
UNCTAD's **native** ML component that *"assigns a score and the degree of
inspection"* from declarant / importer / origin, self-updating from inspection
feedback.
!!! tip "The random slot is the injection point"
A distinct **random selectivity** layer re-routes a percentage of green
declarations to red — commonly around **1–3%** — so procedures
stay unpredictable. A separate random function even assigns *which officer*
verifies (anti-collusion). **This random slot is the natural injection point
for an ML "exploration" strategy** — the exploitation/exploration split that
the [ML risk-engine guide](../guides/ml-risk-engine.md) builds on.
## When selectivity fires — the timing switch
!!! warning "This sets your ML scoring deadline"
*When* selectivity fires is a **per-country switch**. Documented African and
Caribbean deployments run selectivity **after assessment/payment**; ASYCUDA
also supports **selectivity before assessment**. Your integration must know
which mode the target runs — it changes the deadline by which your ML engine
must have scored the declaration. Confirm this before building anything
real-time. See the [ML risk-engine guide](../guides/ml-risk-engine.md#going-live).
## Mapping the platform to our model
Our schema represents most — but not all — of this behaviour. The honest map:
| Platform concept | Our model | Notes |
|------------------|-----------|-------|
| Status lifecycle (STORED…RELEASED) | `ref_declaration_status` rows; current on `declaration.status_id` | Statuses we store: **stored / registered / assessed / paid / released / queried / cancelled** |
| Status transitions over time | `declaration_status_history` (`status_id`, `changed_at`, `changed_by`) | The full audit trail of the state machine |
| The four lanes | `ref_selectivity_lane` (`code`, `requires_exam`) | Green / Yellow / Red / Blue |
| A criterion → target lane | `risk_criterion` (`code`, `name`, `target_lane_id`) | Criterion *operators / priorities / weights* are **not modelled** — they are not public |
| The lane a declaration was routed to, and why | `selectivity_result` (`lane_id`, `criterion_id`, `triggered_at`, `officer_id`); current lane cached on `declaration.selectivity_lane_id` | Preserves the routing history and reason |
| Examiner's outcome (the ML label) | `inspection_act` (`result`, `findings`, `inspected_at`, `officer_id`) | The feedback signal for the learning loop |
| Payment / receipt (PRN) | `payment`, `receipt` | The PAID transition |
### Two honest gaps
The full clearance state machine has two states our status catalogue does **not**
carry as `ref_declaration_status` rows:
!!! warning "SELECTED and EXITED are not statuses in our model"
- **SELECTED** — the "held pending checks" state — has **no direct status
row**. It *is* representable indirectly: a row in `selectivity_result`
routing the declaration to Yellow/Red, plus (optionally) an
`inspection_act`, expresses "this declaration was selected". The
declaration's own `status_id` stays at its last true status
(e.g. `paid`) until it moves to `released`.
- **EXITED** — the physical **gate-out** — is **not modelled at all**. Our
lifecycle ends at `released`; there is no exit / gate event table and no
`exited` status. If you need to model goods leaving the premises, you must
[extend the schema](../guides/extending.md) yourself.
State these gaps plainly in any analysis — do not treat a `released`
declaration as evidence the goods have physically exited.
## Next
- :material-robot-outline:{ .lg .middle } **Build the loop**
---
Turn this behaviour into a working risk engine — features, labels, the
read → score → inject → feedback loop, and how to prototype on this schema.
[:octicons-arrow-right-24: ML on customs data](../guides/ml-risk-engine.md)
- :material-connection:{ .lg .middle } **The doors**
---
Where an external engine actually plugs in — RDBMS/ETL, ASYHUB,
Cargo-XML, ASY5 — and the specs you must request.
[:octicons-arrow-right-24: Integration surfaces](integration.md)
---
# Further reading & document map
UNCTAD does not publish ASYCUDA's physical database schema or a machine-readable
API catalog. The field-level model is public **only through the XML layer and
national customs manuals** — so knowing *which kinds* of documents exist, and
where they normally live, is half the battle. This page describes the shape of
that landscape without pointing at any one document.
!!! note "This complements the schema's own citations"
[Sources](../provenance/sources.md) lists the documents our schema **actually
cites** (each resolving to a `-- src:` tag). *This* page is broader: a general
guide to the kinds of public reading that help you understand ASYCUDA the
platform, not just our reconstruction of it.
## The kinds of documentation, and where they live
Understanding ASYCUDA from public material means drawing on a few recurring
categories. None of them is a single authoritative schema — together they are the
public window onto the model.
### Public programme materials
The ASYCUDA programme publishes introductory and processing manuals, message
specifications and general platform documentation. These describe the SAD
workflow, the XML e-document model and the overall system at a conceptual level.
They are the canonical starting point for what the platform *is*.
### National customs manuals — the field-level goldmines
ASYCUDA World is deployed as a national instance, and each administration
documents its own instance. These national user guides, broker manuals and SAD
field guides define the declaration boxes and fields concretely — often in more
practical detail than the general programme material — because they describe the
system as traders and officers actually use it. They are typically hosted on the
national customs administration's own portal.
### Real schemas, samples & code
The most useful public artefacts for the actual XML wire format are the real
instances themselves: exported declaration and manifest XML, recovered schema
definitions, and integrator code that reads or writes the `` format.
These show what real instances export, as opposed to the idealised specification.
### Open standards
The surrounding standards are fully public and stable: ISO country/currency
codes, the UN trade and transport code lists, the WCO Harmonized System and Data
Model, Incoterms, and the WTO valuation framework. ASYCUDA aligns to these, so
they are load-bearing background reading in their own right.
### Public customs-ML research and open datasets
For the analytics and risk-engine side, there is a body of public customs
machine-learning research and a small number of open, downloadable customs
datasets. These describe feature schemas, modelling approaches and evaluation
methods generically — enough to prototype a risk model before you have access to
real declaration history. See the [ML risk-engine guide](../guides/ml-risk-engine.md)
for how that research shapes the blueprint.
## Restricted — request via your national customs or the ASYCUDA programme
The load-bearing technical documents are **not public**. The physical database
schema / ERD, the ASYHUB API specification, the ASY5 risk-signal payload format,
the selectivity admin data model, and Inspection-Act read access are all obtained
by request — through your national customs administration's ASYCUDA project team
or the UNCTAD ASYCUDA programme. Gated learning platforms and credentialed
developer repositories exist for member customs administrations but are not open
to the public.
What to put on the request list:
| Request | Why it matters |
|---------|----------------|
| Physical **DB schema / ERD** | The biggest gap — needed for training-data extraction and selectivity write-back |
| **ASYHUB API specification** (OpenAPI/WSDL, auth, message catalog) | The single most important unknown for sanctioned real-time integration |
| **ASY5 "third-party AI → risk profile" signal payload format & endpoint** | The forward-looking injection point; format not yet public |
| **Selectivity admin data model** (operators, priority, validity, score→lane thresholds) | Deliberately hidden to prevent gaming |
| **Inspection-Act read access** (illicit flag + recovered revenue) | The ML labels and the feedback loop depend on it |
---
# Guides
Task-oriented walkthroughs for working with the model day to day.
!!! tip "Querying a real ASYCUDA World system?"
These guides use the friendly **logical model**. To run the same queries on a
live Sydonia, see **[Querying Sydonia](../querying-sydonia/index.md)** (the
real tables) and **[the query compiler](../compiler/index.md)** (write
friendly, run genuine).
- :material-database-arrow-down:{ .lg .middle } **Loading the schema**
---
Integration patterns beyond the quickstart — CI, test resets, loading
alongside your own tables, and namespacing.
[:octicons-arrow-right-24: Loading](loading.md)
- :material-magnify:{ .lg .middle } **Querying the model**
---
The join paths that matter, plus an analytics cookbook: revenue by HS,
assessed-vs-paid, lane throughput, warehouse stock.
[:octicons-arrow-right-24: Querying](querying.md)
- :material-source-branch:{ .lg .middle } **Extending the schema**
---
Add tables and columns while keeping the conventions — and the provenance
trail — intact.
[:octicons-arrow-right-24: Extending](extending.md)
- :material-play-box:{ .lg .middle } **Worked example**
---
`e2e.sql`, narrated step by step: manifest → declaration → valuation →
taxes → selectivity → payment → release.
[:octicons-arrow-right-24: Worked example](worked-example.md)
- :material-table-search:{ .lg .middle } **Useful queries**
---
A growing, verified library of analytical queries — effective rates,
valuation outliers, selectivity hit-rates — with a copy-paste format.
[:octicons-arrow-right-24: Useful queries](useful-queries.md)
- :material-brain:{ .lg .middle } **ML on customs data**
---
The risk-engine blueprint: features mapped to SAD boxes and columns, labels
from the Inspection Act, the selectivity loop.
[:octicons-arrow-right-24: ML on customs data](ml-risk-engine.md)
---
# Loading the schema
The [installation page](../getting-started/installation.md) covers first-time
setup. This guide is about **integrating loading into a workflow** — tests, CI,
and living alongside your application's own tables.
## Load order (always)
```text
1. Sydonia/schema/asycuda.sql # schema + tables (drops & recreates `asycuda`)
2. Sydonia/schema/seed_reference.sql # reference / code data
3. Sydonia/examples/e2e.sql # optional worked example
```
`asycuda.sql` begins with `DROP SCHEMA IF EXISTS asycuda CASCADE;`, so **every
load is a clean reset** of the `asycuda` namespace.
## Living alongside your own tables
Because the model lives entirely in the `asycuda` schema, it coexists with your
application. Keep your tables in `public` (or your own schema); reference the
model explicitly or via `search_path`:
```sql
-- your app tables in public, the customs model in asycuda
SELECT d.registration_number, my.note
FROM asycuda.declaration d
JOIN public.my_annotations my ON my.declaration_id = d.id;
```
!!! warning "Reloading discards `asycuda` contents"
Re-running `asycuda.sql` drops the whole `asycuda` schema. Never put data you
want to keep **inside** `asycuda` — keep it in your own schema and reference
across.
## Reset between tests
The idempotent load makes per-suite resets trivial:
```bash
# fast reset: reload schema + seed only (skip the e2e example)
psql -v ON_ERROR_STOP=1 -d "$TEST_DB" -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d "$TEST_DB" -f Sydonia/schema/seed_reference.sql
```
For many fast resets, `DROP SCHEMA asycuda CASCADE;` + reload is cheaper than
recreating the database.
## Continuous integration
A minimal GitHub Actions job that proves a clean load on every push:
```yaml
jobs:
load:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env: { POSTGRES_PASSWORD: postgres }
ports: ['5432:5432']
options: >-
--health-cmd pg_isready --health-interval 10s
--health-timeout 5s --health-retries 5
env:
PGPASSWORD: postgres
PSQL: psql -v ON_ERROR_STOP=1 -h localhost -U postgres -d customs
steps:
- uses: actions/checkout@v4
- run: createdb -h localhost -U postgres customs
- run: $PSQL -f Sydonia/schema/asycuda.sql
- run: $PSQL -f Sydonia/schema/seed_reference.sql
- run: $PSQL -f Sydonia/examples/e2e.sql
```
The [`customs-validate`](../skills/index.md) skill wraps exactly this check for
local use.
## Loading into a specific schema name
Prefer a different namespace? The model hardcodes `asycuda`, but you can rename
after loading, or `sed` the three `asycuda` references at the top of the file:
```bash
sed 's/\basycuda\b/customs/g' Sydonia/schema/asycuda.sql | psql -v ON_ERROR_STOP=1 -d mydb -f -
# remember to apply the same rename to seed_reference.sql and e2e.sql (their SET search_path lines)
```
## Verify the load programmatically
```sql
SET search_path TO asycuda, public;
SELECT
(SELECT count(*) FROM information_schema.tables
WHERE table_schema='asycuda' AND table_type='BASE TABLE') AS tables, -- 55
(SELECT count(*) FROM ref_transport_mode) AS transport_modes, -- 9
(SELECT count(*) FROM ref_selectivity_lane) AS lanes; -- 4
```
---
# Querying the model
The model is normalised, so coded values are foreign keys and totals are derived
by query. This page is the cookbook of join paths you will reach for.
!!! tip "Write here, run on a real Sydonia"
Every query on this page is written against the friendly **logical model**.
To run the same SQL on a live ASYCUDA World database, pipe it through the
[query compiler](../compiler/index.md) — it rewrites the friendly names into
genuine Sydonia SQL over the real tables. See [Querying
Sydonia](../querying-sydonia/index.md) for the real tables themselves.
!!! note "Set the search path first"
Every table is in the `asycuda` schema. Run this once per session (or
`ALTER DATABASE … SET search_path`) so you can use bare names:
```sql
SET search_path TO asycuda, public;
```
## The two spines
Almost every query walks one of two chains:
```text
manifest → bill_of_lading → container / manifest_cargo_item (cargo spine)
declaration → declaration_item → declaration_tax_line (declaration spine)
↘ item_value_note (per-item CIF)
```
## Assemble a full declaration
Resolve the coded foreign keys into human-readable columns:
```sql
SELECT d.registration_serial || ' ' || d.registration_number AS reg,
ty.code AS type,
off.name AS office,
exp.name AS exporter,
imp.name AS consignee,
st.code AS status,
lane.code AS lane,
d.total_cif_value
FROM declaration d
JOIN ref_declaration_type ty ON ty.id = d.declaration_type_id
JOIN ref_customs_office off ON off.id = d.office_id
JOIN trader exp ON exp.id = d.exporter_id
JOIN trader imp ON imp.id = d.consignee_id
JOIN ref_declaration_status st ON st.id = d.status_id
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id
WHERE d.trader_reference = 'REF-2026-0001';
```
## Items with their taxes
```sql
SELECT di.item_number,
di.hs_code,
di.customs_value,
jsonb_object_agg(tt.code, tl.tax_amount) AS taxes,
sum(tl.tax_amount) AS total_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
JOIN ref_tax_type tt ON tt.id = tl.tax_type_id
WHERE di.declaration_id = (SELECT id FROM declaration WHERE trader_reference='REF-2026-0001')
GROUP BY di.item_number, di.hs_code, di.customs_value
ORDER BY di.item_number;
```
## Cargo spine — manifest to goods lines
```sql
SELECT m.voyage_number,
bl.bl_reference,
ctn.ctn_reference,
ci.hs_code,
ci.goods_description,
ci.number_of_packages
FROM manifest m
JOIN bill_of_lading bl ON bl.manifest_id = m.id
LEFT JOIN container ctn ON ctn.bl_id = bl.id
JOIN manifest_cargo_item ci ON ci.bl_id = bl.id
WHERE m.voyage_number = 'V2026-042'
ORDER BY bl.line_number, ci.line_number;
```
## Assessed vs paid reconciliation
The single most useful integrity query — do the taxes, the payment and the
receipt agree?
```sql
SELECT d.registration_number,
assessed.total_tax,
p.amount AS paid,
r.total_amount AS receipted,
assessed.total_tax = coalesce(r.total_amount, 0) AS balanced
FROM declaration d
JOIN LATERAL (
SELECT sum(tl.tax_amount) AS total_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
WHERE di.declaration_id = d.id
) assessed ON true
LEFT JOIN payment p ON p.declaration_id = d.id
LEFT JOIN receipt r ON r.payment_id = p.id;
```
## Revenue by tax type
```sql
SELECT tt.code AS tax,
count(*) AS lines,
sum(tl.tax_amount) AS revenue
FROM declaration_tax_line tl
JOIN ref_tax_type tt ON tt.id = tl.tax_type_id
GROUP BY tt.code
ORDER BY revenue DESC;
```
## Revenue by HS chapter
```sql
SELECT left(di.hs_code, 2) AS hs_chapter,
sum(di.customs_value) AS declared_value,
sum(tl.tax_amount) AS revenue
FROM declaration_item di
LEFT JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY 1
ORDER BY revenue DESC NULLS LAST;
```
## Selectivity throughput
```sql
SELECT lane.code AS lane,
count(*) AS declarations,
count(*) FILTER (WHERE ia.result = 'conform') AS conform,
round(100.0 * count(ia.id) / count(*), 1) AS pct_inspected
FROM declaration d
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id
LEFT JOIN inspection_act ia ON ia.declaration_id = d.id
GROUP BY lane.code
ORDER BY lane.code;
```
## Trace a write-off to the manifest
Which manifest bill of lading did each declared item come from?
```sql
SELECT d.registration_number,
di.item_number,
di.hs_code,
bl.bl_reference,
pd.written_off_packages,
pd.written_off_mass
FROM declaration_previous_document pd
JOIN declaration d ON d.id = pd.declaration_id
JOIN declaration_item di ON di.id = pd.declaration_item_id
JOIN bill_of_lading bl ON bl.id = pd.bl_id
ORDER BY d.registration_number, di.item_number;
```
## Goods still in a warehouse
```sql
SELECT w.code AS warehouse, we.entry_date, we.packages, we.gross_mass
FROM warehouse_entry we
JOIN ref_warehouse w ON w.id = we.warehouse_id
LEFT JOIN warehouse_exit wx ON wx.warehouse_entry_id = we.id
WHERE wx.id IS NULL
ORDER BY we.entry_date;
```
## Save the common joins as views
If you query the model a lot, wrap the resolved joins in a view:
```sql
CREATE VIEW asycuda.v_declaration_summary AS
SELECT d.id,
d.registration_serial || ' ' || d.registration_number AS reg,
ty.code AS type, st.code AS status, lane.code AS lane,
d.total_items, d.total_cif_value
FROM declaration d
JOIN ref_declaration_type ty ON ty.id = d.declaration_type_id
JOIN ref_declaration_status st ON st.id = d.status_id
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id;
```
!!! tip "Let an Agent Skill write the joins"
The [`customs-query`](../skills/index.md) skill knows these join paths and the
`search_path` requirement, so you can ask for a report in plain English and get
a correct query back.
---
# Useful queries
The [querying guide](querying.md) teaches the join paths — the two spines, how
coded columns resolve to `ref_*` names, why totals are derived rather than
stored. This page is the next step: a **growing library of ready analytical
reports** built on those paths, each verified against the seeded sandbox. Add
yours by copying the template.
!!! note "Set the search path once"
Every table lives in the `asycuda` schema. Run this once per session so bare
table names resolve. Each fenced block below repeats it to stay
copy-paste self-contained.
```sql
SET search_path TO asycuda, public;
```
!!! tip "These are logical queries — compile them to genuine Sydonia SQL"
Every query here is written against the friendly **logical model**. To run the
same SQL on a real ASYCUDA World instance, pipe it through the
[**query compiler**](../compiler/index.md): it rewrites each into genuine
Sydonia SQL over the real physical tables. Write friendly, run genuine.
!!! abstract "Built for analytics, ML and selectivity"
These are more than dashboards. The `risk`, `valuation` and `trader` queries
below are the **feature-extraction and selectivity-analytics building blocks**
for a model — effective rates, unit-price outliers, importer discrepancy
profiles, criterion hit-rates. See the
[ML risk-engine guide](ml-risk-engine.md) for how these features feed a
fraud / valuation model and the clearance lanes, and
[running on a real ASYCUDA World](../platform/running-on-real-asycuda.md) to
run them on *your own* declarations.
## The entry format
Every query is one **entry** with a fixed shape — keep the headings and their
order exactly:
````markdown
### { #stable-slug }
**Intent:** one line — what question it answers.
**Tags:** `revenue` · `hs` · `trader` (pick from the vocabulary below)
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
SELECT …;
```
**How it works:** 2–4 sentences on the join path and any windowing.
**Variations:** optional bullet list of tweaks.
````
### The tag vocabulary
Pick one or more from this closed set; add a new tag here first if you truly
need one, so the vocabulary stays the single source of truth.
| Tag | Use it for |
|-----|------------|
| `revenue` | duty, VAT and fee amounts; fiscal totals and shares |
| `valuation` | customs value, CIF build-up, unit prices, value integrity |
| `risk` | selectivity lanes, criteria, inspection outcomes |
| `trader` | importer, exporter, declarant/broker analytics |
| `manifest` | cargo, bills of lading, write-offs, reconciliation |
| `quality` | data-integrity checks that should return **zero rows** |
| `time` | dwell times, lifecycle durations, throughput over time |
| `suspense` | warehousing, transit, temporary admission, guarantees |
### To add a query
1. Copy the template block above into the right `## ` category (or start a new one).
2. Keep the four bold headings and the fenced `sql` block; write an imperative title and a stable `{ #slug }`.
3. Run it read-only against your sandbox and fix any error before you commit it:
```bash
PGOPTIONS='-c default_transaction_read_only=on -c search_path=asycuda,public' \
psql -X -d customs_sandbox -c ""
```
4. Only then tick **Verified: ✓**. An unverified query does not belong in the library.
!!! tip "Integrity checks are supposed to return nothing"
A `quality`-tagged query that returns **zero rows** on clean data has
passed — it found no defects. That is the expected result, not an empty
report.
---
## Revenue & fiscal
### Effective duty rate by HS chapter { #effective-rate-by-chapter }
**Intent:** what proportion of declared customs value is actually collected as tax, grouped by tariff chapter.
**Tags:** `revenue` · `valuation`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH item_tax AS ( -- collapse an item's tax lines to one row first
SELECT di.id, left(di.hs_code, 2) AS hs_chapter,
di.customs_value, sum(tl.tax_amount) AS item_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY di.id, di.hs_code, di.customs_value
)
SELECT hs_chapter,
sum(customs_value) AS customs_value,
sum(item_tax) AS total_tax,
round(100.0 * sum(item_tax) / nullif(sum(customs_value), 0), 2) AS effective_rate_pct
FROM item_tax
GROUP BY hs_chapter
ORDER BY effective_rate_pct DESC NULLS LAST;
```
**How it works:** The inner CTE collapses an item's several tax lines to one row *first* — otherwise summing `customs_value` after the tax join double-counts the value once per tax line. Only then is value and tax rolled up per chapter and divided.
**Variations:** group by `di.hs_id` + join `ref_hs_tariff` for the full code; filter `WHERE tt.code = 'IMP'` for a duty-only rate.
### Rank revenue concentration with a running share { #revenue-concentration }
**Intent:** which HS chapters carry the revenue, and how few of them make up most of it (Pareto view).
**Tags:** `revenue` · `hs`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH chapter_rev AS (
SELECT left(di.hs_code, 2) AS hs_chapter,
sum(tl.tax_amount) AS revenue
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY 1
)
SELECT hs_chapter,
revenue,
round(100.0 * revenue / sum(revenue) OVER (), 1) AS pct_of_total,
round(100.0 * sum(revenue) OVER (ORDER BY revenue DESC)
/ sum(revenue) OVER (), 1) AS running_pct
FROM chapter_rev
ORDER BY revenue DESC;
```
**How it works:** Two window frames over the same result: `SUM() OVER ()` gives the grand total for each row's share, and `SUM() OVER (ORDER BY revenue DESC)` accumulates a running total down the ranking, reaching 100 at the last row.
**Variations:** swap the chapter expression for `d.consignee_id` (join `trader`) to concentrate by importer; wrap with `WHERE running_pct <= 80` for the vital-few chapters.
### Split duty, VAT and other taxes per office { #duty-vat-split }
**Intent:** how each customs office's collection breaks down between import duty, VAT and everything else.
**Tags:** `revenue`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
SELECT off.office_code,
off.name AS office,
sum(tl.tax_amount) FILTER (WHERE tt.code = 'IMP') AS import_duty,
sum(tl.tax_amount) FILTER (WHERE tt.code = 'VAT') AS vat,
sum(tl.tax_amount) FILTER (WHERE tt.code NOT IN ('IMP','VAT')) AS other_taxes,
round(100.0 * sum(tl.tax_amount) FILTER (WHERE tt.code = 'VAT')
/ nullif(sum(tl.tax_amount), 0), 1) AS vat_share_pct
FROM declaration d
JOIN ref_customs_office off ON off.id = d.office_id
JOIN declaration_item di ON di.declaration_id = d.id
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
JOIN ref_tax_type tt ON tt.id = tl.tax_type_id
GROUP BY off.office_code, off.name
ORDER BY off.office_code;
```
**How it works:** `FILTER (WHERE …)` turns one pass over the tax lines into several conditional sums, so duty, VAT and residual taxes land in separate columns without a self-join. Codes come from `ref_tax_type`.
**Variations:** add `d.registration_date` to the grouping for a monthly breakdown per office.
## Valuation quality
### Flag unit-price outliers against the per-HS median { #unit-price-outliers }
**Intent:** which item lines are priced far from the typical value-per-kilo for their HS code — a classic under-valuation signal.
**Tags:** `valuation` · `risk`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH unit AS (
SELECT di.id, di.hs_code,
(di.customs_value / nullif(di.net_mass, 0))::numeric AS unit_price
FROM declaration_item di
WHERE di.net_mass > 0 AND di.customs_value IS NOT NULL
),
hs_median AS ( -- percentile_cont is an ordered-set aggregate, not a window fn
SELECT hs_code,
percentile_cont(0.5) WITHIN GROUP (ORDER BY unit_price)::numeric AS median_unit_price
FROM unit GROUP BY hs_code
)
SELECT u.hs_code,
round(u.unit_price, 4) AS unit_price,
round(m.median_unit_price, 4) AS hs_median,
round(100.0 * (u.unit_price - m.median_unit_price)
/ nullif(m.median_unit_price, 0), 1) AS deviation_pct
FROM unit u
JOIN hs_median m ON m.hs_code = u.hs_code
ORDER BY abs(u.unit_price - m.median_unit_price) DESC;
```
**How it works:** The first CTE derives a value-per-kilo per item; the second computes the median per HS code. `percentile_cont` is an ordered-set aggregate — it cannot be a window function, so the median is grouped in its own CTE and joined back. Rows are ordered by absolute deviation, largest first.
**Variations:** filter `WHERE abs(deviation_pct) > 40` for strong outliers only; group the median by `left(hs_code, 4)` (heading) for thin datasets.
### Reconcile item customs value against the valuation note { #value-vs-valuation-note }
**Intent:** find item lines whose declared `customs_value` disagrees with the per-item CIF built up in the valuation note.
**Tags:** `valuation` · `quality`
**Verified:** ✓ against the seeded sandbox — returns zero rows on clean data
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number, di.item_number, di.hs_code,
di.customs_value, ivn.item_cif,
(di.customs_value - ivn.item_cif) AS difference
FROM declaration_item di
JOIN declaration d ON d.id = di.declaration_id
JOIN item_value_note ivn ON ivn.declaration_item_id = di.id
WHERE di.customs_value IS DISTINCT FROM ivn.item_cif
ORDER BY abs(di.customs_value - ivn.item_cif) DESC;
```
**How it works:** `item_value_note.item_cif` is the freight-and-insurance-apportioned value that *should* equal the item's `customs_value`. `IS DISTINCT FROM` treats NULLs safely, so a missing value on either side surfaces rather than hides. Any row returned is an integrity break to investigate.
## Risk & selectivity
### Measure criterion hit-rate and discrepancy rate { #criterion-hit-rate }
**Intent:** for each risk criterion, how often it fires, how often that leads to inspection, and how often inspection finds a discrepancy.
**Tags:** `risk`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
SELECT rc.code AS criterion,
rc.name,
count(sr.id) AS times_triggered,
count(ia.id) AS inspections,
count(ia.id) FILTER (WHERE ia.result = 'discrepancy') AS discrepancies,
round(100.0 * count(ia.id) FILTER (WHERE ia.result = 'discrepancy')
/ nullif(count(ia.id), 0), 1) AS discrepancy_rate_pct
FROM risk_criterion rc
LEFT JOIN selectivity_result sr ON sr.criterion_id = rc.id
LEFT JOIN inspection_act ia ON ia.declaration_id = sr.declaration_id
GROUP BY rc.code, rc.name
ORDER BY discrepancy_rate_pct DESC NULLS LAST, times_triggered DESC;
```
**How it works:** Walks `risk_criterion → selectivity_result` (each firing) then to the `inspection_act` of the same declaration; `LEFT JOIN` keeps criteria that never fired. A high discrepancy rate on a frequently-firing criterion is well targeted; one that fires often but finds nothing is a candidate to retire.
### Profile importers by discrepancy history { #trader-discrepancy-profile }
**Intent:** which importers have a track record of inspection discrepancies — a signal to feed back into risk profiling.
**Tags:** `risk` · `trader`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
SELECT tr.tin,
tr.name AS importer,
count(DISTINCT d.id) AS declarations,
count(DISTINCT ia.declaration_id) AS inspected,
count(ia.id) FILTER (WHERE ia.result = 'discrepancy') AS discrepancies,
round(100.0 * count(ia.id) FILTER (WHERE ia.result = 'discrepancy')
/ nullif(count(ia.id), 0), 1) AS discrepancy_rate_pct
FROM declaration d
JOIN trader tr ON tr.id = d.consignee_id
LEFT JOIN inspection_act ia ON ia.declaration_id = d.id
GROUP BY tr.tin, tr.name
HAVING count(ia.id) > 0
ORDER BY discrepancy_rate_pct DESC, declarations DESC;
```
**How it works:** Joins each declaration to its consignee (`consignee_id → trader`) and to any inspection acts. `HAVING count(ia.id) > 0` restricts to importers actually inspected, so the rate is meaningful; the sort brings the worst offenders to the top.
## Trader analytics
### Build the importer league table { #importer-league-table }
**Intent:** rank importers by volume, total customs value and total tax paid.
**Tags:** `trader` · `revenue`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH decl_tax AS (
SELECT di.declaration_id, sum(tl.tax_amount) AS decl_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY di.declaration_id
)
SELECT tr.tin,
tr.name AS importer,
count(DISTINCT d.id) AS declarations,
sum(d.total_cif_value) AS total_cif,
sum(dt.decl_tax) AS total_tax
FROM declaration d
JOIN trader tr ON tr.id = d.consignee_id
LEFT JOIN decl_tax dt ON dt.declaration_id = d.id
GROUP BY tr.tin, tr.name
ORDER BY total_tax DESC NULLS LAST;
```
**How it works:** Tax is pre-aggregated to one row per declaration in the CTE *before* the join, so summing per importer never multiplies by the item count. `total_cif_value` is the stored declaration total, safe to sum directly.
**Variations:** swap `d.consignee_id` for `d.exporter_id` to rank exporters instead.
### Rank broker (declarant) activity { #declarant-activity }
**Intent:** how much clearance work each broker handles, and across how many distinct importers.
**Tags:** `trader`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
SELECT br.tin,
br.name AS declarant,
count(*) AS declarations_lodged,
count(DISTINCT d.consignee_id) AS distinct_importers,
sum(d.total_cif_value) AS total_cif_handled
FROM declaration d
JOIN trader br ON br.id = d.declarant_id
GROUP BY br.tin, br.name
ORDER BY declarations_lodged DESC;
```
**How it works:** The declarant (SAD box 14) is a `trader` in its own right, referenced by `declaration.declarant_id`. Counting distinct `consignee_id` shows whether a broker serves one client or many.
## Manifest reconciliation
### Reconcile manifested against written-off packages { #manifest-writeoff-reconciliation }
**Intent:** per bill of lading, compare the packages the carrier manifested with the packages actually written off on declarations — surfacing under- or over-declaration.
**Tags:** `manifest` · `quality`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH manifested AS (
SELECT bl.id AS bl_id, bl.bl_reference,
sum(ci.number_of_packages) AS manifested_packages
FROM bill_of_lading bl
JOIN manifest_cargo_item ci ON ci.bl_id = bl.id
GROUP BY bl.id, bl.bl_reference
),
written_off AS (
SELECT pd.bl_id, sum(pd.written_off_packages) AS written_off_packages
FROM declaration_previous_document pd
WHERE pd.bl_id IS NOT NULL GROUP BY pd.bl_id
)
SELECT m.bl_reference,
m.manifested_packages,
coalesce(w.written_off_packages, 0) AS written_off_packages,
m.manifested_packages - coalesce(w.written_off_packages, 0) AS remaining_packages
FROM manifested m
LEFT JOIN written_off w ON w.bl_id = m.bl_id
ORDER BY remaining_packages DESC;
```
**How it works:** One CTE totals the cargo lines per B/L; the other totals what declarations wrote off against it via `declaration_previous_document` (SAD box 40). `remaining_packages` above zero is cargo not yet cleared; below zero means more was declared than manifested — an anomaly.
## Lifecycle timing
### Measure dwell time between status transitions { #status-dwell-time }
**Intent:** how long a declaration sits at each stage — registered → assessed → paid → released.
**Tags:** `time`
**Verified:** ✓ against the seeded sandbox
```sql
SET search_path TO asycuda, public;
WITH trail AS (
SELECT d.registration_number, st.code AS status, h.changed_at,
lead(st.code) OVER w AS next_status,
lead(h.changed_at) OVER w AS next_changed_at
FROM declaration_status_history h
JOIN declaration d ON d.id = h.declaration_id
JOIN ref_declaration_status st ON st.id = h.status_id
WINDOW w AS (PARTITION BY h.declaration_id ORDER BY h.changed_at)
)
SELECT registration_number,
status || ' -> ' || next_status AS transition,
changed_at, next_changed_at - changed_at AS dwell
FROM trail
WHERE next_status IS NOT NULL
ORDER BY registration_number, changed_at;
```
**How it works:** `lead()` looks one row ahead within each declaration's history (ordered by `changed_at`), so every row knows its successor status and time. The interval between them is the dwell at the current stage; the final status has no successor and drops out.
**Variations:** aggregate `avg(next_changed_at - changed_at)` grouped by the transition for a mean dwell per stage across all declarations.
## Data quality
### List item lines carrying no tax { #items-without-tax }
**Intent:** find declaration items with no tax line at all — usually a data-entry gap rather than a genuine zero-rating.
**Tags:** `quality`
**Verified:** ✓ against the seeded sandbox — returns zero rows on clean data
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number, di.item_number, di.hs_code, di.customs_value
FROM declaration_item di
JOIN declaration d ON d.id = di.declaration_id
WHERE NOT EXISTS (
SELECT 1 FROM declaration_tax_line tl WHERE tl.declaration_item_id = di.id
)
ORDER BY d.registration_number, di.item_number;
```
**How it works:** An anti-join with `NOT EXISTS` keeps only items with no matching tax line. Genuine relief should still show a zero-amount line with an exemption code, so a truly empty item is worth a second look.
### Catch assessed tax that does not match the receipt { #assessed-vs-receipted }
**Intent:** flag declarations where the sum of tax lines differs from the amount actually receipted.
**Tags:** `quality` · `revenue`
**Verified:** ✓ against the seeded sandbox — returns zero rows on clean data
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number, assessed.total_tax,
r.total_amount AS receipted,
assessed.total_tax - coalesce(r.total_amount, 0) AS variance
FROM declaration d
JOIN LATERAL (
SELECT sum(tl.tax_amount) AS total_tax
FROM declaration_item di
JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
WHERE di.declaration_id = d.id
) assessed ON true
LEFT JOIN payment p ON p.declaration_id = d.id
LEFT JOIN receipt r ON r.payment_id = p.id
WHERE assessed.total_tax IS DISTINCT FROM coalesce(r.total_amount, 0)
ORDER BY abs(assessed.total_tax - coalesce(r.total_amount, 0)) DESC;
```
**How it works:** A `LATERAL` subquery totals the tax per declaration; the payment and receipt are joined in. This is the sibling of the querying guide's assessed-vs-paid check, but *filtered* to keep only the mismatches — a focused exception report rather than a full reconciliation.
### Find released declarations missing a required inspection act { #released-without-inspection }
**Intent:** released declarations on an exam-requiring lane that have no inspection act on file — a control gap.
**Tags:** `quality` · `risk`
**Verified:** ✓ against the seeded sandbox — returns zero rows on clean data
```sql
SET search_path TO asycuda, public;
SELECT d.registration_number, lane.code AS lane, st.code AS status
FROM declaration d
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id
JOIN ref_declaration_status st ON st.id = d.status_id
WHERE lane.requires_exam
AND st.code = 'released'
AND NOT EXISTS (
SELECT 1 FROM inspection_act ia WHERE ia.declaration_id = d.id
)
ORDER BY d.registration_number;
```
**How it works:** If a lane's `requires_exam` is true, release should be preceded by an `inspection_act`. The `NOT EXISTS` anti-join returns any declaration that reached `released` without one — a procedural breach worth auditing.
---
## Run these on a real ASYCUDA World system
Everything here targets the toolbox's normalised `asycuda` schema, not the real
ASYCUDA World physical tables. To point the same SQL at a live system, run it
through the compatibility-view adapter in
[running on real ASYCUDA](../platform/running-on-real-asycuda.md) — the views
expose these table and column names over the real store, so the queries above
run unchanged.
## Test before you trust
Before adding a query here — or running one you did not write against a
production database — validate it. The [`customs-query`](../skills/index.md)
skill tests SQL through the customs-query-tester MCP, and the bundled
`skills/customs-query/scripts/test_query.sh` does the same from the shell. Both
are **privacy-preserving**: they check the query against schema metadata and a
read-only plan, never returning row data — safe against databases holding real
customs declarations.
---
# Extending the schema
The model is a starting point. When you add to it, keep two things intact: the
**conventions** (so it stays consistent) and the **provenance trail** (so your
fork stays as auditable as the original).
## The conventions, as a checklist
When adding a table or column:
- [ ] Live in the `asycuda` schema (the load already `SET search_path`).
- [ ] `snake_case` names; `ref_` prefix for code tables, `sys_` for system/RBAC.
- [ ] Surrogate PK: `bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY`.
- [ ] Keep real business codes as `UNIQUE NOT NULL` (don't rely on the surrogate).
- [ ] Coded columns are a **foreign key to a `ref_*` table**, not an inline
code+name pair.
- [ ] Money `numeric(18,4)`; mass/quantity `numeric(18,3)`; dates `date` /
`timestamptz`; flags real `boolean`.
- [ ] A status lifecycle → a `ref_*_status` table + a `*_status_history` child.
- [ ] `COMMENT ON` anything non-obvious.
- [ ] **Tag provenance** on the `CREATE TABLE` (see below).
## Provenance is the rule that makes this project trustworthy
Every `CREATE TABLE` carries exactly one provenance tag on the line above it:
```sql
-- src: S014, S003 (cite the SOURCES.md IDs the table is grounded in)
CREATE TABLE declaration_item ( ... );
-- inferred (introduced by your own modelling judgement)
CREATE TABLE trader_role ( ... );
```
The rule from the project goal is worth repeating:
!!! quote
A larger honest **inferred** set beats a fabricated **documented** one. If you
can't ground a table in a source you actually have, tag it `-- inferred` and
record it in `COVERAGE.md` — never invent a citation.
Individually inferred columns inside an otherwise-documented table get their own
inline `-- inferred` note too.
## Worked change — add a table and a column
Say you want to model **container gate movements** (a new concept) and add a
**customs-value-method note** column to items.
**1 · Add the column** (documented — it maps to SAD box 43):
```sql
-- src: S003 (SAD box 43 valuation method note)
ALTER TABLE declaration_item
ADD COLUMN valuation_method_note varchar(200);
COMMENT ON COLUMN declaration_item.valuation_method_note
IS 'Free-text note on the valuation method chosen (SAD box 43).';
```
**2 · Add the table** (inferred — no public source at this granularity):
```sql
-- inferred (gate in/out events are operational; not in the reference docs)
CREATE TABLE asycuda.container_gate_move (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
container_id bigint NOT NULL REFERENCES container(id) ON DELETE CASCADE,
direction varchar(3) NOT NULL, -- in / out
moved_at timestamptz NOT NULL DEFAULT now(),
office_id bigint REFERENCES ref_customs_office(id),
CONSTRAINT ck_gate_dir CHECK (direction IN ('in','out'))
);
COMMENT ON TABLE asycuda.container_gate_move
IS 'Container gate in/out events at a customs office; inferred.';
```
## Keep the docs in lock-step
Three files are the audit surface — update them in the same change:
| File | Update |
|------|--------|
| `SOURCES.md` | Add any **new** source ID you cited (with URL, note, and a cached copy under `sources/`). |
| `COVERAGE.md` | Add the new table under its module, marked `documented` / `partial` / `inferred`. |
| `DATA_DICTIONARY.md` | Regenerate from the catalog (it is generated, not hand-written). |
## Verify nothing broke
Re-run the load and the done-condition checks after any change:
```bash
createdb oct_check
psql -v ON_ERROR_STOP=1 -d oct_check -f Sydonia/schema/asycuda.sql
psql -v ON_ERROR_STOP=1 -d oct_check -f Sydonia/schema/seed_reference.sql
psql -v ON_ERROR_STOP=1 -d oct_check -f Sydonia/examples/e2e.sql
dropdb oct_check
# every CREATE TABLE must still carry a provenance tag:
grep -niE 'create[ \t]+table' Sydonia/schema/asycuda.sql | wc -l
```
!!! tip "Let an Agent Skill enforce this"
The [`customs-extend`](../skills/index.md) skill applies this whole checklist —
conventions, provenance tag, doc updates, and a re-load — for you, and
[`customs-validate`](../skills/index.md) confirms the schema still loads clean
and every table is grounded.
---
# Worked example — manifest to release
`Sydonia/examples/e2e.sql` inserts one complete import and prints it back. This
page narrates that script so you can see the domain and the schema working
together. The scenario: **consumer electronics and apparel shipped from Shanghai
to Pohnpei (FSM)**, cleared by a broker.
Load it (after the schema and seed):
```bash
psql -v ON_ERROR_STOP=1 -d customs_sandbox -f Sydonia/examples/e2e.sql
```
The whole script runs in **one transaction** and uses natural-key subselects
(`SELECT id FROM … WHERE code = …`) so it is order-independent and self-documenting.
## 0 · Parties
Five economic operators — carrier, shipping agent, exporter, importer/consignee
and broker — plus two system users (the broker and a customs officer).
```sql
INSERT INTO trader (tin, name, address, country_id) VALUES
('EXP001','Shenzhen Electronics Co', 'Shenzhen, China', (SELECT id FROM ref_country WHERE iso_alpha2='CN')),
('IMP001','Pohnpei Trading Ltd', 'Kolonia, Pohnpei',(SELECT id FROM ref_country WHERE iso_alpha2='FM')),
...;
```
## 1 · Manifest
The carrier's manifest: **MV Pacific Star**, voyage V2026-042, Shanghai →
Pohnpei, one bill of lading, one container, 250 packages. A house B/L
(`POL-BL-88231`, nature *Imports*) carries two goods lines — phones and T-shirts
— stuffed in container `OTEU1223808`.
```mermaid
flowchart LR
M[manifest
V2026-042] --> B[bill_of_lading
POL-BL-88231]
B --> C[container
OTEU1223808]
B --> G1[cargo line 1
851712 phones]
B --> G2[cargo line 2
610910 T-shirts]
```
## 2 · Declaration
The broker files an **IM4** (import for home use) declaration, regime `4000`,
against the manifest. Two items:
| Item | HS | Goods | FOB |
|:----:|----|-------|----:|
| 1 | 851712 | Mobile telephones | $40,000 |
| 2 | 610910 | Cotton T-shirts | $20,000 |
Invoice terms are **CIF**, currency USD, total FOB $60,000, freight $3,000,
insurance $300 → total CIF **$63,300**.
## 3 · Valuation note
Freight and insurance are apportioned to each item by FOB share (2:1):
| Item | FOB | + freight | + insurance | = item CIF |
|:----:|----:|----------:|------------:|-----------:|
| 1 | 40,000 | 2,000 | 200 | **42,200** |
| 2 | 20,000 | 1,000 | 100 | **21,100** |
Those item CIFs become the **customs value** — the tax base.
## 4 · Tax lines
Duty and VAT per item. Note VAT cascades — its base is *(customs value + import
duty)*:
| Item | Tax | Base | Rate | Amount |
|:----:|-----|-----:|:----:|-------:|
| 1 | IMP | 42,200 | 5% | 2,110.00 |
| 1 | VAT | 44,310 | 10% | 4,431.00 |
| 2 | IMP | 21,100 | 15% | 3,165.00 |
| 2 | VAT | 24,265 | 10% | 2,426.50 |
**Total assessed: $12,132.50.**
## 5 · Documents
An attached commercial invoice (`380`) and bill of lading (`705`), then each item
is **written off** against the manifest B/L via `declaration_previous_document`
(SAD box 40) — closing the loop between what the carrier manifested and what the
importer declared.
## 6 · Lifecycle, selectivity, payment, release
The declaration walks its lifecycle, is routed **RED** by the `HS-HIGHRISK`
criterion (electronics chapter), inspected and found conform, paid ($12,132.50,
receipt `RCPT-2026-0427`), and released:
```mermaid
stateDiagram-v2
direction LR
[*] --> stored
stored --> registered
registered --> assessed
assessed --> paid : selectivity RED → inspected conform
paid --> released
released --> [*]
```
An `audit_log` row records the release.
## The verification read-out
After `COMMIT`, the script prints four checks:
```text
--- Declaration summary ---
reg | type | status | lane | total_items | total_cif_value
-------+------+----------+------+-------------+-----------------
C 427 | IM4 | released | RED | 2 | 63300.0000
--- Items with tax totals ---
item_number | hs_code | customs_value | taxes
-------------+---------+---------------+-----------
1 | 851712 | 42200.0000 | 6541.0000
2 | 610910 | 21100.0000 | 5591.5000
--- Total assessed vs receipt ---
total_assessed | receipt_amount
----------------+----------------
12132.5000 | 12132.5000
--- Lifecycle trail ---
sort_order | code | note
------------+------------+-------------------------------------
1 | stored | Draft captured
2 | registered | Validated & registered (C 427)
3 | assessed | Assessed: duties & taxes calculated
4 | paid | Paid — receipt RCPT-2026-0427
5 | released | Release order issued
```
`total_assessed = receipt_amount` and every foreign key resolves — the model is
internally consistent end to end.
## Try changing it
- Add a **third item** and watch the apportionment and totals shift.
- Route it **GREEN** instead and drop the `inspection_act`.
- Pay against a deferred **`account`** instead of cash, and add the
`account_movement`.
The [querying guide](querying.md) has the reports to see your changes; the
[extending guide](extending.md) shows how to add new structure.
---
# ML on customs data
This toolbox was reconstructed with two payloads in mind: **(1)** doing
analytics / ML on customs declarations, and **(2)** plugging an external risk
engine into the clearance flow. This guide is the blueprint — distilled from the
public ASYCUDA record and public customs-ML research — and, crucially, a way to
**prototype the whole loop against this schema before you have access to a live
ASYCUDA system**.
!!! note "Read the platform behaviour first"
This guide assumes the clearance state machine and four-lane routing from
[Selectivity & clearance](../platform/selectivity-clearance.md). The lanes,
the criterion model, and the **random exploration slot** are the hooks
everything here plugs into.
## The canonical loop
Every documented ASYCUDA-plus-ML deployment follows the same four-step loop:
**read → score → inject → feedback**.
```mermaid
flowchart LR
A[("ASYCUDA
declarations")] -->|read: DB / API / stream| B[External model]
B -->|score: fraud + revenue| C{Inject}
C -->|criteria / trader profile| D[Selectivity lane]
C -.->|exploration| E[Random slot]
D --> F[Inspection Act]
E --> F
F -->|feedback: relabel| B
```
The pattern is proven at several levels of evidence:
- **Vendor risk-management plugins** on legacy AW — the real-world blueprint.
A typical plugin ingests ASYCUDA data *through either a database connection or
an API*, trains and scores externally, pushes high-risk flags back into
selectivity, and feeds inspection outcomes back in real time to retrain.
- **Live production deployments** — the deployable analogue. A separate but
integrated risk module within ASYCUDA World retrieves the declaration in real
time on submission, scores it (nomenclature, origin, importer/exporter history,
declaration patterns, valuation anomalies), and sends risk recommendations back
to officers through ASYCUDA World. This proves the architecture is deployable
**today**.
- **ASY5 first-class hook** — the new generation transforms *"signals from ML
analysis of customs data or from third-party AI engines … into clear risk
profiles"* via a **Risk Configuration → Signal Transformation → Risk Output**
pipeline. The intended injection point once a country is on ASY5 — but the
**payload format is not yet public**.
## The feature set
The empirically-standard feature schema used across public customs-ML research
maps directly onto ASYCUDA fields and onto our tables. The fourth column is what
you query in the [`asycuda`](../schema/data-dictionary.md) schema:
| Concept | SAD box | AW XML tag | Sydonia Toolkit column |
|---------|:-------:|------------|-----------------------------|
| Declaration ID | 7 | `Declarant/Reference/Number` | `declaration.id` (business ref: `declaration.trader_reference`) |
| Date | reg. | `Identification/Registration/Date` | `declaration.registration_date` |
| Importer TIN | 8 | `Consignee_code` | `trader.tin` via `declaration.consignee_id` |
| Declarant / broker | 14 | `Declarant_code` | `trader.tin` via `declaration.declarant_id` |
| Origin | 34a | `Country_of_origin_code` | `declaration_item.country_origin_id` → `ref_country` |
| Office / port | A/29 | `Customs_clearance_office_code` | `declaration.office_id` → `ref_customs_office` |
| HS / tariff | 33 | `Commodity_code` | `declaration_item.hs_code` |
| Quantity | 41 | `Supplementary_unit_quantity` | `declaration_item.supplementary_qty` |
| Gross weight | 35 | `Gross_weight_itm` | `declaration_item.gross_mass` |
| Invoice value (FOB) | 22/42 | `Invoice/Amount_foreign_currency` | `declaration_item.item_price` (hdr `declaration.total_invoice_amount`) |
| Customs value (CIF) | 46 | `Statistical_value` | `declaration_item.customs_value` |
| Total taxes | 47 | `Item_taxes_amount` / `Global_taxes` | `sum(declaration_tax_line.tax_amount)` |
| **Label** — illicit (0/1) | inspection | (server outcome) | `inspection_act.result` |
| **Target** — revenue | inspection | (server outcome) | `inspection_act.findings` (free text; not a structured amount) |
**Engineered signals** the literature relies on: unit price (`cif / quantity`),
weight-unit price (`cif / gross_mass`), tax ratio (`total_taxes / cif`),
FOB/CIF ratio, cross-features (**HS6 × origin**, office × importer), and
mean-target risk encodings per importer / HS / office.
## Labels & bias
The single hardest problem is not features — it is **labels**.
- **Labels only exist for inspected declarations.** `illicit` / `revenue` are
recorded only for Yellow/Red (and PCA) declarations; green-lane transactions
are unlabelled. This is **selection bias** baked into your training set.
- **The Inspection Act is the feedback signal** — in our model,
`inspection_act.result` (conform / discrepancy) and `inspection_act.findings`.
Read it back to relabel and retrain.
- **Exploration mitigates the bias.** The **random slot** (the 1–3% green→red
re-route) is your uniform sample of the unlabelled space — reserve it for
exploration rather than exploitation.
- **The field uses semi-supervised + active-learning methods** (e.g. GraphFC)
precisely because of this bias: inspected items get relabelled and added to
training (active learning), and unlabelled structure is exploited
semi-supervised.
## Prototype on this toolbox
You can build and test the entire read → feature → label loop against this
schema before touching a live system.
**1. Stand up the schema.** Use the
[`customs-schema-setup`](../skills/index.md) skill to load the `asycuda` schema,
then [`customs-seed`](../skills/index.md) to generate synthetic manifests,
declarations, items, tax lines and inspection acts. Set the search path once:
```sql
SET search_path TO asycuda, public;
```
**2. Extract a per-item feature vector.** One row per declared item, with the
engineered ratios computed in-query:
```sql
SELECT d.id AS declaration_id,
d.registration_date AS decl_date,
imp.tin AS importer_tin,
org.iso_alpha2 AS origin,
off.office_code AS office,
di.hs_code,
left(di.hs_code, 6) AS hs6,
di.gross_mass,
di.customs_value AS cif,
coalesce(sum(tl.tax_amount), 0) AS total_taxes,
di.customs_value / nullif(di.gross_mass, 0) AS unit_price_per_kg,
coalesce(sum(tl.tax_amount), 0)
/ nullif(di.customs_value, 0) AS tax_ratio
FROM declaration_item di
JOIN declaration d ON d.id = di.declaration_id
JOIN trader imp ON imp.id = d.consignee_id
LEFT JOIN ref_country org ON org.id = di.country_origin_id
JOIN ref_customs_office off ON off.id = d.office_id
LEFT JOIN declaration_tax_line tl ON tl.declaration_item_id = di.id
GROUP BY d.id, d.registration_date, imp.tin, org.iso_alpha2, off.office_code,
di.hs_code, di.gross_mass, di.customs_value;
```
**3. Pull the labels.** Only inspected declarations have them — the join is a
`LEFT JOIN`, and the `NULL` rows *are* the selection bias:
```sql
SELECT d.id AS declaration_id,
lane.code AS lane,
ia.result,
ia.findings,
CASE WHEN ia.result = 'discrepancy' THEN 1
WHEN ia.result = 'conform' THEN 0
ELSE NULL END AS illicit_label
FROM declaration d
JOIN ref_selectivity_lane lane ON lane.id = d.selectivity_lane_id
LEFT JOIN inspection_act ia ON ia.declaration_id = d.id;
```
**4. Aggregate a per-importer risk encoding.** A mean-target feature — the kind
the literature leans on hardest:
```sql
SELECT imp.tin AS importer_tin,
count(*) AS inspected,
count(*) FILTER (WHERE ia.result = 'discrepancy') AS discrepancies,
round(100.0 * count(*) FILTER (WHERE ia.result = 'discrepancy')
/ nullif(count(*), 0), 1) AS discrepancy_rate
FROM declaration d
JOIN trader imp ON imp.id = d.consignee_id
JOIN inspection_act ia ON ia.declaration_id = d.id
GROUP BY imp.tin
ORDER BY discrepancy_rate DESC NULLS LAST;
```
Adapt the join-path style from [Querying the model](querying.md); every column
above is verified against the schema.
!!! tip "External baselines to prototype against"
Before you have real ASYCUDA history, train against **public customs-ML
research and open customs datasets**. The field offers:
- **Open, downloadable customs declaration datasets** — some synthetic, with
fraud / critical-fraud labels — good enough to prototype the feature
pipeline and modelling against before you have real history.
- **Published dual-task scorers** that predict both illicitness and
recoverable revenue, reporting high precision and revenue recall while
inspecting only a small fraction of flows — a well-documented target to
benchmark against.
- **Reference open-source analytics notebooks** for customs fraud detection —
the standard starting point for feature engineering and baselines.
## Going live
Once you move from this sandbox to a real deployment, the four-step reference
architecture:
1. **Read** — a **DB read-replica** of the ASYCUDA schema for training data
(the proven path for vendor risk plugins), plus **ASYHUB** API / **Cargo-XML**
pre-arrival feed or **Kafka** (ASY5) for real-time scoring.
2. **Score** — output a per-declaration fraud/illicitness score **and** predicted
recoverable revenue (a dual-task scorer).
3. **Inject** — on legacy AW, write into selectivity criteria / trader profiles,
prioritising the **random / exploration slot**; on ASY5, emit "signals".
4. **Feedback** — read **Inspection Act / PCA** outcomes to relabel and retrain.
!!! warning "The deadline is set by when selectivity fires"
Whether selectivity fires **before or after assessment** is a per-country
switch (see
[the timing warning](../platform/selectivity-clearance.md#when-selectivity-fires-the-timing-switch)).
It sets the hard deadline by which your engine must have scored the
declaration. Confirm the mode before committing to real-time.
!!! note "What to request — these are not public"
No public customs-ML dataset or research codebase ships an ASYCUDA connector;
the ETL and write-back are bespoke in every deployment. Before building,
request from your national customs administration or the UNCTAD ASYCUDA
programme: the **physical DB schema**, the **ASYHUB API spec**, the **ASY5
risk-signal payload format**, the **Asysel** admin data model, and
**Inspection-Act read access** (illicit flag + recovered revenue). See
[Integration surfaces](../platform/integration.md) for the doors and the
request list.
---
# Provenance
The credibility of this project rests on one promise: **every table is either
grounded in a cited public source or honestly marked as inferred — nothing is
invented and dressed up as documented.** This section is the evidence.
49tables documented (-- src:)
6tables inferred (-- inferred)
100%cited, cached sourcing
0fabricated citations
- :material-compass:{ .lg .middle } **Methodology**
---
The evidence-first reconstruction loop, the source policy, and the
verification checks that gate "done".
[:octicons-arrow-right-24: Methodology](methodology.md)
- :material-bookshelf:{ .lg .middle } **Sources**
---
The public documentation consulted — official public technical tables, national
ASYCUDA World manuals, and open standards — cited as generic categories.
[:octicons-arrow-right-24: Sources](sources.md)
- :material-format-list-checks:{ .lg .middle } **Coverage**
---
Every module and table, marked documented / partial / inferred, with the
known gaps stated plainly.
[:octicons-arrow-right-24: Coverage](coverage.md)
- :material-vector-difference:{ .lg .middle } **Official fit & gap**
---
Our reconstruction mapped table-by-table against the official
ASYCUDA World tables — where it fits, and where it deliberately differs.
[:octicons-arrow-right-24: Fit & gap](fit.md)
## Read the provenance yourself
You don't have to take these pages on faith. The evidence is in the repository:
```bash
# every CREATE TABLE carries a -- src: or -- inferred tag
grep -niE 'create[ \t]+table' Sydonia/schema/asycuda.sql
# every cited ID resolves to a row in SOURCES.md and a cached file under sources/
grep -oiE '\-\- src: *S[0-9, ]+' Sydonia/schema/asycuda.sql | grep -oiE 'S[0-9]+' | sort -u
```
The [`customs-validate`](../skills/index.md) skill automates exactly this audit.
---
# Reconstruction methodology
How do you rebuild a proprietary system's data model **without touching the
proprietary system**? By treating public documentation as evidence and never
letting the schema get ahead of it. This page is that method — useful both to
audit this project and to apply the same discipline to any other reconstruction.
## The problem
ASYCUDA World (SYDONIA) runs customs in 100+ countries, but its internal database
schema is proprietary and unpublished. The goal was a **faithful, information-
equivalent reference model** for sandbox, analytics, integration and training
use — built entirely from what is public.
## The evidence-first loop
The core discipline: **never write DDL for a table or column until a source for
it is already fetched, cached and cited.** The order is always the same.
```mermaid
flowchart LR
F[fetch
public doc] --> S[save
under sources/]
S --> C[cite
in SOURCES.md]
C --> R[restate
in RESEARCH_LOG.md]
R --> M[model
the table/column]
M --> V[verify
re-load + checks]
V -->|next highest-value gap| F
```
Anything introduced by reasoning rather than a document is tagged `-- inferred`
and recorded as inferred in coverage — never quietly promoted to "documented".
## The provenance contract
Two tags, one on every `CREATE TABLE`, and the rule that governs them:
| Tag | Meaning |
|-----|---------|
| `-- src: ` | Grounded in the cited source(s) `` (each resolves in [Sources](sources.md)) |
| `-- inferred` | Introduced by modelling judgement; no public source at this granularity |
!!! quote "The governing principle"
A larger honest **inferred** set beats a fabricated **documented** one. If
something can't be sourced from a document actually fetched, it is tagged
`-- inferred` and marked in coverage — not given an invented citation.
## Source policy — non-negotiable
**Used freely (public):**
- Official ASYCUDA / UNCTAD programme material and the **official
technical table descriptions** (the real physical schema, published as
reference documentation).
- Full ASYCUDA World user and broker manuals published by national customs
administrations (declaration processing, manifest/cargo, valuation, suspense).
- Open standards: the **WCO Data Model**, the **SAD**, ISO 3166 / 4217 / 6346,
UN/LOCODE, UN/ECE Rec 21, the Harmonized System, Incoterms.
**Never (out of scope, by rule):**
- Downloading, cracking or decompiling the ASYCUDA software to dump its schema.
- Probing, scanning or logging into any live customs deployment beyond its
openly published docs.
- Using leaked credentials or bypassing access controls.
- Reproducing long verbatim copyrighted text — structure and field semantics are
restated in the project's own words; only short field labels are quoted, cited.
The public corpus proved more than sufficient.
## Verification gates "done"
The project is only "done" when these hold **and the checks were re-run** — not
asserted:
1. **Schema loads clean.** `asycuda.sql` → `seed_reference.sql` → `e2e.sql`
against a fresh PostgreSQL database completes with **zero errors**, and the
end-to-end example inserts with referential integrity intact.
2. **Every table is grounded.** Every `CREATE TABLE` carries a `-- src:` (ID in
`SOURCES.md`) or `-- inferred`. Zero untagged.
3. **Every source is cited and cached.** Each cited ID has a row in `SOURCES.md`
*and* a saved local copy under `sources/`.
4. **Supporting docs match the schema.** Data dictionary, ERD, coverage and the
research log are complete and consistent with the loaded model.
These map directly to the [`customs-validate`](../skills/index.md) skill.
## How the model was actually built
7research phases
100%sources fetched & cached
55tables, 100% tagged
1. **Orient** — a SAD overview, a WCO Data Model briefing, and a cargo-manifest
XML message description; confirmed the general + item segment model.
2. **Mine national manuals** — the highest-yield public source: full declaration
and manifest user guides, from which field names, code lists, segments and
lifecycles were extracted and restated.
3. **Code tables & standards** — grounded the `ref_*` tables in the ISO/UN/WCO
standards the forms reference.
4. **Draft the schema** module by module, loading into a scratch DB after each.
5. **Seed & validate** — representative reference data and the end-to-end example.
6. **Document & finalise** — data dictionary, ERD, coverage, fit/gap.
7. **Integrate official data** — later, the **official table
descriptions** were cited across the schema, upgrading 11 tables from
`inferred` to documented (49 / 6).
The full trail lives in `RESEARCH_LOG.md` (append-only findings with source IDs)
and `STATE/progress.md`.
## Why the shape differs from the official schema
The official model is a **wide, denormalised** physical schema tuned for the
ASYCUDA engine; this is a **normalised relational** reference model tuned for
sandboxes and analytics. They are information-equivalent for the modelled scope —
the [fit & gap analysis](fit.md) maps every official table to ours and states the
deliberate differences (FK vs inline code+name, surrogate keys, derived totals).
---
# Sources
Every `-- src: ` in the schema resolves to a row below, where each ID denotes
a **generic category of public documentation** consulted (specific titles,
publishers and URLs are intentionally omitted). Each cited category has a cached
local copy under `sources/` (or `docs/` for the official technical documents).
All material is public. This registry is the single source of truth for citations
— reproduced here from `SOURCES.md`.
Every `-- src: ` comment in `schema/*.sql` resolves to a row in this
registry, and each `` denotes a category of **public documentation**
consulted when modelling that table. A cached copy of the material is retained
locally under `sources/` (or `docs/`).
> Specific document titles, publishers, and URLs have been intentionally omitted
> from this registry. The model is grounded in public documentation only —
> restated in the project's own words — not in any proprietary schema or data
> from a live customs system.
## Categories consulted
- Public references on the **Single Administrative Document (SAD)** and Incoterms.
- **National ASYCUDA World user and broker manuals** published by customs
administrations (declaration processing, manifest/cargo, valuation, suspense).
- **Public ASYCUDA / UNCTAD programme documentation**, including official
technical table descriptions, processing manuals, and XML message descriptions.
- **Open international standards**: ISO 3166 (countries), ISO 4217 (currencies),
ISO 6346 (containers), UN/LOCODE, UN/ECE Rec 21 (packages), the Harmonized
System, the WCO Data Model, Incoterms, and the WTO valuation methods.
## Registry
| ID | Category | Cached |
|----|----------|:------:|
| S001 | Public reference on the SAD declaration | yes |
| S002 | National ASYCUDA World declaration-processing manual | yes |
| S003 | National ASYCUDA World declaration user guide | yes |
| S004 | National ASYCUDA World broker manual | yes |
| S005 | National ASYCUDA World declaration user guide | yes |
| S006 | National ASYCUDA World manifest user guide | yes |
| S007 | National ASYCUDA World manifest user guide | yes |
| S008 | Public ASYCUDA World cargo-manifest XML message description | yes |
| S009 | Public WCO Data Model overview | yes |
| S010 | National ASYCUDA World manifest manual | yes |
| S011 | National ASYCUDA World manifest manual | yes |
| S012 | Public Incoterms reference | yes |
| S013 | Official ASYCUDA World reference-tables description | yes |
| S014 | Official ASYCUDA World declaration-tables description | yes |
| S015 | Official ASYCUDA World manifest-tables description | yes |
| S016 | Official ASYCUDA World accounting-tables description | yes |
| S017 | Official ASYCUDA World SAD processing manual | yes |
| S018 | Official ASYCUDA World introductory manual | yes |
| S019 | ASYCUDA World suspense-declarations manual | yes |
| S020 | Public ASYCUDA World XML manifest message description | yes |
Field semantics were restated in the project's own words; only short field labels
were reused. Where a table could not be grounded in consulted documentation it is
tagged `-- inferred` in the schema and recorded in `COVERAGE.md`.
---
# Coverage
The provenance roll-up: each GOAL module and every table within it, marked
**documented**, **partial** or **inferred**, ending with the known gaps stated
plainly. Reproduced from `COVERAGE.md`.
Legend: **documented** = grounded in a cited source (`-- src:` in the DDL); **partial** =
core grounded, some columns/tables inferred; **inferred** = introduced by modelling judgement
(`-- inferred`), no public source at this granularity. IDs (S0xx) resolve in SOURCES.md.
Every `CREATE TABLE` in `schema/asycuda.sql` (55 tables) carries a `-- src:` or `-- inferred`
tag; this file rolls those up by GOAL §4 module.
> **Official-data update.** The official technical table descriptions cached under `docs/`
> (S013–S016) plus the suspense-declarations manual (S019) are now cited across the schema. This
> upgraded 11 tables from `inferred` to documented (exchange-rate, unit-of-measure, tax-rate,
> manifest-status, account-movement, guarantee, warehouse entry/exit, temporary-admission,
> risk-criterion, audit-log). See `FIT.md` for the full official-table-to-model mapping. Current
> tally: **49 documented / 6 inferred**.
---
## 4.1 Reference / configuration — **documented (partial)**
| Table | Status | Grounding |
|-------|--------|-----------|
| `ref_country` | documented | S008 (ISO 3166 2-alpha nationality) |
| `ref_currency` | documented | S008 (ISO 4217 freight currency), S003 box 22 |
| `ref_exchange_rate` | documented | S013 (official xxRATTAB), S003 box 23 |
| `ref_customs_office` | documented | S008 (customs_office_code AN5), S003 box 29 |
| `ref_location` (UN/LOCODE ports) | documented | S008 (place codes = UN/LOCODE) |
| `ref_transport_mode` | documented | S008 (full 1–9 code list) |
| `ref_package_type` | documented | S008 (UN/ECE Rec 21 alpha-2), S003 box 31 |
| `ref_container_type` | documented | S008 (ISO 6346:1995) |
| `ref_unit_of_measure` | documented | S013 (official xxUOMTAB statistical units), S003 box 41 |
| `ref_incoterm` | documented | S003 box 20, S012 |
| `ref_hs_tariff` | documented | S003 box 33 (HS), S008 (6-digit HS) |
| `ref_cpc_regime` | documented | S003 box 37 (CPC extended procedure) |
| `ref_tax_type` | documented | S003 box 47 (per-tax calculation) |
| `ref_tax_rate` | documented | S013 (official xxRULTAB taxation rules / xxTAXTAR) |
| `ref_document_type` | documented | S003 box 44, S008 attached_document |
| `ref_exemption_code` | documented | S003 box 37 national procedure (ANC) |
| `ref_declaration_type` | documented | S003 box 1 (IM4/EX1/SD4) |
| `ref_declaration_status` | documented | S002 (finder statuses) |
| `ref_manifest_status` | documented | S015 (official GEN_TAB STA status) |
| `ref_bl_nature` | documented | S008 (Bol_nature codes) |
| `ref_selectivity_lane` | documented | S002, S005 (four lanes) |
| `ref_warehouse` | documented | S003 box 49 |
| `trader` / `trader_role` | documented / inferred | S003 boxes 2/8/14, S008 traders; role-junction inferred |
| `sys_user` | documented | S002 (registered users) |
| `sys_role`,`sys_permission`,`sys_user_role`,`sys_role_permission` | inferred | RBAC implied; no public schema |
## 4.2 Manifest / cargo — **documented**
| Table | Status | Grounding |
|-------|--------|-----------|
| `manifest` | documented | S008, S006 (general segment) |
| `bill_of_lading` (master/house, self-ref) | documented | S008, S006, S010 (Bol segment, master/house, degroupage) |
| `container` | documented | S008 (ctn_segment, ISO 6346, seals, reefer) |
| `manifest_cargo_item` | documented | S008 (Goods/Commodity segment) |
| `manifest_status_history` | partial | S015 (GEN_TAB STA) grounds status; history shape inferred |
*Note:* the manifest XML also defines a vehicles sub-segment (chassis/VIN/engine) [S008]; it is
noted in RESEARCH_LOG but not modelled as a table (out of the e2e path). Marked as a known gap.
## 4.3 Declaration — the SAD (core) — **documented**
| Table | Status | Grounding |
|-------|--------|-----------|
| `declaration` (general segment) | documented | S003 (boxes 1–49/B), S001 |
| `declaration_item` (item segment) | documented | S003 (boxes 31–49) |
| `valuation_note` | documented | S003 (value build-up) |
| `item_value_note` | documented | S003 (per-item CIF apportionment), box 46 |
| `declaration_tax_line` | documented | S003 box 47 (type, base, rate, amount, mode) |
| `declaration_attached_document` | documented | S003 box 44, S008 |
| `declaration_previous_document` | documented | S003 box 40, S008 (write-off vs B/L) |
| `declaration_status_history` | documented | S002 (lifecycle) |
| `selectivity_result` | documented | S002, S005 |
## 4.4 Accounting — **documented**
| Table | Status | Grounding |
|-------|--------|-----------|
| `account` | documented | S003 box 48 (deferred payment account) |
| `payment` | documented | S003 box B, S005 |
| `receipt` | documented | S003 box B (receipt number) |
| `account_movement` | documented | S016 (official account transactions in/out) |
| `guarantee` | documented | S019 (suspense guarantees), S003 box 52 |
## 4.5 Transit & suspense — **documented** (S019)
| Table | Status | Grounding |
|-------|--------|-----------|
| `transit_declaration` | documented | S003 boxes 50–53 (principal, offices, guarantee) |
| `ref_warehouse` | documented | S003 box 49 |
| `warehouse_entry` / `warehouse_exit` | documented | S014 (SUS_WH_IN), S019 (suspense manual) |
| `temporary_admission` | documented | S019 (suspense temporary admission regime) |
## 4.6 Selectivity / risk — **documented**
| Table | Status | Grounding |
|-------|--------|-----------|
| `ref_selectivity_lane` | documented | S002, S005 |
| `selectivity_result` | documented | S002, S005 |
| `inspection_act` | documented | S002, S005 (examination) |
| `risk_criterion` | documented | S014 (official SEL_*_PARAM_TAB selectivity criteria) |
## 4.7 Audit / workflow (cross-cutting) — **documented** (LogTable)
| Table | Status | Grounding |
|-------|--------|-----------|
| `audit_log` | documented | S013 (official LogTable actions/procedures) |
| `*_status_history` tables | documented/inferred | declaration & manifest lifecycles (see above) |
---
## Roll-up
- **Documented (`-- src:`):** 49 tables — after citing the official table descriptions
(S013–S016) and the Suspense manual (S019), all reference/manifest/declaration/accounting/transit/
selectivity/audit tables are grounded in an official or public source.
- **Inferred (`-- inferred`):** 6 tables — `trader_role`, `sys_role`, `sys_permission`, `sys_user_role`,
`sys_role_permission` (RBAC + trader-role junction; no published ASYCUDA user/role schema).
- **Known gaps (not blocking the e2e):** manifest vehicle sub-segment; degroupage/split as its own
table (modelled via `bill_of_lading.master_bl_id`); full ISO/UN/WCO code-list *values* (seeded as
representative samples of the referenced standard, not exhaustive catalogues).
No table is left untagged; the honest inferred set is preferred over fabricated citations.
---
# Official fit & gap analysis
How the reconstructed model measures up against the **official public
ASYCUDA World technical table descriptions** (S013–S016). Verdict first, then the
table-by-table mapping and the deliberate structural differences. Reproduced from
`FIT.md`.
This checks our reconstructed PostgreSQL model (`schema/asycuda.sql`) against the **official public
ASYCUDA World technical table descriptions** cached under `docs/` (S013–S016). Verdict first, then the
table-by-table mapping and the deliberate structural differences.
## Verdict
**We fit the official model at the entity and field level.** Every core official operational table
(manifest general segment, bill of lading, containers, SAD general segment, SAD item, SAD taxes,
attached/previous documents, selectivity, accounting, suspense) has a corresponding table in our
schema, and the field semantics line up. After cross-checking, 49 of 55 tables now cite an official
or public source; the 6 remaining `inferred` tables are our own normalisation (RBAC + trader-role
junction), which the official model handles differently rather than "not at all".
**The difference is shape, not content.** The official schema is a *wide, denormalised* physical model
optimised for the ASYCUDA World engine; ours is a *normalised relational* reference model for sandbox,
analytics and integration use (which is exactly this project's stated purpose). The two are
information-equivalent for the modelled scope. Concretely:
| Aspect | Official ASYCUDA World (S013–S016) | Our model |
|--------|-----------------------------------|-----------|
| Table names | terse codes: `GEN_TAB`, `BOL_TAB`, `SAD_General_Segment`, `xxCTYTAB` | descriptive: `manifest`, `bill_of_lading`, `declaration`, `ref_country` |
| Coded fields | store **code _and_ name inline** (`GEN_CAR_COD` + `GEN_CAR_NAM`) | store an **FK to a `ref_*` table** (normalised) |
| General segment | **repeated** into every `BOL_TAB` / `SAD_Item` row | held once in the parent, referenced by FK |
| Keys | `INSTANCE_ID` / `InstanceId` engine keys | `bigint GENERATED ALWAYS AS IDENTITY` surrogate PKs |
| HS code | split across `TAR_HSC_NB1..NB5` (national precision) | single `hs_code` + `ref_hs_tariff` hierarchy |
| Reference validity | every `UN*` table has `VALID_FROM`/`VALID_TO` | `is_active` boolean (+ `valid_from/to` on rate tables) |
| Taxes | `SAD_Tax` + `SAD_Ask_Tax` + `SAD_Global_Taxes` + `SAD_Tax_Totals` | `declaration_tax_line` (+ `is_manual` flag); totals derived by query |
None of these change *what* is captured; they change *how many tables* and *whether a value is a
code+name pair or an FK*. For a reference/analytics model, the normalised form is the intended one.
## Manifest module — official S015 → our tables
| Official table | Our table | Fit |
|----------------|-----------|-----|
| `GEN_TAB` (Manifest General Segment) | `manifest` | ✅ office, voyage, dates (departure/arrival/last discharge), totals (BOL/pkg/ctn/gross), carrier, shipping agent, transporter+nationality+mode, place of loading/unloading, tonnage net/gross, registration year/nbr/date, status. |
| `BOL_TAB` (Bill of lading) | `bill_of_lading` | ✅ reference, line/sub-line, nature, type, previous master B/L ref → our `master_bl_id`, exporter/consignee/notify, loading/unloading, packages, gross, volume, freight/customs/insurance/transport value+currency, seals. |
| `CTN_TAB` / `BOL_CTN_TAB` (Containers) | `container` | ✅ reference, type, packages, empty/full, seals, weights, volume, dangerous goods, description. |
| (goods lines within BOL) | `manifest_cargo_item` | ✅ HS, description, packages, package type, gross, container link. |
| `HIS_WRITE_OFF_TAB` / `REM_WRITE_OFF_TAB` | `declaration_previous_document` (write-off) | ◑ we model write-off as the declaration→B/L link; the official standalone write-off/history tables are folded in. |
| `MAN_TRANSIT_TAB` / `MAN_TRANSH_TAB` | `transit_declaration` | ◑ transit/transhipment management captured at declaration level. |
| vehicle sub-segment | — | ✗ **gap**: RoRo vehicle details (chassis/VIN/engine/make) not modelled (noted in COVERAGE). |
## Declaration module — official S014 → our tables
| Official table | Our table | Fit |
|----------------|-----------|-----|
| `SAD_General_Segment` | `declaration` | ✅ office, model/type, regime, manifest ref, registration/assessment/receipt serials+numbers+dates, exporter/consignee/financial/declarant, countries (export/dest/origin/trading/first-dest), value details, CAP, transport (depart/border/inland MOT, incoterm+place, container flag, place of loading, border office), release fields, **selectivity colour flags (PTY_BLU/RED/YEL/GRE)** → our `selectivity_lane_id` + `selectivity_result`. |
| `SAD_Item` | `declaration_item` | ✅ item no., packages+marks, package type, HS (NB1..NB5→`hs_code`), preference, extended+national procedure, quota, item price, valuation method, value details, attached docs, country of origin, container refs, description, gross/net mass, `VIT_CIF`→`customs_value`, `VIT_STV`→`statistical_value`. |
| `SAD_Tax` | `declaration_tax_line` | ✅ exact: `COD`→tax_type, `BSE`→tax_base, `RAT`→rate, `AMT`→amount, `MOP`→mode_of_payment, `TYP`→`is_manual`. |
| `SAD_Ask_Tax` (manual taxes) | `declaration_tax_line` (`is_manual=true`) | ✅ folded into the flag. |
| `SAD_Global_Taxes`, `SAD_Tax_Totals` | (derived) | ◑ computed by query in the e2e read-out; not stored as summary tables. |
| `SAD_Attached_Documents` | `declaration_attached_document` | ✅ header/item, doc type, reference, date. |
| `SAD_Int` (Previous Documents) | `declaration_previous_document` | ✅ B/L / previous-declaration write-off. |
| `SAD_Supplementary_Unit` | `declaration_item.supplementary_qty/uom` | ✅ captured on the item. |
| `SAD_Relief` | `ref_exemption_code` + `declaration_item.exemption_id` | ✅ relief/exemption. |
| `SAD_Serial_Storage/Registration/Assessment` | `declaration` serial columns + `declaration_status_history` | ◑ serials stored on the header; lifecycle in the history table. |
| `Exit_Note_*` | `warehouse_exit` | ◑ ex-warehouse exit note modelled minimally. |
| `INSP_ACT_TAB` | `inspection_act` | ✅ inspection act. |
| `SEL_PARAM_TAB` / `SEL_*_PARAM_TAB` / `SEL_LISTS*` | `risk_criterion` (+ `ref_selectivity_lane`) | ◑ criteria catalogue simplified to one table. |
| `VAL_CTL_TAB` / `VAL_FOR_TAB` (valuation control) | `valuation_note` / `item_value_note` | ◑ we model the value build-up result, not the control formulas. |
## Reference module — official S013 (`UN*` tables) → our `ref_*` tables
Direct matches: `xxCTYTAB`→`ref_country`, `xxCURTAB`→`ref_currency`, `xxRATTAB`→`ref_exchange_rate`,
`xxCUOTAB`→`ref_customs_office`, `xxLOCTAB`→`ref_location`, `xxMOTTAB`→`ref_transport_mode`,
`xxPKGTAB`→`ref_package_type`, `xxCTNTAB`→`ref_container_type`, `xxUOMTAB`→`ref_unit_of_measure`,
`xxTODTAB`→`ref_incoterm`, `xxHS1-6TAB`/`xxTARTAB`→`ref_hs_tariff`, `xxCP1/3/4TAB`→`ref_cpc_regime`,
`xxTAXTAB`→`ref_tax_type`, `xxRULTAB`/`xxTAXTAR`→`ref_tax_rate`, `xxATDTAB`→`ref_document_type`,
`xxCP3TAB`→`ref_exemption_code`, `xxMODTAB`→`ref_declaration_type`, `xxNATTAB`→`ref_bl_nature`,
`xxWHSTAB`→`ref_warehouse`, `xxCARTAB/xxDECTAB/xxCMPTAB/xxPRPTAB`→`trader`(+`trader_role`),
`LogTable`→`audit_log`.
Official `UN*` tables we intentionally did **not** model as separate tables (folded inline or out of
scope): `xxPRFTAB` (preference → `declaration_item.preference_code`), `xxVAMTAB` (valuation method →
`valuation_method_code`), `xxTR1/TR2TAB` (nature of transaction), `xxTOPTAB` (terms of payment),
`xxMOPTAB` (means of payment → `mode_of_payment`), `xxINDTAB` (empty/full → `container.empty_full`),
`xxSEATAB` (seals parties), `xxQUOTAB` (quota → `declaration_item.quota`), `xxCAPTAB` (CAP),
`xxMUQTAB` (measurement qualifiers), `xxHOLTAB`/`xxLNGTAB`/`xxPRTTAB`/`xxKWD*` (system housekeeping),
and the many `*_link` tables (e.g. `xxCTYMOT`, `xxCUOMOT`, `xxPRFCTY`) which are M:N junctions our
FK design expresses differently. These are listed as known gaps in COVERAGE.md.
## Accounting module — official S016 → our tables
| Official | Our table | Fit |
|----------|-----------|-----|
| `xxATITAB`/`xxATOTAB` (account transactions in/out) | `account` + `account_movement` | ✅ accounts and ledger movements. |
| receipts + `TAX_TAB` (taxes per receipt) | `receipt` + `payment` + `declaration_tax_line` | ✅ payment/receipt with per-tax detail. |
| `SER_LETTERS_TAB` / `SER_NBERING_TAB` (serial mgmt) | serial columns on `declaration`/`receipt` | ◑ serials stored inline rather than in a dedicated numbering service table. |
| cashier/shift/daybook report tables (`RPT*`) | — | ✗ reporting/aggregation tables out of scope (analytics can be built as views). |
## Suspense / transit — official S014 + S019
`SUS_WH_IN` (warehouse entry/exit) → `warehouse_entry`/`warehouse_exit`; `WHS_DLY*` (extension of
delay) → `ref_warehouse` + item `warehouse_days`; temporary admission → `temporary_admission`;
transit → `transit_declaration`; guarantees → `guarantee`. The Suspense Declarations manual (S019)
now grounds these tables that were previously `inferred`.
## Remaining honestly-inferred (6 tables)
`trader_role`, `sys_role`, `sys_permission`, `sys_user_role`, `sys_role_permission` — RBAC and the
trader-role junction. ASYCUDA World has role-based access, but the reference docs don't publish a
user/role/permission schema, so this stays our normalisation. `manifest_status_history` is grounded
by `GEN_TAB.STA` but the *history* shape is ours.
## Bottom line for the user
Yes — the reconstruction fits your official data. Where the official docs are more authoritative than
the national manuals we started from, the schema now cites them (S013–S016, S019), which upgraded 11
tables from `inferred` to documented. The intentional divergences (normalised vs denormalised,
FK vs code+name, derived totals) are the right choice for a sandbox/analytics reference model and are
listed above and in COVERAGE.md so nothing is hidden.
---
# Agent Skills
The toolbox ships a suite of standard **[Agent Skills](https://code.claude.com/docs/en/skills)**
(the `SKILL.md` format) so you can drive the customs model in plain English,
right inside your own project. They live in
[`skills/`](https://github.com/FrancoisChastel/sydonia-toolkit/tree/master/skills)
and are focused on one thing: **using the model on your own codebase.**
They are harness-agnostic — installable into **Claude Code, Cursor, Codex,
opencode** and any other agent the [skills CLI](https://github.com/vercel-labs/skills)
supports.
## The suite
- :material-database-plus:{ .lg .middle } **`customs-schema-setup`**
---
Stand the model up in a database: create it, load schema + seed (+ optional
e2e) in order, and report a clean/failed result.
*"Set up the customs sandbox in a local Postgres database."*
- :material-magnify-scan:{ .lg .middle } **`customs-query`**
---
Generate correct SQL against the model — it knows the `asycuda` search path
and the join paths — and **verify it privacy-preservingly** through the
companion tester MCP (or a bundled script): metadata only, never row data.
*"Show me duty revenue by HS chapter last quarter — and test it."*
- :material-table-plus:{ .lg .middle } **`customs-seed`**
---
Add reference/code values or generate realistic sample declarations and
manifests that respect every foreign key.
*"Generate three more import declarations with 2–4 items each."*
- :material-source-branch-plus:{ .lg .middle } **`customs-extend`**
---
Add tables/columns while preserving the conventions and the provenance trail,
and update the coverage docs in lock-step.
*"Add a container gate-movement table to the schema."*
- :material-check-decagram:{ .lg .middle } **`customs-validate`**
---
Re-run the done-condition checks: clean load, every table tagged, every cited
source resolves. Your auditable green light.
*"Validate the customs schema still loads clean and is fully sourced."*
## Install into your codebase
Install the whole suite into your current project with the
[`skills`](https://skills.sh) CLI — it auto-detects your agent (Claude Code,
Cursor, Codex, opencode, …) and drops the skills where that harness discovers
them:
```bash
npx skills add FrancoisChastel/sydonia-toolkit
```
Add a single skill with `--skill`:
```bash
npx skills add FrancoisChastel/sydonia-toolkit --skill customs-query
```
Also copy the model itself (the `Sydonia/` folder, or at least
`Sydonia/schema/` and `Sydonia/examples/`) so the skills have SQL to load. The
skills accept a `--schema-dir` argument if you put it elsewhere.
Verify your agent sees them — in Claude Code, for example:
```text
/skills # lists available skills; customs-* should appear
```
## Use them
Just describe what you want — your agent matches the request to a skill:
```text
> set up the customs model in a throwaway database and run the example
> write a query for assessed-vs-paid across all released declarations
> add a "carrier rating" column to trader, keep it sourced
> validate the schema is still clean and fully grounded
```
Each skill is a self-contained `SKILL.md` with bundled scripts and reference
material, following Anthropic's progressive-disclosure guidance — small
front-matter, details loaded only when needed.
## The privacy-preserving query tester (optional MCP)
`customs-query` ships with a companion **MCP server**,
[`customs-query-tester`](https://github.com/FrancoisChastel/sydonia-toolkit/tree/master/mcp/customs-query-tester)
(pre-registered in the repo's `.mcp.json`), that closes the generate→verify
loop: the assistant can **prove a query is valid and runs** — against a
database that may contain *real, sensitive customs declarations* — without any
row ever reaching the model.
**The model gets an oracle, not a window.** Four guarantees, enforced
server-side on every call:
| Guarantee | How |
|-----------|-----|
| Nothing can be written | Sessions start with `default_transaction_read_only=on` — the PostgreSQL server itself refuses writes, even data-modifying CTEs |
| Only single SELECTs run | A comment/string-aware scanner rejects multi-statements and every write/DDL/`COPY`/`INTO` keyword before the DB is touched |
| No row data in responses | Result shape via `\gdesc` (describe **without executing**); volume via `SELECT count(*) FROM () __q` — one aggregate number |
| Bounded execution | `statement_timeout` (default 5 s) + a hard subprocess cap |
Three tools: `describe_schema` (structure), `validate_query` (EXPLAIN-only),
`test_query` (read-only run → columns, types, row count, duration). No MCP
connected? The skill falls back to `scripts/test_query.sh` with the identical
guarantees through plain `psql`. For defence in depth on real data, run it as a
`SELECT`-only database role — the server's README shows the three-line GRANT.
## What they deliberately don't do
These skills are for **using** the model. They will not decompile ASYCUDA, touch
a live customs system, or fabricate source citations — the same
[source policy](../provenance/methodology.md#source-policy-non-negotiable) that
governs the model governs the skills.