Simon Willison's Weblog: datasette-public-office-hours

Datasette Public Office Hours: Tools in LLM

2025-06-03T19:42:35+00:00

We're hosting the sixth in our series of Datasette Public Office Hours livestream sessions this Friday, 6th of June at 2pm PST (here's that time in your location).

The topic is going to be tool support in LLM, as introduced here.

I'll be walking through the new features, and we're also inviting five minute lightning demos from community members who are doing fun things with the new capabilities. If you'd like to present one of those please get in touch via this form.

Here's a link to add it to Google Calendar.

Tags: ai, datasette, generative-ai, llms, llm, llm-tool-use, datasette-public-office-hours

Datasette Public Office Hours 31st Jan at 2pm Pacific

2025-01-30T21:45:57+00:00

Datasette Public Office Hours 31st Jan at 2pm Pacific

We're running another Datasette Public Office Hours session on Friday 31st January at 2pm Pacific (more timezones here). We'll be featuring demos from the community again - take a look at the videos of the six demos from our last session for an idea of what to expect.

If you have something you would like to show, please drop us a line! We still have room for a few more demos.

Tags: community, datasette, discord, datasette-public-office-hours

Six short video demos of LLM and Datasette projects

2025-01-22T02:09:54+00:00

Last Friday Alex Garcia and I hosted a new kind of Datasette Public Office Hours session, inviting members of the Datasette community to share short demos of projects that they had built. The session lasted just over an hour and featured demos from six different people.

We broadcast live on YouTube, but I've now edited the session into separate videos. These are listed below, along with project summaries and show notes for each presentation.

You can also watch all six videos in this YouTube playlist.

llm-logs-feedback by Matthias Lübken

llm-logs-feedback is a plugin by Matthias Lübken for LLM which adds the ability to store feedback on prompt responses, using new llm feedback+1 and llm feedback-1 commands. These also accept an optional comment, and the feedback is stored in a feedback table in SQLite.

You can install the plugin from PyPI like this:

llm install llm-logs-feedback

The full plugin implementation is in the llm_logs_feedback.py file in Matthias' GitHub repository.

llm-model-gateway and llm-consortium by Thomas Hughes

Tommy Hughes has been developing a whole array of LLM plugins, including his llm-plugin-generator which is a plugin that can help write new plugins!

He started by demonstrating llm-model-gateway, a plugin that adds a llm serve command which starts a localhost server running an imitation of the OpenAI API against LLM models:

llm install llm-model-gateway
llm serve

Tommy's main demo was of llm-consortium, a plugin which can use a consortium of collaborating LLM models working together to solve problems.

llm install llm-consortium
llm consortium "Best way to prepare avocado toast" \
  --models claude-3.5-sonnet \
  --models gpt-4o \
  --arbiter gegemini-1.5-pro-latest \
  --confidence-threshold 0.8 \
  --max-iterations 3 \
  --output results.json

I ran this and got back:

The best way to prepare avocado toast involves selecting quality ingredients and careful preparation. Start with a thick slice of good quality bread, such as sourdough or whole grain. Toast it to your preferred level of crispness. While the bread is toasting, prepare a ripe avocado. Cut it in half, remove the pit, and scoop the flesh into a bowl. Mash the avocado with a fork, leaving some chunks for texture. Season with salt, black pepper, and a squeeze of fresh lemon juice to prevent browning. Optional additions include a touch of red pepper flakes.

Once the toast is ready, let it cool slightly before spreading the seasoned avocado evenly over it. Consider lightly rubbing a cut garlic clove on the warm toast for an extra layer of flavor (optional).

Enhance your avocado toast with your favorite toppings. Popular choices include: everything bagel seasoning, sliced tomatoes, radishes, a poached or fried egg (for added protein), microgreens, smoked salmon (for a more savory option), feta cheese crumbles, or a drizzle of hot sauce. For a finishing touch, drizzle with high-quality olive oil and sprinkle with sesame or chia seeds for added texture.

Consider dietary needs when choosing toppings. For example, those following a low-carb diet might skip the tomatoes and opt for more protein and healthy fats.

Finally, pay attention to presentation. Arrange the toppings neatly for a visually appealing toast. Serve immediately to enjoy the fresh flavors and crispy toast.

But the really interesting thing is the full log of the prompts and responses sent to Claude 3.5 Sonnet and GPT-4o, followed by a combined prompt to Gemini 1.5 Pro to have it arbitrate between the two responses. You can see the full logged prompts and responses here. Here's that results.json output file.

Congressional Travel Explorer with Derek Willis

Derek Willis teaches data journalism at the Philip Merrill College of Journalism at the University of Maryland. For a recent project his students built a Congressional Travel Explorer interactive using Datasette, AWS Extract and Claude 3.5 Sonnet to analyze travel disclosures from members of Congress.

One of the outcomes from the project was this story in Politico: Members of Congress have taken hundreds of AIPAC-funded trips to Israel in the past decade.

llm-questioncache with Nat Knight

llm-questioncache builds on top of https://llm.datasette.io/ to cache answers to questions, using embeddings to return similar answers if they have already been stored.

Using embeddings for de-duplication of similar questions is an interesting way to apply LLM's embeddings feature.

Improvements to Datasette Enrichments with Simon Willison

I've demonstrated improvements I've been making to Datasette's Enrichments system over the past few weeks.

Enrichments allow you to apply an operation - such as geocoding, a QuickJS JavaScript transformation or an LLM prompt - against selected rows within a table.

The latest release of datasette-enrichments adds visible progress bars and the ability to pause, resume and cancel an enrichment job that is running against a table.

Datasette comments, pins and write UI with Alex Garcia

We finished with three plugin demos from Alex, showcasing collaborative features we have been developing for Datasette Cloud:

datasette-write-ui provides tools for editing and adding data to Datasette tables. A new feature here is the ability to shift-click a row to open the editing interface for that row.
datasette-pins allows users to pin tables and databases to their Datasette home page, making them easier to find.
datasette-comments adds a commenting interface to Datasette, allowing users to leave comments on individual rows in a table.

Tags: community, data-journalism, demos, derek-willis, youtube, ai, datasette, alex-garcia, generative-ai, llms, llm, enrichments, datasette-public-office-hours

Datasette Public Office Hours Application

2025-01-16T18:38:31+00:00

Datasette Public Office Hours Application

We are running another Datasette Public Office Hours event on Discord tomorrow (Friday 17th January 2025) at 2pm Pacific / 5pm Eastern / 10pm GMT / more timezones here.

The theme this time around is lightning talks - we're looking for 5-8 minute long talks from community members about projects they are working on or things they have built using the Datasette family of tools (which includes LLM and sqlite-utils as well).

If you have a demo you'd like to share, please let us know via this form.

I'm going to be demonstrating my recent work on the next generation of Datasette Enrichments.

Tags: lightning-talks, datasette, discord, enrichments, datasette-public-office-hours

Project: Civic Band - scraping and searching PDF meeting minutes from hundreds of municipalities

2024-11-16T22:14:01+00:00

I interviewed Philip James about Civic Band, his "slowly growing collection of databases of the minutes from civic governments". Philip demonstrated the site and talked through his pipeline for scraping and indexing meeting minutes from many different local government authorities around the USA.

We recorded this conversation as part of yesterday's Datasette Public Office Hours session.

Civic Band

Philip was inspired to start thinking more about local government after the 2016 US election. He realised that there was a huge amount of information about decisions made by local authorities tucked away in their meeting minutes,but that information was hidden away in thousands of PDF files across many different websites.

There was this massive backlog of basically every decision that had ever been made by one of these bodies. But it was almost impossible to discover because it lives in these systems where the method of exchange is a PDF.

Philip lives in Alameda, which makes its minutes available via this portal powered by Legistar. It turns out there are a small number of vendors that provide this kind of software tool, so once you've written a scraper for one it's likely to work for many others as well.

Here's the Civic Band portal for Alameda, powered by Datasette.

It's running the datasette-search-all plugin and has both tables configured for full-text search. Here's a search for housing:

The technical stack

The public Civic Band sites all run using Datasette in Docker Containers - one container per municipality. They're hosted on a single Hetzner machine.

The ingestion pipeline runs separately from the main hosting environment, using a Mac Mini on Philp's desk at home.

OCR works by breaking each PDF up into images and then running Tesseract OCR against them directly on the Mac Mini. This processes in the order of 10,000 or less new pages of documents a day.

Philip treats PDF as a normalization target, because the pipeline is designed around documents with pages of text. In the rare event that a municipality publishes documents in another format such as .docx he converts them to PDF before processing.

PNG images of the PDF pages are served via a CDN, and the OCRd text is written to SQLite database files - one per municipality. SQLite FTS provides full-text search.

Scale and storage

The entire project currently comes to about 265GB on disk. The PNGs of the pages use about 350GB of CDN storage.

Most of the individual SQLite databases are very small. The largest is for Maui County which is around 535MB because that county has professional stenographers taking detailed notes for every one of their meetings.

Each city adds only a few documents a week so growth is manageable even as the number of cities grows.

Future plans

We talked quite a bit about a goal to allow users to subscribe to updates that match specific search terms.

Philip has been building out a separate site called Civic Observer to address this need, which will store searches and then execute the periodically using the Datasette JSON API, with a Django app to record state to avoid sending the same alert more than once.

I've had a long term ambition to build some kind of saved search alerts plugin for Datasette generally, to allow users to subscribe to new results for arbitrary SQL queries. My sqlite-chronicle library is part or that effort - it uses SQLite triggers to maintain version numbers for individual rows in a table, allowing you to query just the rows that have been inserted or modified since the version number last time you ran the query.

Philip is keen to talk to anyone who is interested in using Civic Band or helping expand it to even more cities. You can find him on the Datasette Discord.

Tags: data-journalism, political-hacking, politics, sqlite, datasette, datasette-public-office-hours

Visualizing local election results with Datasette, Observable and MapLibre GL

2024-11-09T23:32:06+00:00

Alex Garcia and myself hosted the first Datasette Open Office Hours on Friday - a live-streamed video session where we hacked on a project together and took questions and tips from community members on Discord.

We didn't record this one (surprisingly not a feature that Discord offers) but we hope to do more of these and record them in the future.

This post is a detailed write-up of what we built during the session.

San Mateo County election results

I live in El Granada, a tiny town just north of Half Moon Bay in San Mateo County, California.

Every county appears to handle counting and publishing election results differently. For San Mateo County the results are published on this page, and detailed per-precinct and per-candidate breakdowns are made available as a CSV file.

(I optimistically set up a Git scraper for these results in simonw/scrape-san-mateo-county-election-results-2024 only to learn that the CSV is updated just once a day, not continually as the ballots are counted.)

I'm particularly invested in the results of the Granada Community Services District board member elections. Our little town of El Granada is in "unincorporated San Mateo County" which means we don't have a mayor or any local officials, so the closest we get to hyper-local government is the officials that run our local sewage and parks organization! My partner Natalie ran the candidate forum event (effectively the debate) featuring three of the four candidates running for the two open places on the board.

Let's explore the data for that race using Datasette.

Importing CSV data into Datasette

I ran my part of the demo using Datasette Cloud, the beta of my new hosted Datasette service.

I started by using the pre-configured datasette-import plugin to import the data from the CSV file into a fresh table:

Modifying the schema

The table imported cleanly, but all of the columns from the CSV were still being treated as text. I used the datasette-edit-schema plugin to switch the relevant columns to integers so that we could run sums and sorts against them.

(I also noted that I really should add a "detect column types" feature to that plugin!)

The resulting 15,589 rows represent counts from individual precincts around the county for each of the races and measures on the ballot, with a row per precinct per candidate/choice per race.

Faceting and filtering the table

Since I'm interested in the Granada Community Services District election, I applied a facet on "Contest_title" and then used that to select that specific race.

I applied additional facets on "candidate_name" and "Precinct name".

This looks right to me: we have 7 precincts and 4 candidates for 28 rows in total.

Importing geospatial precinct shapes

Those precinct names are pretty non-descriptive! What does 33001 mean?

To answer that question, I added a new table.

San Mateo County offers precinct maps in the form of 23 PDF files. Our precincts are in the "Unincorporated Coastside" file:

Thankfully the county also makes that data available as geospatial data, hosted using Socrata with an option to export as GeoJSON.

The datasette-import plugin can handle JSON files... and if a JSON file contains a top-level object with a key that is an array of objects, it will import those objects as a table.

Dragging that file into Datasette is enough to import it as a table with a properties JSON column containing properties and a geometry JSON columnn with the GeoJSON geometry.

Here's where another plugin kicks in: datasette-leaflet-geojson looks for columns that contain valid GeoJSON geometries and... draws them on a map!

So now we can see the shape of the individual geometries.

Enriching that data to extract the precinct IDs

The precinctid is present in the data, but it's tucked away in a JSON object in that properties JSON blob. It would be more convenient if it was a top-level column.

Datasette's enrichments feature provides tools for running operations against every row in a table and adding new columns based on the results.

My Datasette Cloud instance was missing the datasette-enrichments-quickjs plugin that would let me run JavaScript code against the data. I used my privileged access on Datasette Cloud to add that plugin to my requirements and restarted the instance to install it.

I used that to run this JavaScript code against every row in the table and saved the output in a new precinct_id column:

function enrich(row) {
  return JSON.parse(row.properties).precinctid;
}

This took less than a second to run, adding and populating a new precinct_id column for the table.

Running a join

I demonstrated how to run a join between the election results and the precincts table using the Datasette SQL query editor.

I tried a few different things, but the most interesting query was this one:

select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where 
  Contest_title = "Granada Community Services District Members, Board of Directors"
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;

Creating an API token to access the data

I was nearly ready to hand over to Alex for the second half of our demo, where he would use Observable Notebooks to build some custom visualizations on top of the data.

A great pattern for this is to host the data in Datasette and then fetch it into Observable via the Datasette JSON API.

Since Datasette Cloud instances are private by default we would need to create an API token that could do this.

I used this interface (from the datasette-auth-tokens plugin) to create a new token with read-only access to all databases and tables in the instance:

Since we're running a dedicated instance just for Datasette Public Office Hours there's no reason not to distribute that read-only token in publically accessible code.

Getting CORS working

Embarrassingly, I had forgotten that we would need CORS headers in order to access the data from an Observable notebook. Thankfully we have another plugin for that: datasette-cors. I installed that quickly and we confirmed that it granted access to the API from Observable as intended.

I handed over to Alex for the next section of the demo.

Working with Datasette in Observable

Alex started by running a SQL query from client-side JavaScript to pull in the joined data for our specific El Granada race:

sql = `
select
  Precinct_name,
  precincts.geometry,
  Split_name,
  Reporting_flag,
  Update_count,
  Pct_Id,
  Pct_seq_nbr,
  Reg_voters,
  Turn_Out,
  Contest_Id,
  Contest_seq_nbr,
  Contest_title,
  Contest_party_name,
  Selectable_Options,
  candidate_id,
  candidate_name,
  Candidate_Type,
  cand_seq_nbr,
  Party_Code,
  total_ballots,
  total_votes,
  total_under_votes,
  total_over_votes,
  [Vote Centers_ballots],
  [Vote Centers_votes],
  [Vote Centers_under_votes],
  [Vote Centers_over_votes],
  [Vote by Mail_ballots],
  [Vote by Mail_votes],
  [Vote by Mail_under_votes],
  [Vote by Mail_over_votes]
from
  election_results join precincts on election_results.Precinct_name = precincts.precinct_id
where "Contest_title" = "Granada Community Services District Members, Board of Directors"
limit 101;`

And in the next cell:

raw_data = fetch(
  `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?_shape=array&sql=${encodeURIComponent(
    sql
  )}`,
  {
    headers: {
      Authorization: `Bearer ${secret}`
    }
  }
).then((r) => r.json())

Note the ?_shape=array parameter there, which causes Datasette to output the results directly as a JSON array of objects.

That's all it takes to get the data into Observable. Adding another cell like this confirms that the data is now available:

Inputs.table(raw_data)

Visualizing those with MapLibre GL

There are plenty of good options for visualizing GeoJSON data using JavaScript in an Observable notebook.

Alex started with MapLibre GL, using the excellent OpenFreeMap 3D tiles:

viewof map = {
  const container = html`<div style="height:800px;">`;
  yield container;
  const map = (container.value = new maplibregl.Map({
    container,
    zoom: 2,
    //style: "https://basemaps.cartocdn.com/gl/voyager-gl-style/style.json",
    style: "https://tiles.openfreemap.org/styles/liberty",
    scrollZoom: true
  }));
  yield container;

  map.on("load", function () {
    map.fitBounds(d3.geoBounds(data), { duration: 0 });
    map.addSource("precincts", {
      type: "geojson",
      data: data
    });
    map.addLayer({
      id: "precincts",
      type: "fill",
      source: "precincts",
      paint: {
        "fill-opacity": 0.4,
        "fill-color": [
          "case",
          ["==", ["get", "ratio"], null], "#000000",
          [
            "interpolate",
            ["linear"],
            ["get", "ratio"],
            0.0, "#0000ff",
            0.5, "#d3d3d3",
            1.0, "#ff0000"
          ]
        ]
      }
    });
    map.on("click", "precincts", (e) => {
      const { precinct, ratio } = e.features[0].properties;
      const description = JSON.stringify();

      new maplibregl.Popup()
        .setLngLat(e.lngLat)
        .setHTML(description)
        .addTo(map);
    });
  });
  invalidation.then(() => map.remove());
}

(This is just one of several iterations, I didn't capture detailed notes of every change Alex made to the code.)

Observable Plot

Observable notebooks come pre-loaded with the excellent Observable Plot charting library - Mike Bostock's high-level charting tool built on top of D3.

Alex used that to first render the shapes of the precincts directly, without even needing a tiled basemap:

Plot.plot({
  width,
  height: 600,
  legend: true,
  projection: {
    type: "conic-conformal",
    parallels: [37 + 4 / 60, 38 + 26 / 60],
    rotate: [120 + 30 / 60, 0],
    domain: data
  },
  marks: [
    Plot.geo(data, {
      strokeOpacity: 0.1,
      fill: "total_votes",
      title: (d) => JSON.stringify(d.properties),
      tip: true
    })
  ]
})

The parallels and rotate options there come from the handy veltman/d3-stateplane repo, which lists recommended settings for the State Plane Coordinate System used with projections in D3. Those values are for California Zone 3.

Bringing it all together

For the grand finale, Alex combined everything learned so far to build an interactive map allowing a user to select any of the 110 races on the ballot and see a heatmap of results for any selected candidate and option:

You can try this out in Alex's notebook. Here's the relevant code (Observable cells are divided by // --- comments). Note that Observable notebooks are reactive and allow variables to be referenced out of order.

// Select the contest
viewof contest = Inputs.select(contests, { label: "Choose a contest" })

// ---

// And the candidate
viewof candidate = Inputs.radio(
  candidates,
  {
    label: "Choose a candidate",
    value: candidates[0]
  }
)

// ---

// Show the map itself
Plot.plot({
  width,
  height: 600,
  legend: true,
  color: { scheme: "blues", legend: true },
  projection: {
    type: "mercator",
    domain: data2
  },
  marks: [
    Plot.geo(data2, {
      strokeOpacity: 0.1,
      fill: "ratio",
      tip: true
    })
  ]
})
# ---
data2 = ({
  type: "FeatureCollection",
  features: raw_data2.map((d) => ({
    type: "Feature",
    properties: {
      precinct: d.Precinct_name,
      total_ballots: d.total_ballots,
      ratio: JSON.parse(d.votes_by_candidate)[candidate] / d.total_ballots
    },
    geometry: JSON.parse(d.geometry)
  }))
})

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_grop_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

// Fetch the available contests
contests = query("select distinct Contest_title from election_results").then(
  (d) => d.map((d) => d.Contest_title)
)

// ---

// Extract available candidates for selected contest

candidates = Object.keys(
  JSON.parse(raw_data2[0].votes_by_candidate)
)

// ---

function query(sql, params = {}) {
  return fetch(
    `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?${new URLSearchParams(
      { sql, _shape: "array", ...params }
    ).toString()}`,
    {
      headers: {
        Authorization: `Bearer ${secret}`
      }
    }
  ).then((r) => r.json());
}

We'll be doing this again

This was our first time trying something like this and I think it worked really well. We're already thinking about ways to improve it next time:

I want to record these sessions and make them available on YouTube for people who couldn't be there live
It would be fun to mix up the format. I'm particularly keen on getting more people involved giving demos - maybe having 5-10 minute lightning demo slots so we can see what other people are working on

Keep an eye on this blog or on the Datasette Discord for news about future sessions.

Tags: geospatial, mapping, politics, projects, datasette, datasette-cloud, alex-garcia, datasette-public-office-hours, leaflet

Datasette Public Office Hours, Friday Nov 8th at 2pm PT

2024-11-07T19:10:10+00:00

Datasette Public Office Hours, Friday Nov 8th at 2pm PT

Tomorrow afternoon (Friday 8th November) at 2pm PT we'll be hosting the first Datasette Public Office Hours - a livestream video session on Discord where Alex Garcia and myself will live code on some Datasette projects and hang out to chat about the project.

This is our first time trying this format. If it works out well I plan to turn it into a series.

Tags: open-source, datasette, discord, alex-garcia, datasette-public-office-hours