Querying civic records with SQL and DuckDB

A civic-data question does not always need an agent. Sometimes the useful result is a table: which records are in this collection, what amounts do they state, and which source supports each row?

DuckDB provides a route from local JSON files to SQL queries and an exported report. This walkthrough supplies a small source extract and an import script so the data model remains visible from the first query.

The DuckDB script was checked against the official documentation but was not executed in DuckDB in this environment. The fixture and its arithmetic were executed independently with the included Python and SQLite code. That distinction matters when reproducing the tutorial: the expected values are checked, while the DuckDB runtime still needs its own verification.

Choose a question the collection can answer

The teaching dataset contains three deliberately selected Fort Worth authorizations dated June 24, 2025. The question is: what stated annual amounts appear in these three actions, and what is their sum?

The records are M&C 25-0563, M&C 25-0573, and M&C 25-0574. The extract retains the official source link in every row.

It is manually transcribed research data, not an AICD API response. It also does not include every action at the meeting. The query therefore cannot answer total city spending or the value of all contracts awarded that day.

A small, well-described collection is sufficient for learning how to build an inspectable analytical report.

Keep the amount at the authorization level

The file stores annual_ceiling_cents as an integer. Two of the actions describe combined annual amounts; those belong to the action, not to a repeated row for every named vendor.

Keep the vendor list in the source extract, but omit it from the first analytical table. That prevents the initial calculation from accidentally expanding one authorization into several rows.

The local record identifiers are application identifiers. The publisher's file references and source URLs remain alongside them. A reviewer should be able to move from the calculated output back to the actual record without guessing what the local identifier means.

Load the fields explicitly

Save authorizations.json and duckdb_analysis.sql from the source kit in one directory. With the DuckDB CLI installed, run the script from that directory:

duckdb < duckdb_analysis.sql

The import statement is:

CREATE OR REPLACE TABLE authorizations AS
SELECT * FROM read_json('authorizations.json', columns = {
  record_id: 'VARCHAR',
  agency: 'VARCHAR',
  action_date: 'DATE',
  category: 'VARCHAR',
  annual_ceiling_cents: 'BIGINT',
  source_url: 'VARCHAR'
});

DuckDB's JSON-loading documentation supports an explicit column mapping and reading selected fields from a JSON input. Here the mapping states which fields the analysis uses rather than making the report depend on automatic inference of every field.

The script creates or replaces a local table and writes a local report file. Use a working directory where replacing that output is acceptable.

Inspect the rows before calculating

Run a detail query and open the source behind each of the three records:

SELECT record_id, action_date, category,
       annual_ceiling_cents, source_url
FROM authorizations
ORDER BY record_id;

For the supplied extract, the integer amounts are 155,000,000, 12,000,000, and 146,500,000 cents. The measure is the stated annual authorization amount, not a payment or a multi-year contract value.

Add simple completeness checks before adapting the script to a larger collection. Look for missing IDs, missing source links, and duplicate identities:

SELECT record_id, COUNT(*) AS occurrences
FROM authorizations
GROUP BY record_id
HAVING COUNT(*) > 1;

SELECT COUNT(*) AS incomplete_rows
FROM authorizations
WHERE record_id IS NULL
   OR source_url IS NULL
   OR annual_ceiling_cents IS NULL;

An empty duplicate query does not prove that two different IDs cannot refer to the same source event. It checks one defined condition. Broader source matching belongs in the importer and should have its own reviewed rules.

Calculate a result with a precise label

The initial aggregate is deliberately small:

SELECT COUNT(*) AS selected_actions,
       SUM(annual_ceiling_cents) AS selected_annual_ceiling_cents
FROM authorizations;

The supplied three-row extract should produce three actions and 313,500,000 cents, or $3,135,000. That arithmetic was checked with SQLite in the companion program. It is an expected DuckDB result, not a recorded DuckDB execution in this environment.

Label the output "sum of stated annual amounts in the three selected actions." Do not shorten it to spending. Nor should you multiply each amount by the maximum number of optional renewals and call the result an executed contract value.

The useful discipline is to carry the measure and source scope with the number, not leave them in a separate note the reader may miss.

Export the evidence with the report

The script writes a detail file as well as the aggregate:

COPY (
  SELECT record_id, action_date, category,
         annual_ceiling_cents, source_url
  FROM authorizations
  ORDER BY record_id
)
TO 'authorization_report.csv' (FORMAT CSV, HEADER true);

Retaining identifiers and source links in the export makes it useful beyond the SQL session. A researcher can inspect a row, another script can reproduce the total, and a reviewer can identify which records were included.

The package already contains a detail CSV generated by the Python/SQLite path. Running the DuckDB script will produce its own file at the same relative path. Keep the two execution records separate when verifying that the results agree.

Move from a teaching extract to an API collection

For a real integration, use the AICD API reference to retrieve the supported records your question needs. Save the source response and adapt it into a documented analytical schema rather than assuming that the teaching JSON matches an API resource.

Record the selected jurisdictions, date interpretation, resource types, pagination or export boundary, and known missing sources. A few recent public sample hits cannot support a comprehensive citywide aggregate.

Keep extraction separate from analysis. The importer establishes record identity and scope; the SQL answers a defined question about that imported collection. That makes it easier to diagnose whether a surprising result came from the data, the join, or the calculation.

Use the smallest analytical tool that finishes the job

A source-linked detail report and a transparent aggregate may be all the first product needs. An analyst can build a useful internal view without introducing a hosted database or an AI-generated narrative.

The next step is to run the script in the intended DuckDB environment, record the version and results, and compare them with the checked fixture output. Then expand the collection only when the source scope supports the next question.

Source files

The Notion source kit for this tutorial includes a historical JSON extract, DuckDB SQL, and independently executed comparison code. The SQL and field mappings above are the publishable core; reconstruct the teaching extract from the three linked Fort Worth authorizations and the documented column types.