@indiepub/db

Drizzle ORM schema and migrations for Cloudflare D1. This package defines the database structure shared by all IndiePub packages.

Schema overview

entries

The core table — stores all post types in a single flat structure.

Column	Type	Notes
id	text PK	nanoid
slug	text unique	URL-safe identifier
url	text	canonical URL
type	text	post-type-discovery result
status	text	draft | published | deleted
visibility	text	public | unlisted | private
name	text	title (articles, bookmarks)
content	text	body text (HTML allowed)
summary	text	excerpt
photo	text	image URL
in_reply_to	text	URL being replied to
like_of	text	URL being liked
bookmark_of	text	URL being bookmarked
repost_of	text	URL being reposted
location	text	JSON: {name, latitude, longitude}
rsvp	text	yes | no | maybe | interested
syndicate_to	text	JSON array of target names
author_id	text
published_at	integer	timestamp
created_at	integer	timestamp
updated_at	integer	timestamp

tags and entry_tags

Many-to-many tag relationship. Tags have name and slug columns.

webmentions

Incoming and outgoing webmentions.

Column	Notes
source_url	URL of the page mentioning us
target_url	URL on our site being mentioned
type	like | reply | repost | mention
author_name, author_url, author_photo	parsed from mf2
status	pending | verified | rejected

syndications

Tracks POSSE copies for each entry.

Column	Notes
entry_id	FK to entries
target	bluesky | mastodon | standard.site
target_url	URL of the syndicated copy
at_uri	ATProto URI (Bluesky/standard.site)
status	success | failed

subscribers

Email subscribers for the newsletter.

Column	Notes
email	unique
status	active | unsubscribed
token	HMAC token for unsubscribe links

ap_followers

ActivityPub followers — populated by the inbox when someone follows your actor.

Column	Notes
actor_url	unique; follower’s Actor URL
inbox_url	follower’s inbox for delivery
follow_activity_id	ID of the Follow activity

attachments

Media files attached to entries (images, video). Stores R2 URLs and optional metadata.

Column	Notes
entry_id	FK to entries
url	R2 media URL
content_type	MIME type
display_order	ordering within an entry
width, height	optional dimensions
size	optional file size in bytes
alt	optional alt text

social_links

Social profile links for an author — displayed as icons in the theme.

Column	Notes
author_id	FK to authors
label	display name (e.g. “GitHub”)
url	profile URL
icon	icon identifier
display_order	ordering

logs

Structured request log entries written by the integration logger.

Column	Notes
level	debug | info | warn | error
category	micropub | syndication | webmention | email | admin | system
message	human-readable summary
data	JSON blob with additional context

site_settings

Key/value store for runtime site configuration managed via the admin panel.

Key	Description
debug_mode	"true" enables verbose logging
favicon_url	R2 URL of the site favicon
og_color	hex accent colour for OG images
og_template	OG image layout template name
theme_palette	palette preset name (e.g. "slate", "midnight")

redirects

DB-driven URL redirects managed via /admin/redirects. The middleware checks this table when a route returns 404.

Column	Notes
from_path	unique; normalised path (no trailing slash)
to_path	destination path or full URL
status	301 or 302

Migrations

Migrations are Drizzle-generated SQL files in packages/db/migrations/. Never write migration files by hand — always run pnpm drizzle-kit generate inside packages/db to produce them from schema changes.

In your theme, apply pending migrations with:

# Local D1
pnpm db:migrate

# Remote (production)
pnpm db:migrate:remote

These scripts run wrangler d1 migrations apply DB --local/--remote and apply all unapplied migrations in order.

Using the schema

import { entries, tags, entryTags } from '@indiepub/db/schema';
import { eq } from 'drizzle-orm';

const result = await db.select().from(entries).where(eq(entries.status, 'published'));

Drizzle types are internal to each package — always use the types from @indiepub/types at package boundaries.