Database

PostgreSQL with pgvector, hosted on Supabase. 14 tables covering tariff data, embeddings, enrichments, graph edges, references, and resources.

Schema Overview

Table	Primary Key	Rows	Purpose
`tariffs`	`hts8`	~13,000	Tariff lines with rates and dates
`hts_chapters`	`chapter`	98	2-digit chapter descriptions
`hts_schedule_sections`	`section_roman`	22	Roman numeral section groupings
`hts_headings`	`hts4`	~961	4-digit heading descriptions
`hts_sections`	`id`	~35,700	Raw hierarchical section data
`hts_notes`	`id`	~1,067	Interpretive notes
`hts_raw_notes`	`id`	~121	Full-text HTML note blobs

Table	Primary Key	Rows	Purpose
`hts_embeddings`	`(code, level, variant)`	~38,000	Multi-level vector embeddings
`hts6_enrichments`	`hts6`	~5,714	AI-generated descriptions and keywords

Table	Primary Key	Rows	Purpose
`hts_edge_candidates`	`(source_code, target_code, source_level)`	~142,000	KNN candidate pairs
`hts_semantic_edges`	`(source_code, target_code)`	~142,000	Classified relationships
`hts_graph_runs`	`id`	Variable	Pipeline run tracking

Table	Primary Key	Rows	Purpose
`tariff_code_references`	`(source_hts8, referenced_code_clean)`	~2,400	Cross-references from descriptions
`tariff_resources`	`id`	~45	Curated trade policy documents

The central table with ~13,000 tariff lines:

Multi-level vector embeddings for search:

Column	Type	Description
`code`	text	HTS code (2-8 digits)
`level`	text	chapter, hts4, hts6, hts8
`variant`	text	full, short
`embedding`	vector(1536)	OpenAI embedding
`embedding_text`	text	Source text that was embedded

Primary key: (code, level, variant)

Classified knowledge graph edges:

Column	Type	Description
`source_code`	text	Source HTS code
`target_code`	text	Target HTS code
`relationship_type`	text	One of 10 types
`confidence`	float	0-1 confidence score
`reasoning`	text	LLM reasoning
`key_differentiator`	text	What distinguishes the codes
`similarity`	float	Original cosine similarity
`bidirectional`	boolean	Symmetric relationship
`haiku`	text	5-7-5 creative summary
`classification_clarity`	float	How clear-cut
`source_inclusionary`	text[]	Defining terms for source
`source_exclusionary`	text[]	Excluding terms for source
`target_inclusionary`	text[]	Defining terms for target
`target_exclusionary`	text[]	Excluding terms for target
`run_id`	text	Classification run ID

pgvector must be enabled in the database:


CREATE EXTENSION IF NOT EXISTS vector;

Create an index on the embedding column for fast KNN:


CREATE INDEX ON hts_embeddings
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

Or for better recall at the cost of more memory:


CREATE INDEX ON hts_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

The application uses psycopg2 connection pooling:

Each connection initializes:

Application-level caching reduces database load: