CHIRPdb Data Dictionary

Data dictionary for the CHIRPdb database prepared for the Alan Turing Institute Data Study Group. See the dataset overview for context, provenance and limitations. Content conventions follow DAMA-DMBOK.

What the database contains

This dictionary documents the full CHIRPdb schema. The database as delivered is populated end to end: every pipeline stage has run over the whole corpus. Expect data in:

• Reports & text - documents, authors, sentences, and the report-derived incident facts in document_particulars and document_vessels. Two corpora sit here, separated by documents.jurisdiction: 796 UK MAIB reports ('UK') and 456 US NTSB reports ('US'), 1,252 documents in total.
• Incident metadata from the MAIB spreadsheets - occurrences (9,063), vessels (9,830), affected_persons (3,040), affected_persons_ppe (924), the report↔occurrence link carried on documents.occurrence_id, plus the vocabularies feeding them (taxonomy_terms, 410 terms across eight trees; ppe_items, 9). MAIB-only: NTSB publishes no equivalent dataset, so these tables describe UK incidents alone and no NTSB document carries an occurrence_id. This is much the larger incident record, and it is nearly disjoint from the report corpus - only 58 reports link to an occurrence (see documents).
• Findings from the record pass - safety_issues (11,254, of which 479 are record_type = 'safety_lesson'), recommendations (4,127), the recommendation_organisations links (3,658; 3,342 MAIB / 316 NTSB) and the organisations they resolve to (802). Every document has been through the pass: documents.records_pass_at is set on all 1,252.
• Passages - passages (15,381; 13,892 MAIB / 1,489 NTSB) and passage_sentences (15,939; 14,382 / 1,557), written by the record pass, one passage per record: the sentences a safety issue or recommendation was read from, so every record carries its own provenance. No general passage pass has run, so these hold record passages and nothing else.
• Narrative entities - narrative_entities (545,649) and narrative_entity_mentions (1,267,349): one entity per distinct thing per document, with every in-text mention resolved to it and located by character span.
• Embeddings - passage_embeddings carries a vector for all 15,381 passages under the active model, shield_code_embeddings all 452 code × strategy vectors, and passage_embedding_queue is drained (0 rows). See § 5.
• Seeded reference vocabularies - shield_layers / shield_code_categories / shield_codes (the full SHIELD taxonomy), narrative_entity_types (14 types, referenced by every entity row), taxonomy_terms and ppe_items. embedding_models and embedding_strategies are seeded on the same footing - they configure the embedding layer rather than describe incidents.

Five tables are empty as delivered:

• passage_shield_codes - the one analytical link the database is missing. The taxonomy is loaded and every passage is embedded, but no code has been attached to any passage. See the table's own section for what the embedding layer offers in its place.
• analysis - a spare table for a future per-passage pass. Nothing writes it.
• legislation and safety_issue_legislation - the legislation-attachment feature is not implemented.
• author_identifiers - nothing populates it at this stage.

Some columns of otherwise-populated tables are empty too, and they are flagged where they appear. The ones most likely to be reached for: sentences.relevance_score; every contact field on organisations and authors; safety_issues.name and recommendations.action (the analyst-authored summaries - the report's own wording is in the passage, not here); and every is_verified / verified_at pair.

How to read this document

Every column is classified in the Source column as one of:

A slashed label (Pipeline / AI-U) marks a column whose extractor is deterministic with a model fallback.

Four conventions apply throughout:

1. Row-level verification. Tables carrying an is_verified flag (passage_shield_codes, safety_issues, recommendations) hold AI-generated content whose verification status varies per row. Their content columns are marked AI-U (or Pipeline / AI-U where a deterministic extractor populates them today); treat rows where is_verified = true as AI-V. Sentences are the analogous case at document level: documents.status = 'verified' marks a document whose extracted text an analyst has confirmed.

   In this delivery, nothing has been verified. No row anywhere carries is_verified = true, no document is at status = 'verified', and the Analyst columns (safety_issues.name, recommendations.action) are empty. The AI-V and Analyst labels below therefore describe what a column would hold after review, not what it holds now - read every AI-U column as machine output no human has checked.

2. Sentence text. Text is extracted by a vision LLM reading a rendered image of each page, with deterministic passes (structural-metadata normalisation, line rejoin, content-loss repair) applied on top of the model output. Sentence text and structure columns are therefore marked AI-U, qualified by the parent document's status.
3. Two record sets, linked - not merged. Incident data comes from two sources kept in separate tables rather than reconciled into one row:
   • the MAIB Data Portal spreadsheet → occurrences and its children vessels / affected_persons, plus the vocabularies taxonomy_terms / ppe_items. Spreadsheet-only facts, UK-only; source_id / source_modified_at carry MAIB's per-record identifier and revision date, driving upsert on each refresh so only MAIB-revised rows are touched. Columns on these tables carry the MAIB label.
   • the report PDF, MAIB or NTSB → document_particulars (incident facts stated in the report) and document_vessels (vessels named in the report), each hanging off documents. Columns fed this way carry the Source label, which says nothing about which body published the report - read documents.jurisdiction for that.

   A MAIB report links to its occurrence through documents.occurrence_id, with documents.occurrence_matched_on recording how the link was made (publication_number or manual); several reports can point at the same occurrence (e.g. an interim report followed by the final one). NTSB documents have no occurrence to link to, so only the report-PDF set applies to them.

4. Jurisdiction split. documents.jurisdiction is the column that separates the corpora - 'UK' for MAIB, 'US' for NTSB - and everything owned by a document (sentences, document_particulars, document_vessels, passages, safety_issues, recommendations, narrative_entities) inherits that split through its document_id. Field coverage differs between the two: see the per-column notes on documents, document_particulars and document_vessels, and the fill-rate figures in the README.

All tables carry created_at and updated_at (timestamptz, defaulting to now(), with updated_at maintained by a BEFORE UPDATE trigger). These are listed once per table without repeating the explanation.

1. Reports and incident record

documents

One row per ingested report PDF - MAIB or NTSB. The root of the ownership chain: deleting a document cascades to everything extracted from it. 1,252 rows: 796 with jurisdiction = 'UK' (MAIB), 456 with 'US' (NTSB).

Keys: PK id. UNIQUE hash. FK author_id → authors(id) ON DELETE SET NULL. FK occurrence_id → occurrences(id) ON DELETE SET NULL. CHECK documents_lease_requires_start (claim_expires_at set implies claim_started_at set); CHECK documents_occurrence_link_has_provenance (occurrence_id set implies occurrence_matched_on set); CHECK occurrence_matched_on is null or in ('publication_number', 'manual'); CHECK documents_jurisdiction_check (jurisdiction is null or in ('UK', 'US')).

Document status lifecycle: queued → extracting → extracted → verified, with extracting → extraction_failed on an irrecoverable crash. extracted means the automated pipeline finished; verified means an analyst confirmed the results. The claim_* columns are the extraction lease used to recover documents stranded by a crashed worker. As delivered, all 1,252 documents are at extracted, and 201 carry extraction_fallback = true (182 MAIB, 19 NTSB).

The records_* columns track the separate record pass. As delivered every document has records_pass_at set - the pass has run over the whole corpus - and 981 carry records_fallback = true, meaning the deterministic extractor alone did not settle the result and an LLM route was used. That flag says how the records were reached, not whether they are wrong.

The report corpus and the spreadsheet barely overlap, and that is the database's sharpest limit. occurrence_id links only 58 reports to an occurrence. The cause is on the spreadsheet side: it only goes as far back as 2018. Since then, MAIB have investigated only 61 occurrences that get a publication_number, and 58 of which produced a report. So 738 of the 796 MAIB reports have no occurrence counterpart, and 9,005 occurrences have no adjoining report.

document_particulars

Incident facts as stated in the report PDF - the report-path counterpart to the spreadsheet's occurrences. One row per document (1:1), so 1,252 rows across both corpora. Columns are populated only where the report states the fact, and NTSB reports state fewer of them: of 456 NTSB rows, accident_date is set on 198, accident_location 208, accident_type 130, severity 71, loss_of_life 33 (MAIB, of 796: 796 / 731 / 279 / 305 / 183).

Keys: PK document_id. FK document_id → documents(id) ON DELETE CASCADE. CHECK loss_of_life is null or ≥ 0.

accident_type does not reliably hold the accident type. The extraction pass frequently picks up the report's statement of the voyage or vessel-operation type (what the vessel was doing) rather than what happened to it, so the column mixes the two. Do not treat it as an accident classification without reading the values first; for MAIB documents, the spreadsheet's occurrences.main_event_term_id (the main_event taxonomy) is the classification to use instead.

document_vessels

Vessels named in the report PDF - the report-path counterpart to the spreadsheet's vessels. The schema allows a document many vessel rows, but as delivered there are 1,252 rows and exactly one per document: an incident involving several vessels is not represented as several rows here. Coverage differs by corpus:

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE.

authors

Investigating bodies that publish reports (MAIB, NTSB). Shared reference data, not owned by any document. 22 rows as delivered, and they are not yet canonicalised: the same body appears under several name variants taken from PDF metadata (e.g. MAIB, Marine Accident Investigation Branch, www.maib.gov.uk; NTSB, National Transportation Safety Board, and the typo NSTB), alongside rows that are individual people named as the PDF's author. Group by the body yourself - or filter on documents.jurisdiction - rather than trusting authors.name as a key.

Keys: PK id.

author_identifiers

Curated identifiers that canonicalise an author, one row per identifier - so name variants (e.g. "MAIB" vs "Maritime Accident Investigation Branch") resolve to a single author rather than many near-duplicates.

Keys: PK id. FK author_id → authors(id) ON DELETE CASCADE.

occurrences

One incident as recorded in the MAIB Data Portal spreadsheet - what happened, when, where, how severe. Purely spreadsheet-sourced; it holds no report-PDF fields, and it is UK-only: NTSB has no counterpart feed, so this table and its children describe MAIB incidents alone. The report(s) describing an occurrence point to it via documents.occurrence_id, and the report's own view of the incident lives in document_particulars.

9,063 rows. Well filled: short_description, description and severity are set on every row, latitude / longitude on 9,062, main_event_term_id on 9,061. Only 61 rows carry a publication_number, which is why so few reports link (see documents).

Keys: PK id. UNIQUE source_id. FK main_event_term_id → taxonomy_terms(id) ON DELETE SET NULL.

main_event_term_id references the shared taxonomy_terms tree (see §4). Report-PDF incident facts (accident type, named location, loss of life) are not held here - they live in document_particulars, reached via documents.occurrence_id. A report is linked to its occurrence by documents.occurrence_id (the FK sits on documents, so several reports can share one occurrence).

vessels

Vessels involved in a spreadsheet occurrence - the particulars from the MAIB Data Portal, and so UK-only. An occurrence involves one or more vessels. Vessels as named in the report PDF are separate: see document_vessels.

9,830 rows over 9,063 occurrences, every one attached to an occurrence. ship_type_term_id and vessel_category_term_id are set on every row; place_on_board_term_id on 7,995. No vessel name is held here - the MAIB extract does not publish one.

Keys: PK id. UNIQUE source_id. FK occurrence_id → occurrences(id) ON DELETE CASCADE. FKs vessel_category_term_id, ship_type_term_id, place_on_board_term_id → taxonomy_terms(id) ON DELETE SET NULL.

Death and injury counts are not stored on vessels; count them from affected_persons by person_type and physical-condition outcome so there is one source of truth. Vessel classification lives entirely in taxonomy_terms references (vessel_category_term_id, ship_type_term_id); the report's own vessel names/types sit apart in document_vessels.

affected_persons

People affected aboard a vessel of a spreadsheet occurrence, so UK-only like its parents. A vessel carries zero or more affected persons. No names are stored - demographic and injury attributes only.

3,040 rows, every one attached to a vessel - so the great majority of the 9,830 vessels record no affected person. physical_condition_term_id is set on every row, deviation_term_id on 3,035, injury_type_term_id on 3,013, place_on_board_term_id on 2,854. person_type is Crew (2,132), Passenger (767) or Other (141); gender is Male (2,006), Female (743), Unknown (283) or Not available - PCF (8).

Keys: PK id. UNIQUE source_id. FK vessel_id → vessels(id) ON DELETE CASCADE. FKs physical_condition_term_id, injury_type_term_id, deviation_term_id, place_on_board_term_id → taxonomy_terms(id).

Injury type is a taxonomy_terms reference (injury_type_term_id). PPE worn and lacking is recorded per person in affected_persons_ppe (below), one row per item.

affected_persons_ppe

Junction: which PPE items a person wore or lacked. One row per person per item, split out of the spreadsheet's comma-joined PPE_Used / PPE_Deficient values so items can be counted and filtered.

924 rows - 575 used, 349 deficient - covering a small minority of the 3,040 affected persons. Absence of a row is not evidence the item was absent: the spreadsheet records PPE only where MAIB captured it.

Keys: Composite PK (affected_person_id, ppe_item_id, status). FKs affected_person_id → affected_persons(id), ppe_item_id → ppe_items(id).

2. Parsed text

sentences

Every sentence of every report, in original reading order - 341,057 rows, 293,454 from MAIB documents and 47,603 from NTSB ones. The atomic unit of text; everything in the analysis layer traces back here. Sentences carry no jurisdiction of their own: join through document_id to split the corpora. Text is produced by a vision LLM reading a rendered image of each page, with deterministic passes applied on top - the stored text reflects that output, with the parent document's status indicating how much to trust it.

There is no sections table: a row with text_type = 'heading' is a section boundary, and a section's contents are the sentences whose sort_order falls between that heading and the next.

By text_type: 155,117 paragraph, 77,222 list_item, 47,648 heading, 32,532 frontmatter, 17,455 caption, 7,907 footnote, 3,176 table. frontmatter is cover pages, contents lists and boilerplate - exclude it from most analyses.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. CHECK heading_level BETWEEN 1 AND 6. CHECK page_number is null or ≥ 1.

passages

A grouped passage of consecutive sentences - the unit the analysis pipeline works on. Membership lives in passage_sentences, so the grouping strategy can change without touching sentence rows; the passage also carries its own document_id rather than relying on the transitive path through its sentences, which does not survive a re-extraction.

15,381 rows as delivered - 13,892 from MAIB documents, 1,489 from NTSB ones - all written by the record pass, every one carrying a document_id and non-null source_text. That count is exactly the number of findings (11,254 safety issues + 4,127 recommendations), one passage each.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. CHECK passages_source_valid (source in ('record_pass', 'manual')). A trigger (trg_validate_passage_document) rejects a document_id that contradicts the document derived from the passage's sentences; it validates rather than derives, so a passage with no sentences yet may name its document freely.

document_id is nullable only because two cases genuinely have no answer - a passage orphaned by a re-ingestion, and a passage whose sentences span more than one document - and neither occurs in the delivered data. Deleting a document deletes its passages, matching sentences, safety_issues and recommendations.

passage_sentences

Junction: which sentences belong to which passage, and in what order. 15,939 rows across the 15,381 passages - 14,382 from MAIB documents and 1,557 from NTSB ones. Record passages are short, most of them a single sentence.

Keys: Composite PK (passage_id, sentence_id). FKs passage_id → passages(id), sentence_id → sentences(id), both ON DELETE CASCADE.

narrative_entities

Canonical named things - people, vessels, organisations, locations, equipment and nine more types - extracted from a document's text, one row per distinct thing per document, with a normalised canonical name. Coreference resolution is persisted here: many surface forms, one entity.

545,649 rows as delivered: 446,735 from MAIB documents and 98,914 from NTSB ones, roughly 436 entities per report. By type, the largest are equipment (118,152), condition (61,836), location (60,738), procedure (54,427) and document (39,787); 10,942 rows fell outside the vocabulary and carry type = 'other' with the extraction's own suggestion in proposed_type.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. FK type → narrative_entity_types(name) ON DELETE RESTRICT (natural-key FK). A database trigger enforces that an entity's mentions can only anchor to sentences of the entity's own document.

narrative_entity_mentions

Each occurrence of an entity in the text: the exact words used and their character position within the sentence.

1,267,349 rows as delivered: 1,032,182 from MAIB documents and 235,167 from NTSB ones. By mention_type: 894,775 definite_description, 251,847 name, 70,390 split_antecedent, 50,337 pronoun. confidence is populated on every row.

Keys: PK id. FKs narrative_entity_id → narrative_entities(id), sentence_id → sentences(id), both ON DELETE CASCADE. CHECK: char_span non-empty with lower bound ≥ 0. CHECK: confidence null or between 0 and 1.

3. Analysis and findings

analysis

Free-form per-passage analysis output - somewhere a future analytical pass can record results without a schema change. Several passes can sit side by side on the same passage. No verification flag exists, so any content here would be unverified.

Empty as delivered. Nothing in the current pipeline writes it.

Keys: PK id. FK passage_id → passages(id) ON DELETE CASCADE.

passage_shield_codes

Application of a SHIELD causal-factor code to a passage - the link that makes incidents comparable and countable. Verification lives on the association itself, because an analyst verifies "this code applies to this passage".

Empty as delivered. All 113 SHIELD codes and all 15,381 passages are present and embedded, but no code has been matched to a passage yet.

What exists in place of it is a suggestion function, match_shield_codes(p_passage_id uuid, p_strategy text DEFAULT 'category_definition', p_limit integer DEFAULT 5, p_model text DEFAULT NULL). It ranks SHIELD codes by cosine proximity to the passage's stored vector and returns (shield_code_id, code, title, category, layer, similarity), where similarity is 1 - cosine_distance - higher is closer. It reads; it never writes. Populating this table from its output, and deciding what similarity is worth accepting, is open work.

Keys: Composite PK (passage_id, shield_code_id). FKs passage_id → passages(id), shield_code_id → shield_codes(id), both ON DELETE CASCADE.

safety_issues

A named safety concern the report raises, owned by a document and optionally traced to the passage it was drawn from. passage_id cascades: a passage is deleted when it loses its last sentence, and the finding goes with it, because a finding whose source text no longer exists is not evidence of anything. The column stays nullable because a finding created standalone through the API has no passage at all.

11,254 rows as delivered: 10,038 from MAIB documents and 1,216 from NTSB ones. Split by record_type, 10,775 are safety_issue (9,734 MAIB / 1,041 NTSB) and 479 are safety_lesson (304 / 175). Every row came from the record pass; none is analyst-verified.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. FK passage_id → passages(id) ON DELETE CASCADE. CHECK safety_issues_record_type_valid (record_type in ('safety_issue', 'safety_lesson')). CHECK safety_issues_source_valid (source in ('record_pass', 'manual')). Partial index on record_type where record_type <> 'safety_issue' - nearly every row is a safety_issue, so lessons are the selective side worth indexing. A trigger validates (and derives, where unambiguous) document_id against the passage's actual document, falling back to passages.document_id when the passage has no sentences yet.

is_contributory and is_addressed split MAIB's four-way classification of safety issues (directly contributing, addressed or resulted in recommendations / other directly contributing / not directly contributing, addressed or resulted in recommendations / other not directly contributing) into the two independent facts it actually encodes. Both are nullable because only modern-era reports classify at all: mid-era reports print bare FINDINGS / OTHER FINDINGS, older ones list findings under a CONCLUSIONS part, and bulletins do not classify. Null means the report did not say - never "unknown, pending backfill". As delivered, is_contributory is set on 2,975 of the 11,254 rows and is_addressed on 3,035.

record_type distinguishes the two kinds of free-text observation the table holds. A safety_lesson is general advice to the industry, printed under a SAFETY LESSONS heading in bulletins and in the safety-flyer annexes of full reports; for several bulletins it is the only safety content in the document. Lessons share this table because they have the same shape as issues - free text, no addressee, no identifier - and simply leave the two classification booleans null.

recommendations

An action the report calls for, in the report's own words. Same shape and delete semantics as safety_issues.

4,127 rows as delivered: 3,854 from MAIB documents and 273 from NTSB ones. reference_number is set on 2,414 and addressee on 3,159; none is analyst-verified.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. FK passage_id → passages(id) ON DELETE CASCADE. CHECK recommendations_source_valid (source in ('record_pass', 'manual')). Partial index on reference_number where not null. Same document_id validation trigger as safety_issues.

reference_number is the publisher's sequential identifier across its whole annual output, so one report's recommendations run consecutively and a recommendation made in one report can be cited by name in a later one. Coverage is uneven: every NTSB recommendation carries one (273 of 273), but only 2,141 of the 3,854 MAIB ones do, the rest predating the numbering scheme.

addressee keeps the report's own wording rather than pointing at organisations, because the party addressed is often a class of people ("All UK Operators of small commercial high speed craft…") rather than a nameable company. It is set on 2,904 MAIB and 255 NTSB rows.

safety_issue_legislation

Junction linking a safety issue to legislation that bears on it, with the passage of text that made the connection - so the citation itself is traceable. Empty, along with legislation itself.

Keys: Composite PK (safety_issue_id, legislation_id). FKs to safety_issues(id) and legislation(id), both ON DELETE CASCADE.

recommendation_organisations

Junction linking a recommendation to the organisations it is directed at. Implementation is tracked per organisation, because each may act on the same recommendation at a different time.

Written by the record pass: each recommendation's addressee is cleaned of the report's own connective wording, split into the parties it names, and each party resolved - one row per party. That is why this is a junction rather than a foreign key on recommendations: "The International Chamber of Shipping, the International Maritime Pilots' Association and the International Harbour Masters' Association" is one addressee and three answerable bodies. The links are an enrichment; the verbatim addressee on the recommendation stays the record of who was addressed, and is what to fall back on where resolution produced no row.

3,658 rows as delivered - 3,342 from MAIB documents and 316 from NTSB ones - linking the 3,159 recommendations that name an addressee to the 802 organisations those addressees resolved to. Nothing has been marked implemented; is_implemented and implemented_at are for CHIRP's own follow-up and are empty.

Keys: Composite PK (recommendation_id, organisation_id). FKs to recommendations(id) and organisations(id), both ON DELETE CASCADE.

4. Lookup and reference tables

shield_layers

The four layers of the SHIELD taxonomy - the top tier, grouping categories. Seeded reference data (4 layers). Referenced by natural key from shield_code_categories.layer, so a category row names its layer without a join.

Keys: PK name. UNIQUE sort_order.

shield_code_categories

Categories grouping SHIELD codes, each belonging to one layer. Seeded reference data (24 categories).

Keys: PK id. UNIQUE name, UNIQUE code. FK layer → shield_layers(name) ON UPDATE CASCADE (a layer can be renamed from its own row; categories follow).

shield_codes

The SHIELD causal-factor taxonomy itself - CHIRP's controlled vocabulary of human factors in maritime incidents. Seeded reference data (113 codes in 24 categories under four layers); see Appendix A.

Keys: PK id. UNIQUE code. FK category_id → shield_code_categories(id) ON DELETE SET NULL (an uncategorised code remains valid).

narrative_entity_types

Controlled vocabulary of entity types. Seeded reference data (14 types); referenced by natural key from narrative_entities.type, which is ON DELETE RESTRICT - a type in use cannot be removed.

The 14: person, group_of_people, organisation, vessel, equipment, cargo_or_substance, location, event, condition, procedure, document, legislation, time, other. other is the escape hatch - an entity that fits none of the thirteen takes it and records what the extraction would have called it in narrative_entities.proposed_type, so the vocabulary can be grown from what the corpus actually contains rather than guessed at up front.

Keys: PK id. UNIQUE name.

legislation

Legislation register shared across safety issues - one law can back findings in many reports. When this feature ships, an agentic AI attaches relevant legislation to a safety issue, sourced from the report itself or pulled from external legal sources; every attachment needs analyst verification. Not yet implemented - empty in this database.

Keys: PK id.

organisations

Parties that recommendations are directed at, resolved from each recommendation's addressee text as the record pass runs.

A row is not always a company. Reports frequently address a class of people - "All UK Operators of small commercial high speed craft…" - and the same class recurs across reports, so it earns a row of its own; kind says which sort of row it is. kind is guessed from the shape of the name and is an analyst's to correct.

802 rows as delivered: 710 company, 92 class. 71 are curated seeds; the other 731 were minted by resolution. Every contact column is empty - contact_name, email, phone_number, address and website_url are null on all 802. They exist for CHIRP's own follow-up workflow, which has not run; nothing in this delivery populates them.

One company is one row, enforced rather than intended: match_key is name normalised - case, accents, punctuation, a leading "The" and a trailing legal suffix folded away - and it is UNIQUE, so Poseidon Trawlers Ltd, Poseidon Trawlers Limited and The Poseidon Trawlers Ltd. cannot become three rows. Two limits follow. Resolution will not merge a name into one it merely resembles, so genuine near-duplicates that normalise differently survive as separate rows; and it trusts a curated row's spelling without re-parsing it, which is what is_curated marks and why only a person may set it.

Keys: PK id. UNIQUE match_key.

organisation_identifiers

Curated identifiers that canonicalise an organisation, one row per identifier - so spelling variants resolve to a single organisation rather than many near-duplicates. Mirrors author_identifiers.

It holds the spellings organisations.match_key cannot reach on its own: acronyms (BPA, UKMPG, OCIMF), predecessor names (the Marine Safety Agency, now the MCA), house styles and outright typos. Only a person adds one - by seeding, by merging two rows, or implicitly by renaming an organisation, which keeps the old spelling here so existing addressees still resolve. The record pass writes none.

The unique index on match_key spans the whole table, not one organisation: one spelling must never point at two organisations.

86 rows as delivered, all seeds. Coverage is therefore only as good as what was seeded - near-duplicate organisations that nobody has spotted remain separate rows.

Keys: PK id. UNIQUE match_key. FK organisation_id → organisations(id) ON DELETE CASCADE.

taxonomy_terms

Shared lookup for every tree-shaped MAIB classification - spreadsheet-sourced, so referenced only from the UK-side tables. One row per term; parent_id links a term to its parent; taxonomy names which tree the term belongs to. Built from the spreadsheet's parallel level-columns (_L1, _L2, …), collapsed into one tree so a query can group by a type without knowing which level each row happens to fill.

Keys: PK id. UNIQUE (id, taxonomy) and (taxonomy, parent_id, label). Composite self-FK (parent_id, taxonomy) → (id, taxonomy) ON UPDATE CASCADE ON DELETE CASCADE - a term's parent must live in the same taxonomy.

410 terms across the eight trees: ship_type (98), vessel_place_on_board (83), person_place_on_board (82), deviation (48), injury_type (44), main_event (32), vessel_category (15), physical_condition (8).

Referenced from: occurrences.main_event_term_id; vessels.vessel_category_term_id, ship_type_term_id, place_on_board_term_id; affected_persons.physical_condition_term_id, injury_type_term_id, deviation_term_id, place_on_board_term_id. Place-on-board is split into two trees - vessel_place_on_board (referenced by vessels) and person_place_on_board (referenced by affected_persons).

ppe_items

Controlled vocabulary of PPE items, split out of the spreadsheet's comma-joined PPE_Used / PPE_Deficient cells. Referenced from affected_persons_ppe. Nine items: life jacket, head protection, hand and foot protection, face and eye protection, anti-exposure suit, protection from falls, working clothes, protective clothing, hearing protection.

Keys: PK id. UNIQUE label.

5. Embeddings

The pgvector layer behind SHIELD code suggestion: passages and SHIELD codes encoded under a registered model and compared by cosine distance. No API route exposes any of it - a separate worker container is the only writer of vectors, and the read path is the single function match_shield_codes, documented under passage_shield_codes.

Fully populated as delivered. embedding_models holds the single active encoder (BAAI/bge-large-en-v1.5, 1024 dimensions) and embedding_strategies the four assembly strategies. passage_embeddings carries a vector for every one of the 15,381 passages under that model, shield_code_embeddings all 452 vectors (113 codes × 4 strategies), and passage_embedding_queue is drained to 0 - no passage is awaiting or failing embedding.

Two properties are worth knowing before reading the tables:

• Vectors are hash-addressed, not time-addressed. Every embedding row stores the SHA-256 of the exact text it was computed from, so a vector says what it embedded rather than when. To check whether one is current, compare its source_text_sha256 against the parent's - passages.source_text_sha256 on one side, text_sha256(shield_code_embedding_text(...)) on the other. Do not read updated_at as a freshness signal: a superseded vector sits in the table between a text change and the re-run that replaces it, so a hash mismatch means out of date, not corrupt. Writes are guarded (see § Database-enforced behaviour), which prevents a wrong vector, not a stale one.
• Model generations coexist. model_id sits in every primary key here, so changing encoder is a new embedding_models row plus a re-run, never an in-place edit. A unique partial index allows only one row with is_active = true, and that is the row the queue and worker act on.

The embedding columns are the only model output in this section. They are labelled AI-U because an NLP model produces them, but unlike other AI-U columns they assert nothing about an incident and no verification workflow applies - a vector is either current for its text or absent.

embedding_models

Registry of encoders. One row per model; the worker and queue use the single active one. Seeded reference data (1 row: BAAI/bge-large-en-v1.5).

Keys: PK id. UNIQUE name.

embedding_strategies

Which parts of the SHIELD taxonomy a named strategy assembles into the text that gets embedded. One boolean per taxonomy field, tier by tier - so a new combination is an INSERT, not a schema change, and the name-to-fields mapping lives in exactly one place. Seeded reference data (4 strategies).

The database function shield_code_embedding_text(shield_code_id, strategy) reads the row named here and joins the selected fields; the seeder never rebuilds those strings in Python, so the two sides cannot drift apart.

Keys: PK name. Referenced by shield_code_embeddings.strategy.

The four seeded strategies, widest to narrowest: full (every field the taxonomy holds), category_definition (category name and definition grounding the code's title and definition - the shortlist strategy), title_definition (title and definition, no category), definition (the definition alone, where something downstream wants exactly one code).

passage_embeddings

One vector per passage per model. Written only by the embedding worker. 15,381 rows as delivered - one for every passage, under the single active model (13,892 MAIB, 1,489 NTSB).

Keys: PK (passage_id, model_id). FK passage_id → passages(id) ON DELETE CASCADE; FK model_id → embedding_models(id) ON DELETE RESTRICT (a model with vectors cannot be deleted out from under them). HNSW index on embedding (vector_cosine_ops).

shield_code_embeddings

One vector per SHIELD code per model per strategy - so the same code carries several vectors, one for each way of assembling its text. Written only by the seeder. At full coverage this is 113 codes × 4 strategies = 452 rows per model.

452 rows as delivered - full coverage under the single active model.

Keys: PK (shield_code_id, model_id, strategy). FK shield_code_id → shield_codes(id) ON DELETE CASCADE; FK model_id → embedding_models(id) ON DELETE RESTRICT; FK strategy → embedding_strategies(name) ON DELETE RESTRICT. No vector index - the table is small enough to scan.

passage_embedding_queue

Work list of passages awaiting embedding - one row per passage per model. Filled by trigger, not by application code: changing a passage's text enqueues it, and emptying a passage of text deletes its queue rows and its vectors together. A row leaves the queue when its vector is written. Empty as delivered, because every passage has been embedded - a non-zero depth means passage text changed after its vector was computed.

Keys: PK (passage_id, model_id). FK passage_id → passages(id) ON DELETE CASCADE; FK model_id → embedding_models(id) ON DELETE CASCADE.

Database-enforced behaviour

Constraints participants should know about when writing to (or reasoning about) the data:

• updated_at is trigger-maintained on every table.
• verified_at / implemented_at are trigger-stamped from their boolean flags and cannot drift: application code writes only the boolean.
• passage_sentences.sort_order is auto-assigned by trigger when inserted null.
• Triggers validate that safety_issues.document_id and recommendations.document_id agree with the document derived from their passage_id - falling back to passages.document_id when the passage has no sentences attached yet - and that a narrative entity's mentions anchor only to sentences of the entity's own document.
• passages.document_id is likewise trigger-validated (trg_validate_passage_document): it may not contradict the document derived from the passage's sentences, but a passage with no sentences yet may name its document freely. Deleting a document cascades to its passages.
• documents.jurisdiction is CHECK-restricted to 'UK' or 'US' (or null); safety_issues.record_type to 'safety_issue' or 'safety_lesson', NOT NULL, defaulting to safety_issue; organisations.kind is a NOT NULL enum defaulting to company.
• recommendations.reference_number is indexed but deliberately not unique - re-ingesting a document rewrites its records, so uniqueness would fight regeneration rather than protect anything.
• tsvector columns (title_tsv, text_tsv, source_text_tsv, name_tsv, action_tsv) are generated columns and cannot be written directly.
• Embedding rows are hash-addressed: trg_validate_passage_embedding and trg_validate_shield_code_embedding reject any vector whose source_text_sha256 disagrees with its parent's text at write time, or whose width disagrees with its model's dimensions. This stops a wrong vector being written; it does not delete an existing vector when its parent text later changes, so compare hashes rather than timestamps to tell whether one is current.
• passage_embedding_queue is filled by trigger, never by application code: trg_enqueue_passage_embedding enqueues a passage whose source_text changed, and trg_clear_passage_embeddings deletes a passage's queue rows and vectors together when its text becomes null. Both fire on passages.source_text, so sentence-level edits reach them through the passage derivation stack.
• embedding_models.dimensions is CHECK-restricted to 1024 (the width the vector columns are declared at) and provider to sentence_transformers or openai. A unique partial index (embedding_models_single_active_idx) permits only one row with is_active = true. embedding_models deleted out from under existing vectors is blocked by ON DELETE RESTRICT, as is an embedding_strategies row still named by a shield_code_embeddings row.
• organisations.match_key and organisation_identifiers.match_key are generated (org_match_key(...)), stored, NOT NULL and UNIQUE - one company is one row and one spelling points at one organisation, whatever a caller intends.
• source_id is UNIQUE on occurrences, vessels and affected_persons (and NOT NULL on occurrences / vessels) - the key MAIB spreadsheet ingestion upserts on (ON CONFLICT (source_id) DO UPDATE), touching only rows whose source_modified_at shows MAIB revised them.
• documents.hash is UNIQUE (dedupes re-ingestion). The extraction lease is guarded by a CHECK: claim_expires_at may be set only when claim_started_at is.
• taxonomy_terms enforces that a term's parent is in the same taxonomy (composite FK on (parent_id, taxonomy)).
• documents.occurrence_matched_on is CHECK-restricted to publication_number or manual, and is required whenever documents.occurrence_id is set; document_particulars.loss_of_life must be null or ≥ 0; sentences.page_number must be null or ≥ 1.

Appendix A - SHIELD taxonomy

SHIELD is CHIRP's controlled taxonomy of human factors in maritime incidents: 113 factors in 24 categories under four layers. Each factor has a short code (e.g. PPC2), a title (e.g. "Fatigue") and a full definition; categories and layers carry their own codes and definitions too. The taxonomy is seeded reference data - the complete list is reproduced below and lives in the shield_layers, shield_code_categories and shield_codes tables.

SHIELD is presented as an iceberg: the visible acts at the tip, the preconditions that set them up below the waterline, the operational leadership decisions deeper still, and the organisation that shapes all of them at the base. For the model and its rationale, see the published paper: SHIELD Human Factors Taxonomy and Database for Learning from Aviation and Maritime Safety Occurrences (Safety, 2023).

The full taxonomy

All 113 factors, grouped by their four layers and 24 categories. Layers appear in code-prefix order (A, L, O, P); the summary table above gives the taxonomy's own top-to-bottom order.

A · ACTS

Communicating

Intentional Deviation

Perception

Planning and Decision Making

Response Execution

L · OPERATIONAL LEADERSHIP

Operations Planning

Personnel Leadership

Task Leadership

O · ORGANISATION

Culture

Economy and Business

Resources

Safety Management

P · PRECONDITIONS

Awareness

Competence, Skills and Capability

Drugs and Nutrition

Equipment and Workplace

Interpersonal Communication

Memory

Mental Workload

Misperception

Personal Factors

Physical Environment

Physiological Condition

Team/Group