{"success":true,"data":{"author":"City of Boston (Department of Innovation and Technology)","bucketAccessType":"public","bucketListable":false,"bucketName":"data.boston.gov","category":"Urban & Civic","cloudProvider":"api","createdAt":"2026-08-15T00:00:00.000Z","description":"Line-item municipal spending for the City of Boston from July 2011 (fiscal year 2012) to the present: 1,942,660 payment lines across 16 DataStore-loaded resources, one per fiscal year with the current year split into monthly increments. One row is one distribution line of one voucher. Each carries the voucher number with its voucher-line and distribution-line index, the entry date decomposed into month, month number, fiscal month, fiscal year and calendar year, the vendor name, the account code and description (for example 54906 'Current Software Licen/Maint'), the paying department code and name, a six-digit organisational unit name, and the monetary amount. Paired with the Employee Earnings Report, which covers the payroll the Checkbook deliberately excludes, this is a near-complete picture of where the city's money goes: vendor concentration and dependency, spending by function over fifteen years, procurement seasonality against the June fiscal year end, and the growth of specific cost categories such as software licensing or emergency shelter. The data is published under Mayor Walsh's 2014 open data executive order, with personal information withheld and independent city agencies excluded. Three things are documented in the schema card. Amounts are text, including negatives for credits and reversals, so they need a cast before aggregation and a reversal is a real row that must not be dropped as an error. Row counts per fiscal year are not comparable as a proxy for activity: the FY2024 resource holds 8,750 lines against 118,859 for FY2023 and 235,796 for FY2025, which reflects how that file was published rather than a collapse in city spending, so any year-over-year total must be computed from the amount column and cross-checked against the published budget. And the current fiscal year arrives as dated monthly snapshot files (checkbook_explorer_20260701, checkbook_explorer_20260801) whose contents overlap, so taking every resource in the package double counts the current year. Published by the Department of Innovation and Technology under PDDL 1.0.","displayTitle":"Boston Checkbook Explorer city expenditures, FY2012 to present","dockerImageUrl":"531775725781.dkr.ecr.us-east-1.amazonaws.com/scigantic-notebook-minimal:latest","id":"446b29e0-5c9f-5254-b137-6c1c18992947","license":"Open Data Commons Public Domain Dedication and License (PDDL) 1.0","referenceUrl":"https://data.boston.gov/dataset/checkbook-explorer","region":"us-east-1","schemaCard":{"columns":[{"name":"_id","type":"int"},{"name":"Voucher","type":"text"},{"type":"text","name":"Voucher Line"},{"name":"Distribution Line","type":"text"},{"type":"text","name":"Entered"},{"name":"Month(Number)","type":"text"},{"name":"Month","type":"text"},{"type":"text","name":"Fiscal Month"},{"name":"Fiscal Year","type":"text"},{"type":"text","name":"Year"},{"name":"Vendor Name","type":"text"},{"name":"Account","type":"text"},{"name":"Account Descr","type":"text"},{"name":"Dept","type":"text"},{"type":"text","name":"Dept Name"},{"type":"text","name":"6 Digit Org Name"},{"name":"Monetary Amount","type":"text"}],"format":"v2-hybrid","rowCount":1942660,"sampleObjects":["7afd05bc-34ec-4a1c-b470-bf43ea584f81  checkbook_explorer_20260801.csv","d22fdd5c-7e4c-41b7-a3eb-dfc57a87b245  checkbook_explorer_20260701.csv","84dfc1af-28bd-4f17-804a-9cc0c09a237e  checkbook_explorer_fy25_updated.csv","0b7c9c5f-d1c2-46e7-b738-6ab37a110eef  checkbook_explorerfy24.csv","5ce2ff98-3313-40d2-88bd-47eae9e5a654  checkbook_explorerfy23.csv","0a261d4e-3eec-4bac-bf72-b9a7aa77b033  checkbook_explorerfy22.csv","32897eeb-d9ca-494f-93b1-991c50bcd6a6  checkbook_explorerfy21.csv","c093700f-d78a-49de-a8fe-508ba834ff6f  checkbook-explorer-fy20.csv","38227f56-46ed-47fe-9e1c-5d2fce52908d  checkbook-explorer-fy19.csv","5d8e373f-29a0-472c-b39b-9aa249e86fd5  checkbook-explorer-fy18.csv","01a5c35c-19e3-419e-a8b5-cb623525b96d  checkbook-explorer-fy17.csv","ae5a15cc-8bd3-455d-8cbb-9221e07c1426  checkbook-explorer-fy16.csv","5714ab9f-52d3-4c41-b2a6-2700b41438fc  checkbook-explorer-fy15.csv","69eab395-07d3-41b8-a021-a0d314bd8046  checkbook-explorer-fy14.csv","c2bc5615-9478-4a9b-b71c-f63f6364e409  checkbook-explorer-fy13.csv","fd5c56a7-224f-41c4-a011-969b8aee457d  checkbook-explorer-fy12.csv"],"accessHints":[{"language":"python","snippet":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n"},{"language":"python","snippet":"# Bulk CSV download. pd.read_csv(url) on this portal raises 403: the CKAN link\n# 302s to a presigned S3 URL that the redirect fails on. Resolve it by hand and\n# drop the explicit :443, which is the single change that makes it a 200.\nclass _NoRedirect(urllib.request.HTTPRedirectHandler):\n    def redirect_request(self, *a, **k):\n        return None\n\ndef boston_csv(url, **read_csv_kwargs):\n    try:\n        urllib.request.build_opener(_NoRedirect).open(url, timeout=120)\n        signed = url\n    except urllib.error.HTTPError as e:\n        if e.code not in (301, 302, 303, 307, 308):\n            raise\n        signed = e.headers[\"Location\"].replace(\"s3.amazonaws.com:443\", \"s3.amazonaws.com\")\n    with urllib.request.urlopen(signed, timeout=900) as r:\n        return pd.read_csv(io.BytesIO(r.read()), **read_csv_kwargs)\n"},{"language":"python","snippet":"# Resolve a resource id by package name rather than hardcoding it, and page\n# past the 32,000-row cap. Verified against a 282,836-row resource.\ndef resources(package_name):\n    pkg = ckan(\"package_show\", id=package_name)\n    return [\n        {\"id\": r[\"id\"], \"name\": r.get(\"name\"), \"file\": r[\"url\"].rsplit(\"/\", 1)[-1],\n         \"format\": r.get(\"format\"), \"live\": bool(r.get(\"datastore_active\"))}\n        for r in pkg[\"resources\"]\n    ]\n\ndef read_all(resource_id, page=32000, **filters):\n    \"\"\"Every row of a DataStore resource, in _id order.\"\"\"\n    out, offset = [], 0\n    while True:\n        res = ckan(\"datastore_search\", resource_id=resource_id,\n                   limit=page, offset=offset, **filters)\n        rows = res[\"records\"]\n        out.extend(rows)\n        if len(rows) < page:\n            return pd.DataFrame(out)\n        offset += page\n"}],"layout":{"pattern":"One row is one distribution line of one voucher: the finest grain at which the city publishes a payment. 1,942,660 rows across 16 DataStore resources covering FY2012 (July 2011) to the present.\nCOLUMNS: Voucher, \"Voucher Line\" and \"Distribution Line\" together identify the payment line; Entered is the entry date as M/D/YYYY text; Month, \"Month(Number)\", \"Fiscal Month\", \"Fiscal Year\" and Year are pre-derived date parts; \"Vendor Name\" is the payee; Account and \"Account Descr\" are the expense classification (54906 \"Current Software Licen/Maint\"); Dept and \"Dept Name\" are the paying department (231100 \"Emergency Preparedness\"); \"6 Digit Org Name\" is the organisational unit (\"Homeland Security\"); \"Monetary Amount\" is the amount. Note that most column names contain SPACES and one contains parentheses, so every reference in SQL must be double-quoted exactly as written.\nBOSTON'S FISCAL YEAR RUNS JULY TO JUNE. \"Fiscal Year\" 2025 means July 2024 to June 2025, and \"Fiscal Month\" 1 is July. Year and Month are the calendar equivalents, which is why a single row can read Fiscal Year 2025, Year 2024, Month July.\nWHAT IS DELIBERATELY ABSENT: payroll (published separately as the Employee Earnings Report), personal information withheld under Mayor Walsh's 2014 open data executive order, and independent city agencies. This is the operating checkbook of city government, not of every entity with \"Boston\" in its name.\nPER-FISCAL-YEAR ROW COUNTS: FY2012 168,180 | FY2013 134,478 | FY2014 147,111 | FY2015 129,713 | FY2016 133,543 | FY2017 149,969 | FY2018 154,617 | FY2019 139,037 | FY2020 112,315 | FY2021 89,090 | FY2022 113,491 | FY2023 118,859 | FY2024 8,750 | FY2025 235,796 | plus two current monthly snapshots (101,465 and 6,246).\nRESOURCES:\n  Checkbook Explorer (data.boston.gov/dataset/checkbook-explorer): 16 DataStore resources of 16 total\n    7afd05bc-34ec-4a1c-b470-bf43ea584f81       6246 rows  checkbook_explorer_20260801.csv\n    d22fdd5c-7e4c-41b7-a3eb-dfc57a87b245     101465 rows  checkbook_explorer_20260701.csv\n    84dfc1af-28bd-4f17-804a-9cc0c09a237e     235796 rows  checkbook_explorer_fy25_updated.csv\n    0b7c9c5f-d1c2-46e7-b738-6ab37a110eef       8750 rows  checkbook_explorerfy24.csv\n    5ce2ff98-3313-40d2-88bd-47eae9e5a654     118859 rows  checkbook_explorerfy23.csv\n    0a261d4e-3eec-4bac-bf72-b9a7aa77b033     113491 rows  checkbook_explorerfy22.csv\n    32897eeb-d9ca-494f-93b1-991c50bcd6a6      89090 rows  checkbook_explorerfy21.csv\n    c093700f-d78a-49de-a8fe-508ba834ff6f     112315 rows  checkbook-explorer-fy20.csv\n    38227f56-46ed-47fe-9e1c-5d2fce52908d     139037 rows  checkbook-explorer-fy19.csv\n    5d8e373f-29a0-472c-b39b-9aa249e86fd5     154617 rows  checkbook-explorer-fy18.csv\n    01a5c35c-19e3-419e-a8b5-cb623525b96d     149969 rows  checkbook-explorer-fy17.csv\n    ae5a15cc-8bd3-455d-8cbb-9221e07c1426     133543 rows  checkbook-explorer-fy16.csv\n    5714ab9f-52d3-4c41-b2a6-2700b41438fc     129713 rows  checkbook-explorer-fy15.csv\n    69eab395-07d3-41b8-a021-a0d314bd8046     147111 rows  checkbook-explorer-fy14.csv\n    c2bc5615-9478-4a9b-b71c-f63f6364e409     134478 rows  checkbook-explorer-fy13.csv\n    fd5c56a7-224f-41c4-a011-969b8aee457d     168180 rows  checkbook-explorer-fy12.csv","example":"Largest vendors in FY2025 by total paid:\n  SELECT \"Vendor Name\", count(*) lines, sum(\"Monetary Amount\"::numeric) AS paid\n  FROM \"84dfc1af-28bd-4f17-804a-9cc0c09a237e\"\n  GROUP BY 1 ORDER BY paid DESC LIMIT 20"},"generatedAt":"2026-08-16T17:39:25.112Z","starterCell":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n\nFY2025 = \"84dfc1af-28bd-4f17-804a-9cc0c09a237e\"   # 235,796 payment lines\n\n# Where FY2025 money went, by department and expense account.\n# Every column name has a space in it, so quote each one exactly.\ndf = sql(f'''\n    SELECT \"Dept Name\", \"Account Descr\",\n           count(*) AS lines,\n           sum(\"Monetary Amount\"::numeric) AS amount\n    FROM \"{FY2025}\"\n    GROUP BY 1, 2\n    ORDER BY amount DESC\n    LIMIT 25\n''')\nprint(df.to_string(index=False, float_format=lambda v: f\"{v:,.0f}\"))\n\n# Sanity check that the negatives are present and material.\nneg = sql(f'SELECT count(*) n, sum(\"Monetary Amount\"::numeric) amt '\n          f'FROM \"{FY2025}\" WHERE \"Monetary Amount\"::numeric < 0')\nprint(\"\\ncredit/reversal lines:\", neg.to_dict(\"records\"))\n","sampleRows":[{"Account":"54906","Account Descr":"Current Software Licen/Maint","Vendor Name":"Intuitive Control Systems, LLC","__resourceId":"84dfc1af-28bd-4f17-804a-9cc0c09a237e","6 Digit Org Name":"Homeland Security","Month(Number)":"7","Fiscal Month":"1","Dept":"231100","Monetary Amount":"12500","__schema":"FY2025","Distribution Line":"1","Dept Name":"Emergency Preparedness","Month":"July","Year":"2024","_id":1,"Fiscal Year":"2025","Voucher":"3460593","Entered":"7/2/2024","Voucher Line":"1"}],"totalObjects":16,"caveats":["DO NOT look for a FUSE mount. This archive has cloudProvider \"api\": nothing is mounted, ~/archive and /mnt/archive do not exist, and there are no files on disk to walk. The generic archive preamble you may have been given says otherwise; it is wrong for this row. All data is read over HTTPS from the CKAN API at https://data.boston.gov/api/3/action. The notebook has outbound internet; no credentials, tokens or headers are required for any endpoint below.","DO NOT trust a result set of exactly 32,000 rows. Both datastore_search and datastore_search_sql cap a single response at 32,000 records no matter what LIMIT you write, so a query over a million-row resource returns a full-looking frame that is silently a fragment. datastore_search_sql sets records_truncated: true in the result when it clips; check it on every call. The fixes: aggregate server-side (GROUP BY in SQL returns few enough rows to fit), or page with ORDER BY _id plus OFFSET, which is verified to work past 280,000.","DO NOT do arithmetic or date comparison on a column without casting it. Almost every DataStore column on this portal is typed text regardless of what it holds, so avg(\"latitude\") returns HTTP 409 Conflict rather than a number, and min/max on a date column sorts lexically and returns a confident wrong answer. Cast explicitly in SQL: \"latitude\"::float, \"open_dt\"::timestamp, date_part('year', \"open_dt\"::timestamp). Where a column IS genuinely typed numeric or timestamp it is marked as such in the columns list below, and those are safe to use directly.","SQL AGGREGATES COME BACK AS STRINGS, so arithmetic on the resulting DataFrame silently fails or, worse, concatenates. PostgreSQL `numeric` results are serialized as JSON strings by the CKAN API (sum(\"OVERTIME\") returns '153220443.25', not 153220443.25) while `int`/`bigint` and `double precision` come back as JSON numbers. So count(*) is an int, avg() and sum() over a numeric column are strings, and dividing one such column by another raises \"unsupported operand type(s) for /: 'str' and 'str'\". Run pd.to_numeric on every aggregate column before doing arithmetic on it. Do NOT blanket-coerce every column: ward \"06\", precinct \"0104\" and ZIP \"02128\" are zero-padded identifiers that must stay strings.","A 403 FROM datastore_search_sql MEANS THE SQL TEXT TRIPPED AN EDGE FILTER, NOT THAT YOUR QUERY IS WRONG. Some perfectly valid PostgreSQL is rejected with HTTP 403 Forbidden before it ever reaches the database, so there is no SQL error message to read and retrying the identical query never helps. Verified blocked, deterministically, 5 of 5 attempts each (2026-08-15): agg(...) FILTER (WHERE ...) · extract(<part> FROM ...) · substring(x FROM a FOR b) · trim(BOTH ... FROM ...) · NULLIF(...) · a literal constant comparison such as 1=2. Verified working substitutes for each: sum(CASE WHEN cond THEN 1 ELSE 0 END) for FILTER · date_part('year', ts) and date_part('epoch', ts_a - ts_b) for extract · left(col, n) for substring · CASE WHEN d = 0 THEN NULL ELSE d END for NULLIF. Subqueries, CTEs, LEFT JOIN, IN (...), HAVING, window functions, percentile_cont WITHIN GROUP, the regex operator ~, and casts are all fine. When a query 403s, rewrite it; do not retry it.","DO NOT call pd.read_csv() on a data.boston.gov download URL. It raises HTTP 403. The CKAN download link 302-redirects to a presigned S3 URL on the bucket og-production-open-data-bostonma-892364687672, and the request fails when it is followed from urllib's redirect path, which is what pandas uses. Verified 2026-08-15: pd.read_csv(url) -> 403, while requests.get(url) on the identical URL -> 200. Two fixes, both verified: read through requests (pd.read_csv(io.BytesIO(requests.get(url).content))), or resolve the redirect yourself and strip the explicit \":443\" from the returned Location before fetching it. The bucket itself is private (anonymous ListObjectsV2 and GetObject both return AccessDenied), so there is no S3 path around this: the presigned link is the only way in. See accessHints for a copy-paste helper. None of this applies to the /api/3/action endpoints, which are plain JSON over HTTPS and work with bare urllib.","Resource ids, not names, are the addressing key, and the names are unreliable: CKAN's resource `name` field is null on many Boston resources, and several packages publish generated filenames (tmp4myyj_u8.csv) that carry no year. The ids listed in this card were captured on 2026-08-15. The package NAME is the stable handle, so resolve ids at runtime with GET /api/3/action/package_show?id=<package-name> and match on the resource `name` or `url` rather than hardcoding an index into the resource array, whose order is not guaranteed.","_id is CKAN's synthetic 1-based row number added by the DataStore loader, not a key from the source data. It is the right column to ORDER BY for stable paging and it is meaningless as a join key or an entity id.","DO NOT USE ROW COUNTS AS A PROXY FOR SPENDING. FY2024 holds 8,750 rows against 118,859 for FY2023 and 235,796 for FY2025. That is a property of how that file was published, not a collapse in city spending. Every year-over-year comparison must be computed from \"Monetary Amount\" and, for anything published, cross-checked against the city's adopted budget documents. Treat the FY2024 resource as incomplete and say so.","THE CURRENT FISCAL YEAR ARRIVES AS OVERLAPPING SNAPSHOTS. Files named checkbook_explorer_20260701 (101,465 rows) and checkbook_explorer_20260801 (6,246 rows) are dated cuts of the in-progress year, not disjoint months. Taking every resource in the package double counts. For a clean series, use one resource per completed fiscal year and handle the current year explicitly.","\"Monetary Amount\" is text and includes NEGATIVE values for credits, reversals and corrections. Cast to numeric and keep the negatives: they are how the ledger balances, and filtering them out inflates every total.","NEARLY EVERY COLUMN NAME CONTAINS A SPACE, and one contains parentheses (\"Month(Number)\"). In SQL each must be double-quoted verbatim; in pandas each must be indexed by the exact string. An unquoted reference is a syntax error, which at least fails loudly, but a mistyped one silently returns nothing.","Entered is M/D/YYYY text with no zero padding (\"7/2/2024\"), so it sorts lexically as garbage. Use the pre-derived \"Fiscal Year\", Year and \"Month(Number)\" columns for time series, or cast with to_date(\"Entered\", 'MM/DD/YYYY').","\"Vendor Name\" is unnormalised free text: the same vendor appears with and without a legal suffix and with punctuation variants, so a groupby understates concentration. Normalise before ranking vendors."]},"summary":"Line-item municipal spending for the City of Boston from July 2011 (fiscal year 2012) to the present: 1,942,660 payment lines across 16 DataStore-loaded resources, one per fiscal…","title":"Boston Checkbook Explorer city expenditures, FY2012 to present","updatedAt":"2026-08-16T17:40:38.101Z","visibility":"public","websiteUrl":"https://www.boston.gov/departments/administration-and-finance","whiteLabel":"boston"}}