posts, drafts, and a comment tree that took longer than the posts did

a service that only knows about posts

Same split as auth: blog is its own Go service, doesn't know what a password is, doesn't know what a session is. It gets a request with an already-verified JWT attached (blog checks the signature itself against auth's published keys, no network call back to auth needed) and it deals with posts, comments, likes, and reports. Everything in this post lives in a Postgres database called blog_db, entirely separate from the auth_db the accounts live in.

slugs, and not trusting the title to make one

Every post needs a slug for the URL, and my first idea was just lowercase the title, swap spaces for hyphens, done:

// first idea: no collision handling at all
func slugFromTitle(title string) string {
	return strings.ReplaceAll(strings.ToLower(title), " ", "-")
}

Except titles collide. Two posts with a similar enough title would both want the same slug, and the second one would either fail outright or silently overwrite something. So slug generation checks the database and appends a number until it finds one that's free:

func (s *Server) uniqueSlugFromTitle(ctx context.Context, title string) (string, error) {
	base := slug.Generate(title)
	if base == "" {
		base = "post"
	}

	candidate := base
	for n := 2; ; n++ {
		exists, err := s.Queries.SlugExists(ctx, candidate)
		if err != nil {
			return "", err
		}
		if !exists {
			return candidate, nil
		}
		candidate = fmt.Sprintf("%s-%d", base, n)
	}
}

You can also just hand it your own slug on create if you want, which I use sometimes when the generated one comes out uglier than I'd like.

draft, published, archived

A post is one of three states in the database: draft, published, or archived. What I didn't expect going in is how much logic hangs off that one column. You can only archive something that was actually published (archiving a draft would produce a post reachable at its own URL with no published date at all, which makes no sense), and going back to draft mode clears any pending publish schedule so it can't sneak out later when I wasn't looking.

The scheduling part is the bit I'm proudest of. You can set a post to draft with a publish_at timestamp in the future, and a background sweep flips it to published once that time actually passes:

// D2 sweep statement 1: flips a draft to published once its schedule has
// passed, stamping published_at with the scheduled time (not now()) and
// clearing publish_at so it never fires again.
func (q *Queries) PublishScheduledPosts(ctx context.Context) error {
	_, err := q.db.Exec(ctx, publishScheduledPosts)
	return err
}

UPDATE posts SET status = 'published', published_at = publish_at, publish_at = NULL
WHERE status = 'draft' AND publish_at <= now()

That statement runs every 30 seconds from a goroutine that just ticks until the process shuts down. It's completely idempotent, a row that no longer matches the WHERE clause simply isn't touched, so it's safe to call as often as you want, from anywhere, even a test. This whole devlog series, actually, went out through exactly this mechanism: written and backdated ahead of time, left for the sweep to pick up whenever it got around to it.

comments are a tree pretending to be a table

Comments needed to support replies, so a comment can point at a parent comment. Postgres doesn't need anything exotic for that, just a nullable self-reference:

CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    author_id UUID NOT NULL,
    author_username TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    edited_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ
);

The API itself doesn't return a nested tree though, it returns a flat list ordered by creation time, each row carrying its own parent_comment_id:

comments, err := s.Queries.GetCommentsForPost(ctx, post.ID)

The frontend is the one that turns that flat list into the nested thread you actually see. Felt slightly wrong at first, like I was skipping a step somewhere, but it's actually the more flexible shape: sorting, pagination, and moderation all stay simple queries against one flat table, and building a tree out of a flat list with parent pointers is a completely mechanical thing to do once you already have the data in hand.

Deleting a comment doesn't remove the row either. It sets deleted_at and the API blanks the content, but keeps the row alive, because otherwise deleting a comment with three replies underneath it would either cascade the whole conversation away or leave orphaned replies pointing at nothing. A "[deleted]" placeholder holding its spot in the thread is a lot less confusing than either of those.

likes and reports, kept deliberately simple

Likes are a straightforward join table, one row per user per comment, count is a group-by. Reports were the one place I had to think about abuse a little: nothing stops someone from reporting the same comment five times, except a unique index scoped to only apply while a report is still open:

CREATE UNIQUE INDEX reports_open_unique_idx ON reports (comment_id, reporter_id) WHERE resolved_at IS NULL;

A partial unique index. One open report per person per comment, but once a moderator resolves it, that same person can report it again if it genuinely happens again later. Postgres doing that in a single line still feels like a small magic trick to me.

migrations, or getting less scared of altering a table

Every one of these tables came out of its own numbered migration, run through goose:

-- +goose Up
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    content TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED
);

-- +goose Down
DROP TABLE posts;

Before this project the closest I'd come to a real migration was Django's manage.py migrate, which does an enormous amount of the thinking for you. Writing the up and the down myself, by hand, for every single change, made me actually read what I was doing to the schema instead of trusting a tool to figure it out quietly in the background. That search_vector column is a generated column, Postgres builds it automatically from title and content every time a row changes, and it's what full text search on the blog actually runs against. Didn't know that feature existed until I needed it.