`comments`

Public comments

One row per public comment — the largest table (tens of millions of rows). On R2 it is also published as Hive-partitioned files under comments/agency/agency_code=.../part-0.parquet, one file per agency, rebuilt daily — this is what spicy-regs-ui reads for scoped queries. (An older comments/agency_code=.../docket_id=.../year=.../month=... tree was abandoned when comments moved onto the Iceberg catalog and carried a reduced set of columns; prefer the per-agency tree.) Joins to dockets on docket_id when the source supplies that relationship. Comments with a null docket remain in the table and agency totals.

Coverage. True range, floored and open-ended. Posted dates are stated from 1990-01-01 rather than from the raw minimum of year 0000, on the same publisher defect as documents; the mirror is rebuilt from the catalog, so the latest posted date is a query (SELECT max(posted_date)), not a stated bound. See the data-quality note. (measured 2026-10-03)

Data quality. Rows with a posted_date before 1990 carry the same publisher defect as documents (WHERE posted_date < '1990' counts them); filter on the date when recency matters. A comment on a posting Regulations.gov removed stays here, and where the publisher moved the posting it re-posted the comments under new ids, so one comment can appear twice: on 2026-10-03 FNA-2026-0301-0006 to -0009, on the removed FNA-2026-0301-0004, are FNA-2026-0313-0002 to -0005, body for body. agency_stats and feed_summary leave them out; to do the same, keep rows WHERE comment_on_document_id IS NULL OR comment_on_document_id NOT IN (SELECT document_id FROM documents WHERE publisher_status = 'removed').

Parquet file: comments.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: comment_id

Column	Type	Description
`comment_id` 🔑	`VARCHAR`	Unique comment identifier. Primary key / dedup key.
`docket_id`	`VARCHAR`	Source-supplied docket identifier for joining to `dockets.docket_id`. Null when the source does not identify a docket; mirror directory names and comment ID prefixes do not fill this field.
`comment_on_document_id`	`VARCHAR`	Literal Regulations.gov commentOnDocumentId: the named parent document, independently nullable from docket_id. No identifier-prefix inference. The comment re-read filled it: on receipt sha256:77a08369… (2026-10-03) it is set on 26,388,456 of 26,415,400 rows, on at least 98.97% of each of the 12 largest agencies' rows, and below 95% only at MSHA (1,892 of 1,993) and TRAIN (1 of 114). NULL means the record states none or the row was not read; `comment_reference_values_json` tells the two apart. NULL does not mean the comment names no document.
`comment_on_object_id`	`VARCHAR`	Literal commentOn in the native object-ID namespace; not interchangeable with a public document key.
`original_document_id`	`VARCHAR`	Literal originalDocumentId, retained as an unresolved legacy source reference.
`comment_reference_values_json`	`VARCHAR`	JSON map containing only present source parent-reference fields, preserving null and empty values. {} means the source was read and fields were absent; SQL NULL means legacy or unread.
`agency_code`	`VARCHAR`	Receiving agency's short code.
`first_name`	`VARCHAR`	Commenter's first name, where the row's read carried it. NULL where the publisher's record states none or where the row's read did not carry the field; `count(first_name)` by `agency_code` is the measure.
`last_name`	`VARCHAR`	Commenter's last name, where the row's read carried it. NULL where the publisher's record states none or where the row's read did not carry the field; `count(last_name)` by `agency_code` is the measure.
`organization`	`VARCHAR`	The organization the commenter stated, where the row's read carried it. NULL where the publisher's record states none (FDA and EPA state none; EPA classifies submitters in `subtype` instead) and where the row's read did not carry the field. A campaign record carries it where the publisher states it: on receipt sha256:77a08369… (2026-10-03) FWS states it on 340 of its 408 `Mass Mail Campaign` and 10 of its 13 `Late Mass Mail Campaign` records, EPA on none of 7,842. `count(organization)` by `agency_code` is the measure.
`category`	`VARCHAR`	Submitter category as regulations.gov states it, where the row's read carried it (FDA's records state one, such as `Individual Consumer` or `Private Industry - C0003`). NULL where the publisher's record states none (EPA states none: it classifies submitters in `subtype` and counts campaigns in `duplicate_comments`) or where the row's read did not carry the field; NULL across a whole agency does not mean the agency does not classify. `count(category)` by `agency_code` is the measure.
`title`	`VARCHAR`	Comment title / subject line, as the publisher serves it; a few carry HTML entities (`'`, `&`).
`comment`	`VARCHAR`	The comment body as regulations.gov serves it: an HTML fragment, not plain text. Line breaks are `<br/>` tags and many characters are HTML entities (`'`, `"`, `&`, `’`) on many rows of every agency, so a pattern with a plain apostrophe or quotation mark misses them: replace the entities before grouping or quoting. Filled for nearly every comment. Text of attached files is in `text_content`, which is filled only where an attachment was extracted. The largest field in the dataset.
`document_type`	`VARCHAR`	Document category for the comment record, typically `Public Submission`.
`posted_date`	`VARCHAR`	When the comment was posted, as the publisher stamps it (ISO 8601 string): a date at Eastern midnight written in UTC (`T04:00:00Z` or `T05:00:00Z`) on most records, a bare date label at `T00:00:00Z` on others. Read the date part as the publisher's date; converting a `T00:00:00Z` label to Eastern moves it a day early.
`modify_date`	`VARCHAR`	Timestamp the comment was last modified (ISO 8601 string).
`receive_date`	`VARCHAR`	The agency's stated receipt date, stamped as `posted_date` is (ISO 8601 string). It can follow `posted_date` (late or mailed submissions), so compare on the date part.
`attachments_json`	`VARCHAR`	JSON array of the comment's downloadable attachments: `[{title, formats:[{url, format, size}]}]`, from the object's `included` attachments, keeping only formats with a file URL. NULL, never `[]`, when it keeps none, so NULL does not mean no attachments: an attachment regulations.gov lists with no downloadable file (a `restrictReasonType` such as `Copyrighted`) is left out, and a comment whose attachments are all such reads NULL. The source records of EPA-HQ-OW-2022-0114 list 58 such attachments on 16 records, all Copyrighted; -1540, whose only attachment is one, reads NULL. Rows ingested before the extract gained this column (2026-03-15) kept NULL until the comment re-read (cause proven in comments-full-reread-2026-09-28/attachments-root-cause/), which on receipt sha256:77a08369… (2026-10-03) has written 26,414,926 of 26,415,400 rows: a non-NULL `comment_reference_values_json`, which the same extract always writes, marks a row it wrote, though the re-read leaves a column NULL where two copies of the record's version disagree.
`text_content`	`VARCHAR`	Plain text of the comment's attachment(s). Filled inline during the ETL from Mirrulations' own extraction (the bucket's `derived-data` prefix): one extraction tool per comment, its attachments in number order, each stripped and separated by a blank line; when that tool's objects are all blank there is no derived text. Attachments Mirrulations has not extracted are backfilled by the on-demand PDF text-extraction step. Null when neither has produced text; see `text_extraction_status`.
`text_extraction_status`	`VARCHAR`	Where `text_content` came from, or why there is none. `derived` means Mirrulations' own extraction supplied it (see `pdf_extraction_results_json`); `ok` means Spicy Regs extracted text from at least one PDF, even if another failed; otherwise PDF outcomes prefer `error`, `encrypted`, then `empty`. Derived-data fills written before `derived` existed read `ok` until repaired. Null before an attempt or derived-text fill.
`pdf_extraction_results_json`	`VARCHAR`	Provenance of `text_content`. For `derived` rows, a JSON object `{comment_id, tool, available_tools, attachments:[{attachment, tool, key, size, etag, sha256}], only_in_other_tools}`: the primary tool (the first of `pypdf`, `pdfminer` with an object for the comment), every tool with an object for it, each attachment's tool, S3 key, listed size and ETag and the SHA-256 of the bytes read (null when the text was kept without re-reading them), and the attachment numbers taken from a tool other than the primary because the primary has no object for them (decision 41); those are in the text. A primary object that exists is kept even when blank. About 125 bytes plus 330 per attachment (31 KB at 92 attachments, the published maximum). Otherwise an ordered JSON array from the latest PDF attempt: `{url, source_sha256, status, page_count, error}` per distinct selected URL; SHA-256 identifies observed bytes, and no-bytes fetch failures have null digest/page count and a generic error. Null when neither is recorded.
`subtype`	`VARCHAR`	The agency's own class for the submission (regulations.gov `subtype`), spelled as stated. It is agency-specific. EPA classifies the submitter: `Public Comment` (an individual), `Company/Organization Comment`, `Government Local`, `Government State`, `Government Federal`, `Government Tribal`, `Member of Congress`, `Mass Mail Campaign`, `Late Comment`. Most other agencies that state it put one generic label on every record (`Comment(s)`, `Public Comment`, FDA's `Electronic Regulation from Form`), and many state none. EPA's submitter classes also appear at other agencies in small numbers, and `Mass Mail Campaign` (and `Late Mass Mail Campaign`) at several, FWS among them; which agencies state which labels is a query (`SELECT agency_code, subtype, count(*) … GROUP BY ALL`), not a fixed list. Split organizations from individuals only within an agency that classifies, and not by `organization`: all 1,629 records of EPA-HQ-OW-2022-0114 leave `organization`, `category` and `first_name` NULL, 219 of them `Company/Organization Comment`. NULL where the record states none or the row predates reading this field.
`duplicate_comments`	`INTEGER`	How many received submissions the agency says this posted record stands for (regulations.gov `duplicateComments`), as stated. Agencies that count (EPA and FWS among them) state 1 for a single comment and the campaign's size on a `Mass Mail Campaign` record (15,851 on EPA-HQ-OW-2022-0114-1811, National Wildlife Federation Action Fund); agencies that do not count state 0 on every record. Rows are posted records. To estimate submissions, sum `GREATEST(duplicate_comments, 1)`, each posted record standing for at least itself, and first count the NULL rows: GREATEST reads NULL as 1. The sum counts the submissions the agency says its posted records stand for, each record at least itself: its posted accounting, not its official total. On the comments export of receipt sha256:77a08369… (2026-10-03), EPA-HQ-OW-2022-0114's 1,629 records sum to 53,692, 52,086 of them on its 23 `Mass Mail Campaign` records, while EPA's response to comments reports about 122,200 received. The 2026-09-28 sample found it stated on every record: 0 on 5,281, 1 on 660, more than 1 on 4. NULL where the row predates reading this field; 0 is a stated zero.
`comment_text`	`VARCHAR`	`comment` read as plain text, derived from it each time the mirror is exported and never stored in the catalog. The publisher serves `comment` as an HTML fragment (`I'm`, `<br/>`); this column decodes each character reference once, makes `<br>` and the end of a block element a newline and a no-break space a space, drops every other tag and trims the ends. Search and quote this column; `comment` keeps the publisher's bytes. The projection loses link targets, list bullets and table layout. One decode only: a body the publisher escaped twice keeps one level (`&#39;` reads `'`), and markup a commenter typed as text (`<br>`) reads as that text. NULL exactly where `comment` is NULL.