Simon Willison's Weblog: site-upgrades

Beats now have notes

2026-03-23T02:13:13+00:00

Last month I added a feature I call beats to this blog, pulling in some of my other content from external sources and including it on the homepage, search and various archive pages on the site.

On any given day these frequently outnumber my regular posts. They were looking a little bit thin and were lacking any form of explanation beyond a link, so I've added the ability to annotate them with a "note" which now shows up as part of their display.

Here's what that looks like for the content I published yesterday:

I've also updated the /atom/everything/ Atom feed to include any beats that I've attached notes to.

Tags: atom, blogging, site-upgrades

Writing about Agentic Engineering Patterns

2026-02-23T17:43:02+00:00

I've started a new project to collect and document Agentic Engineering Patterns - coding practices and patterns to help get the best results out of this new era of coding agent development we find ourselves entering.

I'm using Agentic Engineering to refer to building software using coding agents - tools like Claude Code and OpenAI Codex, where the defining feature is that they can both generate and execute code - allowing them to test that code and iterate on it independently of turn-by-turn guidance from their human supervisor.

I think of vibe coding using its original definition of coding where you pay no attention to the code at all, which today is often associated with non-programmers using LLMs to write code.

Agentic Engineering represents the other end of the scale: professional software engineers using coding agents to improve and accelerate their work by amplifying their existing expertise.

There is so much to learn and explore about this new discipline! I've already published a lot under my ai-assisted-programming tag (345 posts and counting) but that's been relatively unstructured. My new goal is to produce something that helps answer the question "how do I get good results out of this stuff" all in one place.

I'll be developing and growing this project here on my blog as a series of chapter-shaped patterns, loosely inspired by the format popularized by Design Patterns: Elements of Reusable Object-Oriented Software back in 1994.

I published the first two chapters today:

Writing code is cheap now talks about the central challenge of agentic engineering: the cost to churn out initial working code has dropped to almost nothing, how does that impact our existing intuitions about how we work, both individually and as a team?
Red/green TDD describes how test-first development helps agents write more succinct and reliable code with minimal extra prompting.

I hope to add more chapters at a rate of 1-2 a week. I don't really know when I'll stop, there's a lot to cover!

Written by me, not by an LLM

I have a strong personal policy of not publishing AI-generated writing under my own name. That policy will hold true for Agentic Engineering Patterns as well. I'll be using LLMs for proofreading and fleshing out example code and all manner of other side-tasks, but the words you read here will be my own.

Chapters and Guides

Agentic Engineering Patterns isn't exactly a book, but it's kind of book-shaped. I'll be publishing it on my site using a new shape of content I'm calling a guide. A guide is a collection of chapters, where each chapter is effectively a blog post with a less prominent date that's designed to be updated over time, not frozen at the point of first publication.

Guides and chapters are my answer to the challenge of publishing "evergreen" content on a blog. I've been trying to find a way to do this for a while now. This feels like a format that might stick.

If you're interested in the implementation you can find the code in the Guide, Chapter and ChapterChange models and the associated Django views, almost all of which was written by Claude Opus 4.6 running in Claude Code for web accessed via my iPhone.

Tags: blogging, design-patterns, projects, writing, ai, generative-ai, llms, ai-assisted-programming, vibe-coding, coding-agents, agentic-engineering, site-upgrades

Adding TILs, releases, museums, tools and research to my blog

2026-02-20T23:47:10+00:00

I've been wanting to add indications of my various other online activities to my blog for a while now. I just turned on a new feature I'm calling "beats" (after story beats, naming this was hard!) which adds five new types of content to my site, all corresponding to activity elsewhere.

Here's what beats look like:

Those three are from the 30th December 2025 archive page.

Beats are little inline links with badges that fit into different content timeline views around my site, including the homepage, search and archive pages.

There are currently five types of beats:

Releases are GitHub releases of my many different open source projects, imported from this JSON file that was constructed by GitHub Actions.
TILs are the posts from my TIL blog, imported using a SQL query over JSON and HTTP against the Datasette instance powering that site.
Museums are new posts on my niche-museums.com blog, imported from this custom JSON feed.
Tools are HTML and JavaScript tools I've vibe-coded on my tools.simonwillison.net site, as described in Useful patterns for building HTML tools.
Research is for AI-generated research projects, hosted in my simonw/research repo and described in Code research projects with async coding agents like Claude Code and Codex.

That's five different custom integrations to pull in all of that data. The good news is that this kind of integration project is the kind of thing that coding agents really excel at. I knocked most of the feature out in a single morning while working in parallel on various other things.

I didn't have a useful structured feed of my Research projects, and it didn't matter because I gave Claude Code a link to the raw Markdown README that lists them all and it spun up a parser regex. Since I'm responsible for both the source and the destination I'm fine with a brittle solution that would be too risky against a source that I don't control myself.

Claude also handled all of the potentially tedious UI integration work with my site, making sure the new content worked on all of my different page types and was handled correctly by my faceted search engine.

Prototyping with Claude Artifacts

I actually prototyped the initial concept for beats in regular Claude - not Claude Code - taking advantage of the fact that it can clone public repos from GitHub these days. I started with:

Clone simonw/simonwillisonblog and tell me about the models and views

And then later in the brainstorming session said:

use the templates and CSS in this repo to create a new artifact with all HTML and CSS inline that shows me my homepage with some of those inline content types mixed in

After some iteration we got to this artifact mockup, which was enough to convince me that the concept had legs and was worth handing over to full Claude Code for web to implement.

If you want to see how the rest of the build played out the most interesting PRs are Beats #592 which implemented the core feature and Add Museums Beat importer #595 which added the Museums content type.

Tags: blogging, museums, ai, til, generative-ai, llms, ai-assisted-programming, claude-artifacts, claude-code, site-upgrades

Experimenting with sponsorship for my blog and newsletter

2026-02-19T05:44:29+00:00

I've long been resistant to the idea of accepting sponsorship for my blog. I value my credibility as an independent voice, and I don't want to risk compromising that reputation.

Then I learned about Troy Hunt's approach to sponsorship, which he first wrote about in 2016. Troy runs with a simple text row in the page banner - no JavaScript, no cookies, unobtrusive while providing value to the sponsor. I can live with that!

Accepting sponsorship in this way helps me maintain my independence while offsetting the opportunity cost of not taking a full-time job.

To start with I'm selling sponsorship by the week. Sponsors get that unobtrusive banner across my blog and also their sponsored message at the top of my newsletter.

I will not write content in exchange for sponsorship. I hope the sponsors I work with understand that my credibility as an independent voice is a key reason I have an audience, and compromising that trust would be bad for everyone.

Freeman & Forrest helped me set up and sell my first slots. Thanks also to Theo Browne for helping me think through my approach.

Tags: newsletter, blogging, troy-hunt, site-upgrades

How I automate my Substack newsletter with content from my blog

2025-11-19T22:00:34+00:00

I sent out my weekly-ish Substack newsletter this morning and took the opportunity to record a YouTube video demonstrating my process and describing the different components that make it work. There's a lot of digital duct tape involved, taking the content from Django+Heroku+PostgreSQL to GitHub Actions to SQLite+Datasette+Fly.io to JavaScript+Observable and finally to Substack.

The core process is the same as I described back in 2023. I have an Observable notebook called blog-to-newsletter which fetches content from my blog's database, filters out anything that has been in the newsletter before, formats what's left as HTML and offers a big "Copy rich text newsletter to clipboard" button.

I click that button, paste the result into the Substack editor, tweak a few things and hit send. The whole process usually takes just a few minutes.

I make very minor edits:

I set the title and the subheading for the newsletter. This is often a direct copy of the title of the featured blog post.
Substack turns YouTube URLs into embeds, which often isn't what I want - especially if I have a YouTube URL inside a code example.
Blocks of preformatted text often have an extra blank line at the end, which I remove.
Occasionally I'll make a content edit - removing a piece of content that doesn't fit the newsletter, or fixing a time reference like "yesterday" that doesn't make sense any more.
I pick the featured image for the newsletter and add some tags.

That's the whole process!

The Observable notebook

The most important cell in the Observable notebook is this one:

raw_content = {
  return await (
    await fetch(
      `https://datasette.simonwillison.net/simonwillisonblog.json?sql=${encodeURIComponent(
        sql
      )}&_shape=array&numdays=${numDays}`
    )
  ).json();
}

This uses the JavaScript fetch() function to pull data from my blog's Datasette instance, using a very complex SQL query that is composed elsewhere in the notebook.

Here's a link to see and execute that query directly in Datasette. It's 143 lines of convoluted SQL that assembles most of the HTML for the newsletter using SQLite string concatenation! An illustrative snippet:

with content as (
  select
    id,
    'entry' as type,
    title,
    created,
    slug,
    '<h3><a href="' || 'https://simonwillison.net/' || strftime('%Y/', created)
      || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) 
      || '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/' || '">' 
      || title || '</a> - ' || date(created) || '</h3>' || body
      as html,
    'null' as json,
    '' as external_url
  from blog_entry
  union all
  # ...

My blog's URLs look like /2025/Nov/18/gemini-3/ - this SQL constructs that three letter month abbreviation from the month number using a substring operation.

This is a terrible way to assemble HTML, but I've stuck with it because it amuses me.

The rest of the Observable notebook takes that data, filters out anything that links to content mentioned in the previous newsletters and composes it into a block of HTML that can be copied using that big button.

Here's the recipe it uses to turn HTML into rich text content on a clipboard suitable for Substack. I can't remember how I figured this out but it's very effective:

Object.assign(
  html`<button style="font-size: 1.4em; padding: 0.3em 1em; font-weight: bold;">Copy rich text newsletter to clipboard`,
  {
    onclick: () => {
      const htmlContent = newsletterHTML;
      // Create a temporary element to hold the HTML content
      const tempElement = document.createElement("div");
      tempElement.innerHTML = htmlContent;
      document.body.appendChild(tempElement);
      // Select the HTML content
      const range = document.createRange();
      range.selectNode(tempElement);
      // Copy the selected HTML content to the clipboard
      const selection = window.getSelection();
      selection.removeAllRanges();
      selection.addRange(range);
      document.execCommand("copy");
      selection.removeAllRanges();
      document.body.removeChild(tempElement);
    }
  }
)

From Django+Postgresql to Datasette+SQLite

My blog itself is a Django application hosted on Heroku, with data stored in Heroku PostgreSQL. Here's the source code for that Django application. I use the Django admin as my CMS.

Datasette provides a JSON API over a SQLite database... which means something needs to convert that PostgreSQL database into a SQLite database that Datasette can use.

My system for doing that lives in the simonw/simonwillisonblog-backup GitHub repository. It uses GitHub Actions on a schedule that executes every two hours, fetching the latest data from PostgreSQL and converting that to SQLite.

My db-to-sqlite tool is responsible for that conversion. I call it like this:

db-to-sqlite \
  $(heroku config:get DATABASE_URL -a simonwillisonblog | sed s/postgres:/postgresql+psycopg2:/) \
  simonwillisonblog.db \
  --table auth_permission \
  --table auth_user \
  --table blog_blogmark \
  --table blog_blogmark_tags \
  --table blog_entry \
  --table blog_entry_tags \
  --table blog_quotation \
  --table blog_quotation_tags \
  --table blog_note \
  --table blog_note_tags \
  --table blog_tag \
  --table blog_previoustagname \
  --table blog_series \
  --table django_content_type \
  --table redirects_redirect

That heroku config:get DATABASE_URL command uses Heroku credentials in an environment variable to fetch the database connection URL for my blog's PostgreSQL database (and fixes a small difference in the URL scheme).

db-to-sqlite can then export that data and write it to a SQLite database file called simonwillisonblog.db.

The --table options specify the tables that should be included in the export.

The repository does more than just that conversion: it also exports the resulting data to JSON files that live in the repository, which gives me a commit history of changes I make to my content. This is a cheap way to get a revision history of my blog content without having to mess around with detailed history tracking inside the Django application itself.

At the end of my GitHub Actions workflow is this code that publishes the resulting database to Datasette running on Fly.io using the datasette publish fly plugin:

datasette publish fly simonwillisonblog.db \
  -m metadata.yml \
  --app simonwillisonblog-backup \
  --branch 1.0a2 \
  --extra-options "--setting sql_time_limit_ms 15000 --setting truncate_cells_html 10000 --setting allow_facet off" \
  --install datasette-block-robots \
  # ... more plugins

As you can see, there are a lot of moving parts! Surprisingly it all mostly just works - I rarely have to intervene in the process, and the cost of those different components is pleasantly low.

Tags: blogging, django, javascript, postgresql, sql, sqlite, youtube, heroku, datasette, observable, github-actions, fly, newsletter, substack, site-upgrades

New tags

2025-07-19T02:02:54+00:00

A few months ago I added a tool to my blog for bulk-applying tags to old content. It works as an extension to my existing search interface, letting me run searches and then quickly apply a tag to relevant results.

Since adding this I've been much more aggressive in categorizing my older content, including adding new tags when I spot an interesting trend that warrants its own page.

Today I added system-prompts and applied it to 41 existing posts that talk about system prompts for LLM systems, including a bunch that directly quote system prompts that have been deliberately published or leaked.

Other tags I've added recently include press-quotes for times I've been quoted in the press, agent-definitions for my ongoing collection of different ways people define "agents" and paper-review for posts where I review an academic paper.

Tags: blogging, tagging, site-upgrades

Disclosures

2025-06-23T18:06:02+00:00

I've added a Disclosures section to my about page, listing my various sources of income and the companies that directly sponsor my work or have supported it in the recent past.

I do not receive any compensation writing about specific topics on this blog - no sponsored content! I plan to continue this policy. If I ever change this I will disclose that both here and in the post itself. [...]

I see my credibility as one of my most valuable assets, so it's important to be transparent about how financial interests may influence my writing here.

I took inspiration from Molly White's disclosures page.

Tags: blogging, molly-white, site-upgrades

Subscribe to my sponsors-only monthly newsletter.

2025-05-25T06:06:17+00:00

Subscribe to my sponsors-only monthly newsletter

I’ve never liked the idea of charging for my content. I get enormous value from putting all of my writing and research out there for free.

So I’m trying something a little different: pay me to send you less.

I’m starting a sponsors-only monthly newsletter featuring just my heavily curated and edited highlights. If you only have ten minutes, what are the most important things not to miss from the last month?

Don’t want to pay? That’s fine, you can continue to follow my firehose for free!

Anyone who sponsors me for $10/month (or $50/month or more) on GitHub sponsors will receive my new newsletter on approximately the last day of the month. I’ll be sending out the first edition next week.

This blog and my newsletter will continue at their same breakneck pace. Paying subscribers can get a lower volume of stuff.

I'm cautiously optimistic that this could work. I've never liked the idea of business models that incentivize me to publish less. This feels like it encourages me to do what I'm doing already while giving people a rational reason to support my work, at a relatively small incremental cost to myself.

Tags: projects, blogging, github, email, newsletter, site-upgrades

Backfill your blog

2025-04-25T15:30:37+00:00

Fun fact: there's no rule that says you can't create a new blog today and backfill (and backdate) it with your writing from other platforms or sources, even going back many years.

I'd love to see more people do this!

(Inspired by this tweet by John F. Wu introducing his new blog. I did this myself when I relaunched this blog back in 2017.)

Tags: blogging, site-upgrades

Note on 26th March 2025

2025-03-26T06:11:30+00:00

I've added a new content type to my blog: notes. These join my existing types: entries, bookmarks and quotations.

A note is a little bit like a bookmark without a link. They're for short form writing - thoughts or images that don't warrant a full entry with a title. The kind of things I used to post to Twitter, but that don't feel right to cross-post to multiple social networks (Mastodon and Bluesky, for example.)

I was partly inspired by Molly White's short thoughts, notes, links, and musings.

I've been thinking about this for a while, but the amount of work involved in modifying all of the parts of my site that handle the three different content types was daunting. Then this evening I tried running my blog's source code (using files-to-prompt and LLM) through the new Gemini 2.5 Pro:

files-to-prompt . -e py -c | \
  llm -m gemini-2.5-pro-exp-03-25 -s \
  'I want to add a new type of content called a Note,
  similar to quotation and bookmark and entry but it
  only has a markdown text body. Output all of the
  code I need to add for that feature and tell me
  which files to add  the code to.'

Gemini gave me a detailed 13 step plan covering all of the tedious changes I'd been avoiding having to figure out!

The code is in this PR, which touched 18 different files. The whole project took around 45 minutes start to finish.

(I used Claude to brainstorm names for the feature - I had it come up with possible nouns and then "rank those by least pretentious to most pretentious", and "notes" came out on top.)

This is now far too long for a note and should really be upgraded to an entry, but I need to post a first note to make sure everything is working as it should.

Tags: blogging, projects, gemini, ai-assisted-programming, claude, molly-white, files-to-prompt, site-upgrades

OpenAI DevDay 2024 live blog

2024-10-01T17:17:13+00:00

I'm at OpenAI DevDay in San Francisco, and I'm trying something new: a live blog, where this entry will be updated with new notes during the event.

See OpenAI DevDay: Let’s build developer tools, not digital God for my notes written after the event, and Building an automatically updating live blog in Django for details about how this live blogging system worked under the hood.

Tags: blogging, ai, openai, generative-ai, llms, live-blog, site-upgrades

New blog feature: Support for markdown in quotations

2024-06-24T15:51:03+00:00

New blog feature: Support for markdown in quotations

Another incremental improvement to my blog. I've been collecting quotations here since 2006 - I now render them using Markdown (previously they were just plain text). Here's one example. The full set of 920 (and counting) quotations can be explored using this search filter.

Tags: blogging, projects, markdown, site-upgrades

Tags with descriptions

2024-06-18T16:50:07+00:00

Tags with descriptions

Tiny new feature on my blog: I can now add optional descriptions to my tag pages, for example on datasette and sqlite-utils and prompt-injection.

I built this feature on a live call this morning as an unplanned demonstration of GitHub's new Copilot Workspace feature, where you can run a prompt against a repository and have it plan, implement and file a pull request implementing a change to the code.

My prompt was:

Add a feature that lets me add a description to my tag pages, stored in the database table for tags and visible on the /tags/x/ page at the top

It wasn't as compelling a demo as I expected: Copilot Workspace currently has to stream an entire copy of each file it modifies, which can take a long time if your codebase includes several large files that need to be changed.

It did create a working implementation on its first try, though I had given it an extra tip not to forget the database migration. I ended up making a bunch of changes myself before I shipped it, listed in the pull request.

I've been using Copilot Workspace quite a bit recently as a code explanation tool - I'll prompt it to e.g. "add architecture documentation to the README" on a random repository not owned by me, then read its initial plan to see what it's figured out without going all the way through to the implementation and PR phases. Example in this tweet where I figured out the rough design of the Jina AI Reader API for this post.

Tags: blogging, github, projects, tagging, ai, generative-ai, llms, site-upgrades

A homepage redesign for my blog's 22nd birthday

2024-06-12T19:59:17+00:00

This blog is 22 years old today! I wrote up a whole bunch of higlights for the 20th birthday a couple of years ago. Today I'm celebrating with something a bit smaller: I finally redesigned the homepage.

I publish three kinds of content on my blog: entries (like this one), "blogmarks" (aka annotated links) and quotations. Until recently the entries were the main feature on the (desktop) homepage, with blogmarks and quotations relegated to the sidebar.

Back in April I implemented Markdown support for my blogmarks, allowing me to include additional links and quotations in the body of those descriptions.

I was inspired in this by Daring Fireball, which has long published a combination of annotated links combined with longer essay style entries.

It turns out I really like posting longer-form content attached to links! Here's one from earlier today which rivals my full entries in length.

These were looking pretty cramped in the sidebar:

So I've done a small redesign. The left hand column on my homepage now displays entries, quotations and blogmarks as a combined list, reusing the format I already had in place for the tag page.

The right hand column is for "highlights", aka my longer form blog entries.

The mobile version of my site was already serving content mixed together like this, so this change mainly brings the desktop version in line with the mobile one.

Here's the issue on GitHub and the commit that implemented the change.

Tags: blogging, site-upgrades

Blogmarks that use markdown

2024-04-25T04:34:18+00:00

Blogmarks that use markdown

I needed to attach a correction to an older blogmark (my 20-year old name for short-form links with commentary on my blog) today - but the commentary field has always been text, not HTML, so I didn't have a way to add the necessary link.

This motivated me to finally add optional Markdown support for blogmarks to my blog's custom Django CMS. I then went through and added inline code markup to a bunch of different older posts, and built this Django SQL Dashboard to keep track of which posts I had updated.

Tags: blogging, projects, markdown, django-sql-dashboard, site-upgrades

Semi-automating a Substack newsletter with an Observable notebook

2023-04-04T17:55:28+00:00

I recently started sending out a weekly-ish email newsletter consisting of content from my blog. I've mostly automated that, using an Observable Notebook to generate the HTML. Here's how that system works.

What goes in my newsletter

My blog has three types of content: entries, blogmarks and quotations. "Blogmarks" is a name I came up with for bookmarks in 2003.

Blogmarks and quotations show up in my blog's sidebar, entries get the main column - but on mobile the three are combined into a single flow.

These live in a PostgreSQL database managed by Django. You can see them defined in models.py in my blog's open source repo.

My newsletter consists of all of the new entries, blogmarks and quotations since I last sent it out. I include the entries first in reverse chronological order, since usually the entry I've just written is the one I want to use for the email subject. The blogmarks and quotations come in chronological order afterwards.

I'm including the full HTML for everything: people don't need to click through back to my blog to read it, all of the content should be right there in their email client.

The Substack API: RSS and copy-and-paste

Substack doesn't yet offer an API, and have no public plans to do so.

They do offer an RSS feed of each newsletter though - add /feed to the newsletter subdomain to get it. Mine is at https://simonw.substack.com/feed.

So we can get data back out again... but what about getting data in? I don't want to manually assemble a newsletter from all of these different sources of data.

That's where copy-and-paste comes in.

The Substack compose editor incorporates a well built rich-text editor. You can paste content into it and it will clean it up to fit the subset of HTML that Substack supports... but that's a pretty decent subset. Headings, paragraphs, lists, links, code blocks and images are all supported.

The vast majority of content on my blog fits that subset neatly.

Crucially, pasting in images as part of that rich text content Just Works: Substack automatically copies any images to their substack-post-media S3 bucket and embeds links to their CDN in the body of the newsletter.

So... if I can generate the intended rich-text HTML for my whole newsletter, I can copy and paste it directly into the Substack.

That's exactly what my new Observable notebook does: https://observablehq.com/@simonw/blog-to-newsletter

Generating HTML is a well trodden path, but I also wanted a "copy to clipboard" button that would copy the rich text version of that HTML such that pasting it into Substack would do the right thing.

With a bit of help from MDN and ChatGPT (my TIL) I figured out the following:

function copyRichText(html) {
  const htmlContent = html;
  // Create a temporary element to hold the HTML content
  const tempElement = document.createElement("div");
  tempElement.innerHTML = htmlContent;
  document.body.appendChild(tempElement);
  // Select the HTML content
  const range = document.createRange();
  range.selectNode(tempElement);
  // Copy the selected HTML content to the clipboard
  const selection = window.getSelection();
  selection.removeAllRanges();
  selection.addRange(range);
  document.execCommand("copy");
  selection.removeAllRanges();
  document.body.removeChild(tempElement);
}

This works great! Set up a button that triggers that function and clicking that button will copy a rich text version of the HTML to the clipboard, such that pasting it directly into the Substack editor has the desired effect.

Assembling the HTML

I love using Observable Notebooks for this kind of project: quick data integration tools that need a UI and will likely be incrementally improved over time.

Using Observable for these means I don't need to host anything and I can iterate my way to the right solution really quickly.

First, I needed to retrieve my entries, blogmarks and quotations.

I never built an API for my Django blog directly, but a while ago I set up a mechanism that exports the contents of my blog to my simonwillisonblog-backup GitHub repository for safety, and then deploys a Datasette/SQLite copy of that data to https://datasette.simonwillison.net/.

Datasette offers a JSON API for querying that data, and exposes open CORS headers which means JavaScript running in Observable can query it directly.

Here's an example SQL query running against that Datasette instance - click the .json link on that page to get that data back as JSON instead.

My Observable notebook can then retrieve the exact data it needs to construct the HTML for the newsletter.

The smart thing to do would have been to retrieve the data from the API and then use JavaScript inside Observable to compose that together into the HTML for the newsletter.

I decided to challenge myself to doing most of the work in SQL instead, and came up with the following absolute monster of a query:

with content as (
  select
    'entry' as type, title, created, slug,
    '<h3><a href="' || 'https://simonwillison.net/' || strftime('%Y/', created)
      || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) 
      || '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/' || '">' 
      || title || '</a> - ' || date(created) || '</h3>' || body
      as html,
    '' as external_url
  from blog_entry
  union all
  select
    'blogmark' as type,
    link_title, created, slug,
    '<p><strong>Link</strong> ' || date(created) || ' <a href="'|| link_url || '">'
      || link_title || '</a>:' || ' ' || commentary || '</p>'
      as html,
  link_url as external_url
  from blog_blogmark
  union all
  select
    'quotation' as type,
    source, created, slug,
    '<strong>Quote</strong> ' || date(created) || '<blockquote><p><em>'
    || replace(quotation, '
', '<br>') || '</em></p></blockquote><p><a href="' ||
    coalesce(source_url, '#') || '">' || source || '</a></p>'
    as html,
    source_url as external_url
  from blog_quotation
),
collected as (
  select
    type,
    title,
    'https://simonwillison.net/' || strftime('%Y/', created)
      || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) || 
      '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/'
      as url,
    created,
    html,
    external_url
  from content
  where created >= date('now', '-' || :numdays || ' days')   
  order by created desc
)
select type, title, url, created, html, external_url
from collected 
order by 
  case type 
    when 'entry' then 0 
    else 1 
  end,
  case type 
    when 'entry' then created 
    else -strftime('%s', created) 
  end desc

This logic really should be in the JavaScript instead! You can try that query in Datasette.

There are a bunch of tricks in there, but my favourite is this one:

select 'https://simonwillison.net/' || strftime('%Y/', created)
  || substr(
    'JanFebMarAprMayJunJulAugSepOctNovDec',
    (strftime('%m', created) - 1) * 3 + 1, 3
  ) ||  '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/'
  as url

This is the trick I'm using to generate the URL for each entry, blogmark and quotation.

These are stored as datetime values in the database, but the eventual URLs look like this:

https://simonwillison.net/2023/Apr/2/calculator-for-words/

So I need to turn that date into a YYYY/Mon/DD URL component.

One problem: SQLite doesn't have a date format string that produces a three letter month abbreviation. But... with cunning application of the substr() function and a string of all the month abbreviations I can get what I need.

The above SQL query plus a little bit of JavaScript provides almost everything I need to generate the HTML for my newsletter.

Excluding previously sent content

There's one last problem to solve: I want to send a newsletter containing everything that's new since my last edition - I don't want to send out the same content twice.

I came up with a delightfully gnarly solution to that as well.

As mentioned earlier, Substack provides an RSS feed of previous editions. I can use that data to avoid including content that's already been sent.

One problem: the Substack RSS feed does't include CORS headers, which means I can't access it directly from my notebook.

GitHub offers CORS headers for every file in every repository. I already had a repo that was backing up my blog... so why not set that to backup my RSS feed from Substack as well?

I added this to my existing backup.yml GitHub Actions workflow:

- name: Backup Substack
  run: |-
    curl 'https://simonw.substack.com/feed' | \
      python -c "import sys, xml.dom.minidom; print(xml.dom.minidom.parseString(sys.stdin.read()).toprettyxml(indent='  '))" \
      > simonw-substack-com.xml

I'm piping it through a tiny Python script here to pretty-print the XML before saving it, because pretty-printed XML is easier to read diffs against later on.

Now simonw-substack-com.xml is a copy of my RSS feed in a GitHub repo, which means I can access the data directly from JavaScript running on Observable.

Here's the code I wrote there to fetch that RSS feed, parse it as XML and return a string containing just the HTML of all of the posts:

previousNewsletters = {
  const response = await fetch(
    "https://raw.githubusercontent.com/simonw/simonwillisonblog-backup/main/simonw-substack-com.xml"
  );
  const rss = await response.text();
  const parser = new DOMParser();
  const xmlDoc = parser.parseFromString(rss, "application/xml");
  const xpathExpression = "//content:encoded";

  const namespaceResolver = (prefix) => {
    const ns = {
      content: "http://purl.org/rss/1.0/modules/content/"
    };
    return ns[prefix] || null;
  };

  const result = xmlDoc.evaluate(
    xpathExpression,
    xmlDoc,
    namespaceResolver,
    XPathResult.ANY_TYPE,
    null
  );
  let node;
  let text = [];
  while ((node = result.iterateNext())) {
    text.push(node.textContent);
  }
  return text.join("\n");
}

Then I span up a regular expression to extract all of the URLs from that HTML:

previousLinks = {
  const regex = /(?:"|&quot;)(https?:\/\/[^\s"<>]+)(?:"|&quot;)/g;
  return Array.from(previousNewsletters.matchAll(regex), (match) => match[1]);
}

Added a "skip existing" toggle checkbox to my notebook:

viewof skipExisting = Inputs.toggle({
  label: "Skip content sent in prior newsletters"
})

And added this code to filter the raw content based on whether or not the toggle was selected:

content = skipExisting
  ? raw_content.filter(
      (e) =>
        !previousLinks.includes(e.url) &&
        !previousLinks.includes(e.external_url)
    )
  : raw_content

The url is the URL to the post on my blog. external_url is the URL to the original source of the blogmark or quotation. A match against ether of those should exclude the content from my next newsletter.

My workflow for sending a newsletter

Given all of the above, sending a newsletter out is hardly any work at all:

Ensure the most recent backup of my blog has run, such that the Datasette instance contains my latest content. I do that by triggering this action.
Navigate to https://observablehq.com/@simonw/blog-to-newsletter - select "Skip content sent in prior newsletters" and then click the "Copy rich text newsletter to clipboard" button.
Navigate to the Substack "publish" interface and paste that content into the rich text editor.
Pick a title and subheading, and maybe add a bit of introductory text.
Preview it. If the preview looks good, hit "send".

Copy and paste APIs

I think copy and paste is under-rated as an API mechanism.

There are no rate limits or API keys to worry about.

It's supported by almost every application, even ones that are resistant to API integrations.

It even works great on mobile phones, especially if you include a "copy to clipboard" button.

My datasette-copyable plugin for Datasette is one of my earlier explorations of this. It makes it easy to copy data out of Datasette in a variety of useful formats.

This Observable newsletter project has further convinced me that the clipboard is an under-utilized mechanism for building tools to help integrate data together in creative ways.

Tags: blogging, projects, datasette, observable, cors, newsletter, substack, site-upgrades

Getting the blog back together

2017-10-01T22:15:37+00:00

Getting this blog up and running again has turned out to be one of those side-projects that keeps threatening to fall down a rabbit hole.

I kicked off the work a couple of years ago at the Django Birthday event in Lawrence, KS and got most of the way done. Then life got in the way... and last weekend I decided to try and get it over the finish line. Today, I'm forcing myself to ship even though there are still tons of things I want to do with it. Perfect is the enemy of done. That goes for my first-blog-post-in-seven-years too.

I may not have blogged in seven years, but I did answer a whole bunch of questions on Quora. I've imported my Quora answers (extracted using this userscript) and used them to help fill out the intervening years. I plan to do the same with a few other types of content as well.

Tags: blogging, quora, site-upgrades

1000th Blogmark

2004-08-26T00:30:54+00:00

I just posted my 1000th blogmark. I can't emphasize enough how much of an impact this 15 minute hack has had on both my browsing and my blogging habits. While I still tend to leave browser windows open for days at a time, I now at least have a procedure for getting rid of the ones that still interest me. More importantly, having blogmarks has eliminated the temptation to write a full blog entry (with quotation) just to share a link. This has dramatically reduced my posting rate, but has meant that when I do post an entry I usually have something moderately interesting to say.

To celebrate this personal milestone, I've linked up the rudimentary LIKE query search engine I've been using for a while on the blogmarks index page. My long term aim is still to integrate them with my main content and add comments in the style of photomatt, but that would require more time spent hacking on my blogging system (or switching to WordPress) than I have to spend right now.

Tags: blogging, site-upgrades

More blogmark tweaks

2003-12-11T00:28:50+00:00

I'm up to 110 blogmarks now, and from my point of view they're the single most useful feature I've added to this site in a long time. I've modified my day archive pages to show the blogmarks added on that day, and I'm considering adding them to individual entry pages as well so that an entry is displayed along with the blogmarks added while that entry was at the top of my blog. The idea there is that I could aim to blogmark "related items" for the top entry, although obviously unrelated sites would end up in there as well.

The other thing I'm currently mulling over is whether or not blogmarks should allow comments. Jason Kottke's inline links get a decent amount of comment traffic but I'm not sure mine would get enough individual traffic to warrant a comments thread for each one. I could always add comments to the blogmark daily archive pages, although that could get confusing on days with a lot of comment-worthy links.

Does anyone actually look at the blogmarks? Would you comment on them or read the comments if they were available?

Tags: blogging, jason-kottke, site-upgrades

Blogmarks

2003-11-24T00:52:16+00:00

This entry was going to be another list of links, together with a note about how much I really needed to set up a separate link blog. Then I realised that it would make more sense just to set one up so that's exactly what I've done. I still need to implement the archive but it's getting dark so I'm posting this and heading home.

My main points of inspiration were Paul Hammond's bookmark store, Mark Pilgrim's b-links, Anil Dash's Daily Links and Jason Kottke's Remaindered Links. Since there didn't seem to be any naming convention I decided to call them blogmarks, which isn't a new term but doesn't seem to have a widely accepted meaning yet either.

The system is powered by a simple bookmarklet. To make things a little more interesting I'm capturing the referral information and using it to automatically generate the 'via' link; since the title of the previous page isn't available in Javascript I extract is using a server side script instead. I swayed briefly between using page extracts a la Hammond or sarcastic commentary a la Pilgrim and decided that commentary would be far more fun.

Tags: anil-dash, blogging, jason-kottke, mark-pilgrim, paul-hammond, site-upgrades

New anti-comment-spam measure

2003-10-13T08:22:09+00:00

I've added a new anti-comment-spam measure to this site. The majority of comment spam exists for one reason and one reason only to increase the Google PageRank of the site linked from the spam and specifically to increase its ranking for the term used in the link. This is why so many comment spams include links like this: Cheap Viagra.

Cut off the PageRank boost and you cut off the advantage of spamming, simple as that. I've altered my comments system to redirect ALL outgoing links through a simple redirect script, and added that script to my robots.txt file. Links still work fine (even the referral information persists across the redirect) but Google will ignore them completely when calculating PageRank.

Will this reduce the floods of comment spam my site receives? Probably not; I've added a note about the restriction to my 'add comment' form but I doubt many spammers bother to read much about the sites they are targetting. What's really needed is for this technique to become widespread by being integrated in to existing blogging tools - are you listening Moveable Type hackers?

Update: Sencer has pointed out in the comments that PageRank persists over redirects, and Google appears to ignore robots.txt when used to hide a redirecting page. I've updated my redirection script to use javascript to power the redirect (with a link for people with javascript disabled) and an extra meta tag to remind Google not to follow the link. This has the unfortunate side effect that referral information no longer persists across the redirect.

Tags: projects, robots-txt, spam, site-upgrades

Signing comments on blogs

2003-07-22T21:10:50+00:00

Adrian Holovaty has implemented reserved comment names in his blog, a feature that prevents anyone apart from him from using the names "Adrian", "Adrian H." or "Adrian Holovaty" when posting a comment. François Nonnenmacher suggests extending the idea to allow people to "confirm" their authorship of comments on any blog using a TrackBack sent to their site that in turn causes them to be sent an alert email, which they can then use to confirm their comment. I like his idea of authentication based on URLs (email addresses are no good; they should not be publically displayed for fear of spam harvesters) but I think I've come up with an alternative authentication scheme that removes the need for the user to manually confirm authorship. This is pretty complicated, so bare with me.

The comment author enter's their comment in to a form on the site. They see a standard icon indicating that the blog in question supports comment signing. Rather than manually entering their name and URL, they activate a bookmarklet that they have previously added to their browser.
The bookmarklet fills in the name and URL fields for them. It also takes the comment, appends a secret key (stored in the bookmarklet) and finds the MD5 hash of the new string, using the Javascript MD5 library. It inserts this hash in to a hidden field in the comment form.
The user can now submit the new comment. That's all they have to do.
The weblog server now kicks in to action. If the comment has not been signed (there is no hash in the hidden field) it adds the comment normally, noting that it should be displayed as an "unsigned" comment on the comments page. End of story.
If it has been signed, the server has some work to do. First it must start loading the URL indicated by the user on the comment form. It is looking for a <link rel="signature"> element, which will provide the URL of a signature authenticating web service. If the </head> tag is reached, the system can assume the link element does not exist and can mark the comment as "unsigned",
If the web service is found, the server can now send it the comment and the User's site URL. The web service (which knows the user's secret key) will respond with a hash created in the same way as the one constructed by the bookmarklet.
If the hash returned by the web service matches the hash provided by the bookmarklet, the comment is considered "signed". The server can store it as such, and later display it with an icon or style that indicates it is a signed comment. If they do not match, the server can either store the comment as "unsigned" or even flag it as "untrusted", since it was incorrectly signed.

As you can see, it's a relatively complicated system. The comment authors must have a custom bookmarklet and add a tag to their home page indicating their authenticating web service URL. Note that they do not need to host the authentication web service themselves - they can instead point to one run by someone else who they trust (trust here is essential as the web service must know the user's private key). Meanwhile, the blogging system needs to be able to perform HTTP requests.

The key advantage of my system is that, being based on MD5, it is relatively easy to implement (as opposed to a system based on something like PGP). Provided no one points out any immediate flaws, I would happily construct a prototype in PHP. I'm sure a Perl implementation for Moveable Type users would not prove much of a challenge to any talented plugin author.

Security wise, it strikes me that the weakest link is the client side bookmarklet which comment authors would need to use. However, comment signing is not the most critical security application in the world and comment authors could easily change their password by updating their bookmarklet and alerting their signature web-service provider (which could even be themselves) of the change.

And if the signature idea doesn't win any favour, the idea of having a bookmarklet to fill in your name and URL in blog comment forms is one I've been meaning to share for some time.

Tags: adrian-holovaty, blogging, hashing, security, trackback, site-upgrades

Small design tweak, big difference

2003-06-14T02:25:31+00:00

I've changed from using the day as the principle heading on the front page to using the title of each post instead. This is quite a minor alteration, but I expect it to have a relatively large impact on my blogging habits. For the past year I have treated my blog as a daily endeavour, thanks almost entirely to the way the site was layed out. This was intentional; when I orginally launched blog I made the decision to keep each entry as part of an ongoing narrative, with no individual entry titles and permalinks to entries in the context of the day they were posted.

The decision not to use entry titles turned out to be a costly mistake, which I eventually fixed back in April. The problem with the date headers was far more subtle: over the past few months, I've found myself frequently struggling to rush out an entry before midnight in a bid to keep up the "daily" nature of the site. This bizzare compulsion was spurred on by the glaring hole left in my front page if I missed a day.

By removing the day headers entirely, I hope to shift the focus of this blog from religious daily updates to entries with a little more substance. I think the psychology of a blog's design is easily under-rated; I've already noticed that my blog entries have been getting longer since I started adding entry titles. At any rate, with this latest design tweak I certainly won't be rushing out poor quality entries before midnight any more.

Tags: design, site-upgrades

Category specific RSS feeds

2003-04-08T21:59:12+00:00

I frequently check the Python Programmer Weblogs page for an addition to my daily blogging fix. It's a simple but very effective idea: Subscribe an aggregator to a bunch of feeds about a similar topic and publish the results for all to see. I'm one of the more prolific bloggers of the ones listed there, and since I tend to post a whole bunch of entries in a relatively narrow timeframe my stuff often appears in a big lump on the front page of the site. I've been feeling slightly guilty about this, as most of my posts have nothing to do with Python at all. So I've finally got my act together and knocked out RSS feeds for individual blog categories.

The Python Programmer Weblogs script apparently scrapes this wiki page for feed information, so I've updated the link on there to my RSS feed.

Tags: blogging, site-upgrades

Pingback implemented

2002-09-02T15:18:27+00:00

I've implemented PingBack on my blog. PingBack is a system for tracking who is linking to your blog in a controlled way, based on a post by Stuart a few months ago. The idea is that when you link to a PingBack enabled blog you (or your blogging tool) should send an XML-RPC "ping" to that blog's PingBack server telling it where you have linked to and where you linked from. The PingBack server can then grab your page, check that the link is there and extract a title and short description from the blog. The system is an alternative to (and was inspired by) MoveableType's TrackBack feature. Stuart and I are actively developing the idea and will be releasing code and documentation to help other people experiment with the system in the near future.

Tags: pingback, projects, stuart-langridge, trackback, xml-rpc, site-upgrades

Fixed validation again

2002-06-16T12:19:15+00:00

The road to validity is frought with peril. I've just fixed another small group of errors that were preventing this page from validating (after spotting the ominous W3C validator in today's user-agent logs). This time is was a couple of forgotten </p> tags and an unescaped ampersand.

There has to be a technological way of helping avoid these errors. Originally I wanted to be able to edit my entries in some kind of specialised markup language (such as WikiText or UBBCode) that the blogging sofftware could convert into valid XHTML, but I quickly realised that the most flexible markup language for blog entries is XHTML itself thanks to the built in support for everything from quotes to lists and code samples.

Thinking about it, almost all of the common errors I am experiencing come from the XML parser rather than the rules governing XHTML. I need an XML parser that examines each post as (or before) it is added to the blog and checks for well-formedness. Expat (used in PHP for event based XML parsing) does not validate documents against a DTD but it DOES die with an error if an XML document is malformed. It looks like it could be just what I need.

The ideal alternative would be for the W3C to create a web service back end for their validator so blogging software can check the validity of new entries automatically.

Tags: site-upgrades