Skip to main content

Bulk Data Schema

Pandects publishes its full public dataset as a MariaDB dump on Cloudflare R2. This page documents every table in that dump.

Getting the dump​

Restore into a local MariaDB database:

curl -LO https://bulk.pandects.org/dumps/latest.sql.gz
gunzip latest.sql.gz
mysql -e "CREATE DATABASE pandects"
mysql pandects < latest.sql

The dump targets MariaDB 11+. sections.embedding uses the MariaDB VECTOR type; on servers without vector support, load the dump with that column's DDL adjusted or skip the sections table.

This page describes each table and how the tables relate. Column-level documentation lives in the interactive schema at dbdocs.io, along with an ER diagram and index definitions.

Conventions​

  • UUIDs are stored as char(36) strings.
  • Boolean flags are tinyint(1) with values 0/1.
  • Timestamps are UTC.
  • Money columns are USD.

Agreements and documents​

The corpus itself: one row per merger agreement, plus the versioned XML conversions, the per-section breakdown of the latest XML, and the clause-level spans carved out of sections.

agreements​

One row per merger agreement. Rows are discovered from SEC EDGAR filings (or the original DMA seed corpus) and enriched with AI-extracted deal metadata. This is the root table: nearly every other table references agreement_uuid.

Primary key: agreement_uuid

Relationships:

  • agreement_counsel.agreement_uuid → agreements.agreement_uuid (one-to-many)
  • sections.agreement_uuid → agreements.agreement_uuid (one-to-many)
  • clauses.agreement_uuid → agreements.agreement_uuid (one-to-many)
  • xml.agreement_uuid → agreements.agreement_uuid (one-to-many)
  • latest_sections_search.agreement_uuid → agreements.agreement_uuid (one-to-many)
  • agreement_index_summary.agreement_uuid ↔ agreements.agreement_uuid (one-to-one)

xml​

Versioned XML conversions of each agreement. The ETL pipeline converts the raw filing into structured XML (articles, sections, sub-clauses); re-runs produce new versions rather than overwriting. Use latest = 1 and status = 'verified' for the current public text of an agreement.

Primary key: agreement_uuid, version

Relationships:

  • xml.agreement_uuid → agreements.agreement_uuid (many-to-one)

sections​

One row per section of each agreement's XML, in document order. This is the main unit of retrieval in the app and API: sections carry the section text, taxonomy labels, and a vector embedding.

Primary key: section_uuid

Relationships:

  • sections.agreement_uuid → agreements.agreement_uuid (many-to-one)
  • clauses.section_uuid → sections.section_uuid (one-to-many)
  • latest_sections_search.section_uuid ↔ sections.section_uuid (one-to-one)

clauses​

Clause-level spans carved out of sections for clause-taxonomy modules. Each row is a contiguous span of a section's text. Only sections relevant to a module are carved; currently the only module is tax.

Primary key: clause_uuid

Relationships:

  • clauses.agreement_uuid → agreements.agreement_uuid (many-to-one)
  • clauses.section_uuid → sections.section_uuid (many-to-one)
  • tax_clause_assignments.clause_uuid → clauses.clause_uuid (one-to-many)

Section taxonomy​

A three-level taxonomy of merger-agreement section types. Sections are labeled with these standard_ids via sections.section_standard_id_gold_label and, in exploded form, via latest_sections_search_standard_ids.

taxonomy_l1​

Top level of the section taxonomy — broad categories such as "Definitions and Interpretation" or "Representations and Warranties (Company/Seller)".

Primary key: standard_id

Relationships:

  • taxonomy_l2.parent_id → taxonomy_l1.standard_id (one-to-many)

taxonomy_l2​

Middle level of the section taxonomy.

Primary key: standard_id

Relationships:

  • taxonomy_l2.parent_id → taxonomy_l1.standard_id (many-to-one)
  • taxonomy_l3.parent_id → taxonomy_l2.standard_id (one-to-many)

taxonomy_l3​

Leaf level of the section taxonomy — the most specific section types (e.g. "Termination Events; Outside Date; Extensions").

Primary key: standard_id

Relationships:

  • taxonomy_l3.parent_id → taxonomy_l2.standard_id (many-to-one)
  • latest_sections_search_standard_ids.standard_id → taxonomy_l3.standard_id (one-to-many)

Clause taxonomy (tax module)​

A separate, deeper taxonomy applied at the clause level, organized into modules. The first module, tax, covers tax provisions; its nodes use dotted identifiers (tax.1, tax.1.1, tax.1.1.1). tax_clause_assignments links clauses to these nodes.

tax_clause_taxonomy_l1​

Top level of the tax-module clause taxonomy (e.g. tax.1 "Intended Tax Treatment, Elections, and Purchase Price Allocation").

Primary key: standard_id

Relationships:

  • tax_clause_taxonomy_l2.parent_id → tax_clause_taxonomy_l1.standard_id (one-to-many)

tax_clause_taxonomy_l2​

Middle level of the tax-module clause taxonomy.

Primary key: standard_id

Relationships:

  • tax_clause_taxonomy_l2.parent_id → tax_clause_taxonomy_l1.standard_id (many-to-one)
  • tax_clause_taxonomy_l3.parent_id → tax_clause_taxonomy_l2.standard_id (one-to-many)

tax_clause_taxonomy_l3​

Leaf level of the tax-module clause taxonomy (e.g. tax.1.1.1 "Intended Tax-Free Reorganization").

Primary key: standard_id

Relationships:

  • tax_clause_taxonomy_l3.parent_id → tax_clause_taxonomy_l2.standard_id (many-to-one)
  • tax_clause_assignments.standard_id → tax_clause_taxonomy_l3.standard_id (one-to-many)

tax_clause_assignments​

Assignments of clauses to clause-taxonomy nodes. standard_id may point at an L2 or an L3 node — clauses are labeled at the most specific level the model supports for that branch.

Primary key: clause_uuid, standard_id

Relationships:

  • tax_clause_assignments.clause_uuid → clauses.clause_uuid (many-to-one)
  • tax_clause_assignments.standard_id → tax_clause_taxonomy_l3.standard_id (many-to-one)

Search denormalizations​

Precomputed tables that power section search in the app and API. They duplicate agreement metadata per section so searches can filter without joins; if you are doing your own analysis, prefer joining the source tables directly.

Denormalized search index: one row per section of the latest verified XML of each public agreement, with the deal metadata from agreements copied onto every row so searches can filter without joins. Metadata columns have the same meaning as their agreements counterparts.

Primary key: section_uuid

Relationships:

  • latest_sections_search.section_uuid ↔ sections.section_uuid (one-to-one)
  • latest_sections_search.agreement_uuid → agreements.agreement_uuid (many-to-one)
  • latest_sections_search_standard_ids.section_uuid → latest_sections_search.section_uuid (one-to-many)

latest_sections_search_standard_ids​

Exploded one-row-per-(taxonomy node, section) form of latest_sections_search.section_standard_ids, for indexed taxonomy filtering.

Primary key: standard_id, section_uuid

Relationships:

  • latest_sections_search_standard_ids.section_uuid → latest_sections_search.section_uuid (many-to-one)
  • latest_sections_search_standard_ids.standard_id → taxonomy_l3.standard_id (many-to-one)

Counsel​

Law firms advising each side of each deal, extracted from the agreements and normalized to canonical firm names.

counsel​

Canonical law firms, deduplicated by normalized name.

Primary key: counsel_id

Relationships:

  • agreement_counsel.counsel_id → counsel.counsel_id (one-to-many)

agreement_counsel​

Law firms advising each side of each agreement, in the order they were listed. Join through counsel for canonical firm names.

Primary key: agreement_uuid, side, position

Relationships:

  • agreement_counsel.agreement_uuid → agreements.agreement_uuid (many-to-one)
  • agreement_counsel.counsel_id → counsel.counsel_id (many-to-one)

Reference data​

NAICS industry classification reference tables.

naics_sectors​

NAICS sector reference (two-digit sector codes) with Pandects' groupings.

Primary key: sector_code

Relationships:

  • naics_sub_sectors.sector_code → naics_sectors.sector_code (one-to-many)

naics_sub_sectors​

NAICS subsector reference (three-digit codes).

Primary key: sub_sector_code

Relationships:

  • naics_sub_sectors.sector_code → naics_sectors.sector_code (many-to-one)

Analytics summaries​

Pre-aggregated statistics that power the public data/analytics pages. Everything here is derivable from the core tables; these exist so the app can serve charts without heavy queries.

summary_data​

Pre-aggregated corpus statistics: one row per observed combination of filing year, form type, exhibit type, party types, deal type, attitude, and purpose, with agreement/page/section counts and total deal value.

agreement_overview_summary​

Single-row headline statistics for the data overview page.

Primary key: singleton_key

agreement_index_summary​

One row per public agreement, powering the agreement browse/index pages.

Primary key: agreement_uuid

Relationships:

  • agreement_index_summary.agreement_uuid ↔ agreements.agreement_uuid (one-to-one)

agreement_filter_option_summary​

Distinct filter options offered by the search UI, with the number of agreements each matches.

agreement_metadata_field_coverage_summary​

Extraction coverage per deal-metadata field: how many agreements are eligible for the field and how many have it populated, over all ingested agreements and over fully processed agreements.

Primary key: field_name

agreement_status_summary​

Pipeline progress counts by filing year: how many agreements sit at each processing stage.

Primary key: year, color, current_stage

agreement_deal_type_summary​

Deal counts by filing year and transaction structure.

Primary key: year, deal_type

agreement_target_industry_summary​

Deal count and total transaction value by filing year and target industry.

Primary key: year, industry

agreement_industry_pairing_summary​

Deal count and total transaction value for each (target industry, acquirer industry) pairing.

Primary key: target_industry, acquirer_industry

agreement_ownership_mix_summary​

Deal count and total transaction value by filing year and target ownership bucket.

Primary key: year, target_bucket

agreement_ownership_deal_size_summary​

Transaction-value quartiles by filing year and target ownership bucket.

Primary key: year, target_bucket

agreement_buyer_type_matrix_summary​

Deal count and median transaction value for each target ownership bucket x buyer type cell.

Primary key: target_bucket, buyer_bucket

agreement_counsel_leaderboard_summary​

Counsel league table: deal count and total transaction value per law firm, by side and filing year.

Primary key: side, counsel_key, year