Bulk and SQL access

-- Whole corpus into a local database file. Globs are not supported over plain
-- HTTPS, so read the file list out of the manifest and pass it as an array.
INSTALL httpfs; LOAD httpfs; INSTALL json; LOAD json;

CREATE TABLE law AS
SELECT * FROM read_parquet(
  (SELECT list(f.url)
   FROM (SELECT unnest(files) AS f
         FROM read_json_auto('https://oss-data-us.vaquill.ai/index.json'))
   WHERE f.file LIKE '%.parquet')
);

-- After downloading the tarball and unpacking
CREATE TABLE law (
  act_id text PRIMARY KEY, citation text, citation_short text,
  state text, jurisdiction text, document_type text,
  title_number text, title_name text, chapter text, chapter_name text,
  section_number text, section_title text, breadcrumb text, display_path text,
  act_status text, text text, word_count bigint, source_url text,
  last_amended_year bigint, subsection_count bigint,
  cross_references_usc text, cross_references_cfr text,
  public_laws_referenced text, year bigint
);
-- then COPY from a Parquet-aware loader, or convert with DuckDB first

curl -s https://oss-data-us.vaquill.ai/index.json \
  | jq -r '.files[] | select(.file|endswith(".parquet")) | "\(.sha256)  \(.file)"' \
  > SHA256SUMS
shasum -a 256 -c SHA256SUMS