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, but the database as delivered populates only two of its layers. 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, vessels, affected_persons, affected_persons_ppe, the report↔occurrence link carried on documents.occurrence_id, plus the vocabularies feeding them (taxonomy_terms, ppe_items). MAIB-only: NTSB publishes no equivalent dataset, so these tables describe UK incidents alone and no NTSB document carries an occurrence_id.
• Seeded reference vocabularies - shield_codes / shield_code_categories (the full SHIELD taxonomy) and narrative_entity_types. Loaded as controlled vocabulary; nothing references them yet.

The analysis, findings and entity layers are empty - no chunking, SHIELD tagging, safety-issue/recommendation extraction or entity resolution has been run:

• chunks, chunk_sentences, analysis - empty (no chunking pass).
• chunk_shield_codes - empty: the SHIELD taxonomy is loaded but no code is applied to any passage.
• safety_issues, safety_issue_legislation, recommendations, recommendation_organisations - empty (no findings extraction).
• narrative_entities, narrative_entity_mentions - empty (no NER/coreference pass).
• organisations, legislation, and the identifier tables (author_identifiers, organisation_identifiers) - empty (nothing populates them at this stage).

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 (chunk_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.
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:
4. 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.
5. 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.

1. 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, and the analysis layer once populated) 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. Exceptions, noted on their entries: the vocabulary and junction tables taxonomy_terms, ppe_items, affected_persons_ppe carry no audit timestamps.

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).

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. A document names zero or more vessels (1,252 rows as delivered, one per document). Coverage differs by corpus: vessel_name is set on 400 of 456 NTSB rows and 299 of 796 MAIB rows, vessel_type on 395 / 796, while port_of_origin and destination are populated for MAIB only (286 and 262) and empty for every NTSB row.

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.

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.

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.

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. Carries no audit timestamps.

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.

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.

chunks

A grouped passage of consecutive sentences - the unit the analysis pipeline works on. A chunk's membership still lives in chunk_sentences, so chunking strategy can change without touching sentence records, but the chunk also carries its own document_id: the transitive path chunk_sentences → sentences → documents disappears when a document is re-extracted (its sentences are rewritten and the junction rows cascade away), which used to strand chunks unreachable from any document, and a chunk needs to know its document before any sentences are attached to it.

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

document_id is nullable for two reasons, both meaning "the data does not say": a chunk left over from a re-ingestion has no surviving sentences to derive a document from, and a chunk whose sentences span more than one document is not assigned one rather than being given an arbitrary guess. Deleting a document now deletes its chunks, matching sentences, safety_issues and recommendations.

chunk_sentences

Junction: which sentences belong to which chunk, and in what order.

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

narrative_entities

Canonical named things (people, vessels, organisations, locations, equipment) 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.

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.

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.

3. Analysis and findings

analysis

Free-form per-chunk analysis output. Different analytical passes can sit side by side on the same passage. No verification flag exists on this table - treat all content as unverified.

Keys: PK id. FK chunk_id → chunks(id) ON DELETE CASCADE.

chunk_shield_codes

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

Keys: Composite PK (chunk_id, shield_code_id). FKs chunk_id → chunks(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 chunk it was drawn from. chunk_id is provenance, not ownership: chunks are pipeline artefacts and may be regenerated, so the link is SET NULL rather than cascade - a verified finding survives re-chunking.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. FK chunk_id → chunks(id) ON DELETE SET NULL. CHECK safety_issues_record_type_valid (record_type in ('safety_issue', 'safety_lesson')). 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 chunk's actual document, falling back to chunks.document_id when the chunk 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".

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.

Keys: PK id. FK document_id → documents(id) ON DELETE CASCADE. FK chunk_id → chunks(id) ON DELETE SET NULL. 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. It is not unique in this table by design: re-ingesting a document rewrites its records, and roughly 60% of the corpus predates 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; the structured link, when made, is layered on top in recommendation_organisations and never replaces this text.

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.

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.

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

4. Lookup and reference tables

shield_code_categories

Categories grouping SHIELD codes. Seeded reference data (20 categories).

Keys: PK id. UNIQUE name.

shield_codes

The SHIELD causal-factor taxonomy itself - CHIRP's controlled vocabulary of human factors in maritime incidents. Seeded reference data (96 codes in 20 categories under three top-level groups); 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 (5 types); referenced by natural key from narrative_entities.type.

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. Identified by the AI as it extracts recommendations - it works out which party each recommendation points to. Contact details are essential for a company: they let CHIRP track whether a recommendation was implemented, and chase or escalate to the organisation if not. The AI fills contact fields from the report text where it can, otherwise from a web search, otherwise they are added manually; all are shown to the CHIRP user to check and audit.

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. A class has no contact, which is why contact_name is nullable.

Keys: PK id.

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.

Keys: PK id. 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. Carries no audit timestamps.

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.

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. Carries no audit timestamps.

Keys: PK id. UNIQUE label.

Database-enforced behaviour

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

• updated_at is trigger-maintained on every table except the vocabulary/junction tables taxonomy_terms, ppe_items, affected_persons_ppe, which carry no timestamps.
• verified_at / implemented_at are trigger-stamped from their boolean flags and cannot drift: application code writes only the boolean.
• chunk_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 chunk_id - falling back to chunks.document_id when the chunk has no sentences attached yet - and that a narrative entity's mentions anchor only to sentences of the entity's own document.
• chunks.document_id is likewise trigger-validated (trg_validate_chunk_document): it may not contradict the document derived from the chunk's sentences, but a chunk with no sentences yet may name its document freely. Deleting a document cascades to its chunks.
• 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, name_tsv, action_tsv) are generated columns and cannot be written directly.
• 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: 96 codes in 20 categories under three top-level groups. Each code has a short identifier (e.g. PPC2), a title (e.g. "Fatigue") and a full definition. The taxonomy is seeded reference data - the complete list is reproduced below and lives in the shield_codes and shield_code_categories tables.

SHIELD is presented as an iceberg: the visible acts at the tip, the preconditions that set them up below the waterline, and the operational leadership decisions deeper still. For the model and its rationale, see the published paper: A New Taxonomy for the Analysis of Human Factors in Maritime Incidents (Safety, 2023).

The full taxonomy

All 96 codes, grouped by their three top-level groups and 20 categories.

A · ACTS

Communicating

Intentional Deviation

Perception

Planning and Decision Making

Response Execution

L · OPERATIONAL LEADERSHIP

Operations Planning

Personnel Leadership

Task Leadership

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