Skip to content

documents

Docket documents

One row per document posted to a docket — proposed rules, final rules, notices, supporting analyses, and public-submission stubs. Joins to dockets on docket_id. Two payload fields are not carried: comment (the inline submission body) and restrictReasonType (why a document is withheld). Read those from the acquisition source, the raw Mirrulations payload or spicy-docs' source-native release, not from this table.

Coverage. Sampled repair with retained prior rows. On 2026-09-21 the fork merged every selected retained document release into the prior table, resolving each document to its most recent source record and keeping newer and unrelated rows; the daily regulations ETL adds to it. It is not a fresh regulations.gov census. The repair covered metadata and references; body-text, extraction-status and PDF-extraction-evidence values remain NULL. It does not include a full comments corpus. Receipts: fork-execution-2026-09-21/publication-documents.json and fork-base-repair-2026-09-21/repair-audit.json. (measured 2026-09-28)

Data quality. The 2026-09-21 fork date check across all 2,001,531 rows found 52,699 posted_date values before 1990, including eight in year 0000; one value was in the future, 2,020 were missing and no other values were uncastable. Source date values remain literal in this base table; downstream calendar filters are separate. Receipt: fork-execution-2026-09-21/document-derivatives/audit.json. In the 2026-09-21 fork consumer audit, 1,854,836 documents resolve to published dockets; 466 preserve unmatched literal links across 137 docket IDs, and 146,229 have NULL links. The 348 selected-source unmatched links and 145,907 selected-source NULL links agree with native inputs; the remaining 118 unmatched and 322 NULL rows are outside that replay. Missing joins are not fabricated or treated as failed repairs. Receipt: fork-execution-2026-09-21/documents-mcp-audit/.

Calendar searches must distinguish source dates from instants. Under the Regulations.gov convention in SpicyDocs' regulations_gov_day, date-only values and bare T00:00:00Z retain their printed day; other offset-bearing timestamps use America/New_York, not their UTC date. For example, 2026-10-05T03:59:59Z is October 4 at 23:59:59 EDT, while 2026-11-24T04:59:59Z is November 23 at 23:59:59 EST. This bare-midnight exception is source-specific, not a rule for arbitrary timestamps.

This guarded DuckDB example selects October 2–16 inclusive in Eastern calendar days (not a 14-day instant interval). It handles the shown ISO forms, preserving the source's date-only convention; year zero, malformed values and offset-free timestamps yield NULL. Inspect those unknowns separately rather than treating exclusion from the filter as no deadline. Do not compare UTC timestamp strings with a date-only upper bound.

WITH calendar AS (
  SELECT document_id,
    CASE
      WHEN left(v, 4) = '0000' THEN NULL
      WHEN regexp_full_match(v, '[0-9]{4}-[0-9]{2}-[0-9]{2}(T00:00:00Z)?')
        THEN TRY_CAST(left(v, 10) AS DATE)
      WHEN regexp_full_match(v, '[0-9]{4}-[0-9]{2}-[0-9]{2}T.+(Z|[+-][0-9]{2}:[0-9]{2})')
        THEN CAST(TRY_CAST(v AS TIMESTAMPTZ) AT TIME ZONE 'America/New_York' AS DATE)
    END AS end_day
  FROM (SELECT document_id, trim(comment_end_date) AS v FROM documents)
)
SELECT document_id, end_day FROM calendar
WHERE end_day >= DATE '2026-10-02' AND end_day < DATE '2026-10-17';

A date field alone does not establish submission purpose or the legally controlling/current deadline. For example, FR 2026-15634's DATES text specifies October 2, 2026 objections/hearing requests under 40 CFR part 178, not ordinary comments. NULL does not establish that no deadline exists. Read the notice's filing instructions and later notices before acting. fr_docket_links offers source docket navigation; comment_periods offers derived date-window candidates, not a complete legal extension history.

Rows are additive. The Mirrulations mirror keeps every capture and never sees a deletion, so a posting Regulations.gov later removes, or moves to another docket, stays here: on 2026-10-03 FNA-2026-0301-0004, the Utah notice (FR 2026-18893) filed to the Idaho docket, answered 404 while this table held it with withdrawn = 'false'; Regulations.gov had re-posted it as FNA-2026-0313-0006, and its four comments appear under both. A daily step lists every docket with a document posted or modified in the past week and reads by id each held document the listing omits: publisher_status is removed where Regulations.gov answers 404 or 410. Older dockets are not checked, so NULL means never checked, not still published. Removed rows stay here; agency_stats, feed_summary, agency_monthly_volume and discovery_signals leave them and their comments out. Filter publisher_status IS DISTINCT FROM 'removed' to do the same.

  • Parquet file: documents.parquet
  • MCP query_sql support: Configured; requires an available artifact.
  • Publication status: Not established by this schema page or its measurement date.
  • Row count: Not stated here; the MCP describe_table reply gives the live count under publication.
  • Primary / dedup key: document_id
Column Type Description
document_id 🔑 VARCHAR Unique document identifier. Primary key / dedup key.
docket_id VARCHAR Parent docket this document belongs to. Foreign key to dockets.docket_id.
agency_code VARCHAR Posting agency's short code.
title VARCHAR Document title.
document_type VARCHAR Document category (e.g. Rule, Proposed Rule, Notice, Supporting & Related Material).
posted_date VARCHAR Date the document was posted publicly (ISO 8601 string).
modify_date VARCHAR Timestamp the document was last modified (ISO 8601 string).
comment_start_date VARCHAR Literal Regulations.gov commentStartDate. See data_quality for the source-specific calendar conversion; this field does not establish submission purpose. NULL means no value was retained.
comment_end_date VARCHAR Literal Regulations.gov commentEndDate as Regulations.gov states it now, which can reflect a later extension rather than the notice's own date: when a later notice extends the period, Regulations.gov can rewrite the original notice's date too (FS-2025-0001-223869, FR 2026-16965, reads 2026-10-07T03:59:59Z, the extension FR 2026-18648 set, while the Register's comments_close_on for 2026-16965 is 2026-09-21). For the date a notice printed, read federal_register.comments_close_on by fr_doc_num. Convert offset-bearing instants to America/New_York for calendar searches; date-only values and the source's bare T00:00:00Z convention keep their printed day (see data_quality). Neither purpose nor the controlling/current deadline follows from this field; inspect filing instructions and later notices. NULL does not mean no deadline.
file_url VARCHAR URL of the document's primary downloadable rendition. Retained for backward compatibility; see attachments_json for the full list. Often null.
attachments_json VARCHAR JSON array of the main document's fileFormats renditions: [{url, format, size}]. This compatibility field does not contain separately listed attachment resources. Null when no main renditions were retained.
attachment_records_json VARCHAR Literal records from an explicitly read document attachment relationship, preserving attachment IDs, restrictions and alternative file formats. Null means the relationship was not read; an empty array means a validated complete read returned no attachments. A main document response alone cannot establish attachment absence.
fr_doc_num VARCHAR Federal Register document number, when the document was published in the FR. Often null.
withdrawn VARCHAR Whether regulations.gov flags the posting withdrawn, as the string "true"/"false". Often null. A withdrawn posting is still held here; the rulemaking tables read it as no evidence (see proceedings).
reason_withdrawn VARCHAR Agency-supplied reason for withdrawal, when withdrawn: usually that it was posted to the wrong docket, a duplicate, moved or replaced. Often null.
additional_rins VARCHAR JSON array of additional Regulation Identifier Numbers beyond the docket's primary RIN. Often null.
text_content VARCHAR Plain text extracted from the document's PDF attachment(s) by the PDF text-extraction step. Null until that step has run; see text_extraction_status.
text_extraction_status VARCHAR Aggregate PDF outcome: ok means at least one PDF supplied text, even if another failed; otherwise error, encrypted, or empty (no extractable text) in that order. Null before an attempt. See pdf_extraction_results_json for each file.
pdf_extraction_results_json VARCHAR Ordered JSON array from the latest PDF attempt: {url, source_sha256, status, page_count, error} per distinct selected URL. SHA-256 identifies observed bytes; no-bytes fetch failures have null digest/page count and a generic error. Null when no PDF attempt is recorded. Describes that attempt, not the provenance of older text retained after a failed overwrite.
publisher_status VARCHAR What Regulations.gov last said of the document when the daily reconcile step checked its docket: listed (the docket's document listing names it, or, where the listing does not, its own record still answers) or removed (the listing omits it and its own record answers 404 or 410). NULL where the step has never checked it: it checks only dockets with a document posted or modified in the past week, so NULL does not mean the publisher still serves it. A removed document stays in this table (see data_quality).
removed_observed_at VARCHAR When the reconcile step first found the document removed, as a UTC ISO 8601 instant; NULL unless publisher_status is removed.