How I automate my Substack newsletter with content from my blog

title: "How I automate my Substack newsletter with content from my blog"
Youtube_Thumbnail: "https://img.youtube.com/vi/BoPZltKDM-s/hqdefault.jpg"
sources:
  - "https://www.youtube.com/watch?v=BoPZltKDM-s&t=5s"
media_link: "https://www.youtube.com/watch?v=BoPZltKDM-s&t=5s"
Authors: "[[Simon Willison]]"
contentPublished: 2025-11-19
noteCreated: 2026-01-04
description: "Substack doesn't have an API, but they do support copy-and-paste... here's a walkthrough of the extensive chain of digital duct tape I use to go from Django+..."
tags:
  - "clippings"
  - "video"
takeaways:
subjects:
Status: "🙏🏼 Want To Read"

Description

Substack doesn't have an API, but they do support copy-and-paste... here's a walkthrough of the extensive chain of digital duct tape I use to go from Django+...

Notes

00:02 Newsletter Publishing Overview

Blog updated 2-3 times daily with content
Weekly Substack newsletter pulls from blog content
Takes only a couple minutes to compose

00:39 Observable Notebook Tool

00:51: Displays newsletter stats (days since last edition, stories, links, quotations, notes)
01:01: Can reorder content by dragging items
01:12: "Copy rich text newsletter to clipboard" button is the key feature
- Substack lacks API but accepts copy/paste

01:21 Publishing Process to Substack

01:24: Paste content into Substack editor
01:28: Manual steps required
- Set title
- 01:36: Add subtitle featuring second story

01:46 Content Editing and Troubleshooting

01:46: Watch for embedded videos that may not display correctly
02:01: Substack sometimes unhelpfully auto-converts links to YouTube embeds
- Delete unwanted embeds and fix URLs
02:53: Substack handles pasted images well
02:59: Long newsletter triggers warning ("posts too long for email") but can be ignored

03:10 Image and Alt Text Management

03:29: Animated GIFs may not display as images in paste
- Solution: Copy and re-paste the image directly
03:55: Alt Text Extractor tool for managing image accessibility
- Custom tool built to extract and copy alt text

05:05 Final Editing and Sending

05:05: Handle remaining YouTube embed bugs with URL truncation trick
05:41: Add tags (AI, LLM 2, etc.)
05:55: Select display image for newsletter
06:08: Click "Send to everyone now" to publish

06:25 Technical Architecture Overview

06:27: Observable platform for interactive JavaScript snippets
- Can execute API requests and render content

06:56 Data Pipeline: API Implementation

06:59: Fetch API request against JSON on data set instance
07:06: Executes SQL query to retrieve blog content
07:16: SQL query joins multiple tables
- Blog entries, bookmarks, quotes, notes
- Composes HTML within SQL (unconventional but functional)

07:58 Blog Infrastructure

Django application for content management
08:04: Supports adding links, quotes, and other content types
08:09: Data set powers API using SQLite
08:13: Accessible at data set.simwilson.net
- Can run SQL queries directly
- Returns formatted HTML and JSON

09:00 Data Synchronization: GitHub Backup

09:00: GitHub repository "Simon Wilson blog backup" runs on schedule
09:06: Exports data from Heroku Postgres to JSON files and SQLite
09:17: Workflow runs at 26 minutes past every 2 hours

09:24 Database Migration Tool: db2sqlite

09:27: Custom tool written 7 years ago
Converts Heroku Postgres to SQLite database
09:53: Allows specifying which tables to export
10:08: Published to fly.io hosting via data set publish command

10:21 Complete Infrastructure Stack

10:32: Every 2 hours, database updates on fly.io
10:38: Observable JavaScript can query updated database
10:43: Composed HTML ready for Substack copy/paste

10:48 Technology Stack Summary

Django + Postgres on Heroku (blog application)
GitHub Actions (scheduling and automation)
db2sqlite (database conversion)
fly.io (hosting SQLite database)
Observable (JavaScript interface)
Substack (newsletter distribution)

11:13 Benefits of This Approach

11:23: Low cost infrastructure
- Heroku: inexpensive for single database
- GitHub Actions: free
- fly.io: a few dollars per month
- Substack: free
11:34: Minimal maintenance once set up
11:37: Publishes weekly newsletter with blog content easily

How I automate my Substack newsletter with content from my blog

Interactive graph

On this page

Description

Notes

00:02 Newsletter Publishing Overview

00:39 Observable Notebook Tool

01:21 Publishing Process to Substack

01:46 Content Editing and Troubleshooting

03:10 Image and Alt Text Management

05:05 Final Editing and Sending

06:25 Technical Architecture Overview

06:56 Data Pipeline: API Implementation

07:58 Blog Infrastructure

09:00 Data Synchronization: GitHub Backup

09:24 Database Migration Tool: db2sqlite

10:21 Complete Infrastructure Stack

10:48 Technology Stack Summary

11:13 Benefits of This Approach