pages.laporte.cloud · twit-ads

TWiT-Ads v2 Rebuild

A1 — Scaffold Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: A running, tested skeleton of TWiT-Ads v2 on the Framework testbed: git repo, Go service with fail-all config, Postgres via docker-compose, embedded goose migrations, /healthz, justfile workflow, and a nightly backup timer.

Architecture: Single Go binary (stdlib net/http) + Postgres 17 in docker-compose. Config from YAML with env-var overrides, validated fail-all at boot. Migrations embedded in the binary and applied idempotently at startup. No domain logic in this stage — A1 is pure plumbing that every later stage builds on.

Tech Stack: Go 1.24+, pgx/v5 (+ stdlib adapter for goose), goose/v3, gopkg.in/yaml.v3, docker-compose (postgres:17-alpine), just, systemd user units.

Global Constraints (from ~/Projects/twit-ads/CLAUDE.md — read it first)

Ports (Framework): app 8730 (0.0.0.0 — tailnet-reachable), postgres host-published on 127.0.0.1:5445 only. 8080/8082/8199/8888/8002 are taken by other services — do not use.

Task 1: Git repository

Files:

Interfaces:

# legacy extracts — reference only, preserved outside git
site_extract/
AIGEN_sql_format_helper/
*.zip

# local dev
app/config.yaml
*.sql.gz

cd ~/Projects/twit-ads
git init -b main
git add .gitignore CLAUDE.md Plans/ reference/checklists.md
git commit -m "chore: repo scaffold — conventions, stage plans, continuity checklists"

Expected: commit created; git status shows extracts untracked-ignored. (The xlsx files in reference/ may be added too — they're small; include them.)

Task 2: Go module, layout, justfile

Files:

Interfaces:

cd ~/Projects/twit-ads/app
go mod init twit.tv/twitads
go get github.com/jackc/pgx/v5@latest gopkg.in/yaml.v3@latest github.com/pressly/goose/v3@latest

Expected: go.mod lists the three deps (check the 14-day rule against each version's release date; pin back if needed).

set shell := ["bash", "-cu"]

# unit tests (no DB needed)
test:
    go test ./...

# integration tests against the compose postgres (creates twitads_test db)
test-db: up
    docker compose exec -T postgres sh -c 'psql -U twitads -tc "SELECT 1 FROM pg_database WHERE datname='"'"'twitads_test'"'"'" | grep -q 1 || createdb -U twitads twitads_test'
    TEST_DATABASE_URL="postgres://twitads:twitads@127.0.0.1:5445/twitads_test" go test ./internal/db -v

run:
    go run ./cmd/twitads -config config.yaml

up:
    docker compose up -d --wait postgres

down:
    docker compose down

psql:
    docker compose exec postgres psql -U twitads twitads

# copy to config.yaml for local dev (config.yaml is gitignored)
env: dev            # dev | test | prod
listen: "0.0.0.0:8730"
database_url: "postgres://twitads:twitads@127.0.0.1:5445/twitads"  # dev-only creds

cd ~/Projects/twit-ads
git add app/go.mod app/go.sum app/justfile app/config.example.yaml
git commit -m "feat(app): go module, justfile, example config"

Task 3: Config loader (fail-all validation)

Files:

Interfaces:

package config

import (
	"os"
	"path/filepath"
	"strings"
	"testing"
)

func writeTemp(t *testing.T, body string) string {
	t.Helper()
	p := filepath.Join(t.TempDir(), "config.yaml")
	if err := os.WriteFile(p, []byte(body), 0o644); err != nil {
		t.Fatal(err)
	}
	return p
}

func TestLoadValid(t *testing.T) {
	p := writeTemp(t, "env: dev\nlisten: \"127.0.0.1:8730\"\ndatabase_url: \"postgres://u:p@localhost:5445/db\"\n")
	cfg, err := Load(p)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if cfg.Env != "dev" || cfg.Listen != "127.0.0.1:8730" {
		t.Errorf("bad fields: %+v", cfg)
	}
}

func TestValidationCollectsAllErrors(t *testing.T) {
	p := writeTemp(t, "env: staging\nlisten: \"\"\ndatabase_url: \"not-a-url\"\n")
	_, err := Load(p)
	if err == nil {
		t.Fatal("expected error")
	}
	for _, want := range []string{"env", "listen", "database_url"} {
		if !strings.Contains(err.Error(), want) {
			t.Errorf("error should mention %q, got: %v", want, err)
		}
	}
}

func TestEnvOverrides(t *testing.T) {
	p := writeTemp(t, "env: dev\nlisten: \"127.0.0.1:8730\"\ndatabase_url: \"postgres://u:p@localhost/db\"\n")
	t.Setenv("DATABASE_URL", "postgres://x:y@otherhost:5432/other")
	t.Setenv("TWITADS_LISTEN", "0.0.0.0:9999")
	cfg, err := Load(p)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if cfg.DatabaseURL != "postgres://x:y@otherhost:5432/other" || cfg.Listen != "0.0.0.0:9999" {
		t.Errorf("env override not applied: %+v", cfg)
	}
}

func TestMissingFileErrors(t *testing.T) {
	if _, err := Load("/nonexistent/config.yaml"); err == nil {
		t.Fatal("expected error for missing file")
	}
}

Run: cd ~/Projects/twit-ads/app && go test ./internal/config/ -v Expected: FAIL — undefined: Load.

// Package config loads and validates the service configuration.
// Validation is fail-all: every problem is reported at once.
package config

import (
	"errors"
	"fmt"
	"net"
	"os"

	"github.com/jackc/pgx/v5"
	"gopkg.in/yaml.v3"
)

type Config struct {
	Env         string `yaml:"env"`
	Listen      string `yaml:"listen"`
	DatabaseURL string `yaml:"database_url"`
}

func Load(path string) (*Config, error) {
	raw, err := os.ReadFile(path)
	if err != nil {
		return nil, fmt.Errorf("read config: %w", err)
	}
	var cfg Config
	if err := yaml.Unmarshal(raw, &cfg); err != nil {
		return nil, fmt.Errorf("parse config: %w", err)
	}
	if v := os.Getenv("TWITADS_ENV"); v != "" {
		cfg.Env = v
	}
	if v := os.Getenv("TWITADS_LISTEN"); v != "" {
		cfg.Listen = v
	}
	if v := os.Getenv("DATABASE_URL"); v != "" {
		cfg.DatabaseURL = v
	}
	if err := cfg.validate(); err != nil {
		return nil, err
	}
	return &cfg, nil
}

func (c *Config) validate() error {
	var errs []error
	switch c.Env {
	case "dev", "test", "prod":
	default:
		errs = append(errs, fmt.Errorf("env: must be dev, test, or prod (got %q)", c.Env))
	}
	if c.Listen == "" {
		errs = append(errs, errors.New("listen: required"))
	} else if _, _, err := net.SplitHostPort(c.Listen); err != nil {
		errs = append(errs, fmt.Errorf("listen: %w", err))
	}
	if c.DatabaseURL == "" {
		errs = append(errs, errors.New("database_url: required"))
	} else if _, err := pgx.ParseConfig(c.DatabaseURL); err != nil {
		errs = append(errs, fmt.Errorf("database_url: %w", err))
	}
	return errors.Join(errs...)
}

Run: go test ./internal/config/ -v Expected: all 4 PASS.

cd ~/Projects/twit-ads
git add app/internal/config/
git commit -m "feat(config): YAML+env config loader with fail-all validation"

Task 4: DB package — pool, migrations

Files:

Interfaces:

-- +goose Up
CREATE TABLE system_info (
    key        text PRIMARY KEY,
    value      text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO system_info (key, value) VALUES ('schema_bootstrap', 'a1');

-- +goose Down
DROP TABLE system_info;

package db

import (
	"context"
	"os"
	"testing"
	"time"
)

func testURL(t *testing.T) string {
	t.Helper()
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set; run via `just test-db`")
	}
	return url
}

func TestMigrateAndConnect(t *testing.T) {
	url := testURL(t)
	if err := Migrate(url); err != nil {
		t.Fatalf("migrate: %v", err)
	}
	// idempotent: second run is a no-op, not an error
	if err := Migrate(url); err != nil {
		t.Fatalf("second migrate: %v", err)
	}
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	pool, err := Connect(ctx, url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()
	var v string
	if err := pool.QueryRow(ctx, "SELECT value FROM system_info WHERE key='schema_bootstrap'").Scan(&v); err != nil {
		t.Fatalf("query: %v", err)
	}
	if v != "a1" {
		t.Errorf("got %q, want a1", v)
	}
}

Run: just up && just test-db (from app/) Expected: FAIL — undefined: Migrate / undefined: Connect. (Compose postgres from Task 6 isn't written yet — if just up fails because docker-compose.yml doesn't exist, run this test after Task 6; verify compile failure now with go vet ./internal/db/.)

// Package db owns the connection pool and embedded schema migrations.
package db

import (
	"context"
	"database/sql"
	"embed"
	"fmt"

	"github.com/jackc/pgx/v5/pgxpool"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

func Connect(ctx context.Context, url string) (*pgxpool.Pool, error) {
	pool, err := pgxpool.New(ctx, url)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping: %w", err)
	}
	return pool, nil
}

// Migrate applies all pending migrations. Safe to run at every startup.
func Migrate(url string) error {
	sqldb, err := sql.Open("pgx", url)
	if err != nil {
		return fmt.Errorf("open for migrate: %w", err)
	}
	defer sqldb.Close()
	goose.SetBaseFS(migrationsFS)
	if err := goose.SetDialect("postgres"); err != nil {
		return err
	}
	if err := goose.Up(sqldb, "migrations"); err != nil {
		return fmt.Errorf("goose up: %w", err)
	}
	return nil
}

Run go get github.com/jackc/pgx/v5/stdlib is not needed (same module); go mod tidy after adding imports.

Run: go vet ./... && go test ./... Expected: PASS (db test skips without TEST_DATABASE_URL).

cd ~/Projects/twit-ads
git add app/internal/db/ app/go.mod app/go.sum
git commit -m "feat(db): pgx pool + embedded goose migrations with bootstrap table"

Task 5: HTTP server + /healthz

Files:

Interfaces:

package server

import (
	"context"
	"errors"
	"net/http"
	"net/http/httptest"
	"strings"
	"testing"
)

type fakePinger struct{ err error }

func (f fakePinger) Ping(ctx context.Context) error { return f.err }

func TestHealthzOK(t *testing.T) {
	h := New(fakePinger{})
	rec := httptest.NewRecorder()
	h.ServeHTTP(rec, httptest.NewRequest(http.MethodGet, "/healthz", nil))
	if rec.Code != http.StatusOK {
		t.Fatalf("status = %d, want 200", rec.Code)
	}
	if !strings.Contains(rec.Body.String(), `"ok"`) {
		t.Errorf("body = %s", rec.Body.String())
	}
}

func TestHealthzDBDown(t *testing.T) {
	h := New(fakePinger{err: errors.New("boom")})
	rec := httptest.NewRecorder()
	h.ServeHTTP(rec, httptest.NewRequest(http.MethodGet, "/healthz", nil))
	if rec.Code != http.StatusServiceUnavailable {
		t.Fatalf("status = %d, want 503", rec.Code)
	}
}

Run: go test ./internal/server/ -v Expected: FAIL — undefined: New.

// Package server assembles the HTTP handler tree.
package server

import (
	"context"
	"encoding/json"
	"net/http"
	"time"
)

type Pinger interface {
	Ping(ctx context.Context) error
}

func New(db Pinger) http.Handler {
	mux := http.NewServeMux()
	mux.HandleFunc("GET /healthz", func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
		defer cancel()
		w.Header().Set("Content-Type", "application/json")
		if err := db.Ping(ctx); err != nil {
			w.WriteHeader(http.StatusServiceUnavailable)
			json.NewEncoder(w).Encode(map[string]string{"status": "degraded", "db": "unreachable"})
			return
		}
		json.NewEncoder(w).Encode(map[string]string{"status": "ok"})
	})
	return mux
}

Run: go test ./internal/server/ -v Expected: both PASS.

cd ~/Projects/twit-ads
git add app/internal/server/
git commit -m "feat(server): handler tree with /healthz (db-aware)"

Task 6: main.go, Dockerfile, docker-compose

Files:

Interfaces:

package main

import (
	"context"
	"errors"
	"flag"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"twit.tv/twitads/internal/config"
	"twit.tv/twitads/internal/db"
	"twit.tv/twitads/internal/server"
)

func main() {
	configPath := flag.String("config", "config.yaml", "path to config file")
	flag.Parse()
	log := slog.New(slog.NewTextHandler(os.Stderr, nil))

	cfg, err := config.Load(*configPath)
	if err != nil {
		log.Error("config", "err", err)
		os.Exit(1)
	}
	if err := db.Migrate(cfg.DatabaseURL); err != nil {
		log.Error("migrate", "err", err)
		os.Exit(1)
	}
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	pool, err := db.Connect(ctx, cfg.DatabaseURL)
	cancel()
	if err != nil {
		log.Error("db connect", "err", err)
		os.Exit(1)
	}
	defer pool.Close()

	srv := &http.Server{Addr: cfg.Listen, Handler: server.New(pool)}
	go func() {
		log.Info("listening", "addr", cfg.Listen, "env", cfg.Env)
		if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
			log.Error("serve", "err", err)
			os.Exit(1)
		}
	}()

	stop := make(chan os.Signal, 1)
	signal.Notify(stop, syscall.SIGINT, syscall.SIGTERM)
	<-stop
	log.Info("shutting down")
	shutdownCtx, cancel2 := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel2()
	_ = srv.Shutdown(shutdownCtx)
}

FROM golang:1.24-alpine AS build
WORKDIR /src
COPY go.mod go.sum ./
RUN go mod download
COPY . .
RUN CGO_ENABLED=0 go build -o /twitads ./cmd/twitads

FROM alpine:3.21
# tzdata: civil dates are America/Los_Angeles
RUN apk add --no-cache ca-certificates tzdata
COPY --from=build /twitads /twitads
ENTRYPOINT ["/twitads"]

services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_USER: twitads
      POSTGRES_PASSWORD: twitads   # dev-only; production is RDS via DATABASE_URL
      POSTGRES_DB: twitads
    ports:
      - "127.0.0.1:5445:5432"      # host-local only, for psql/tests
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U twitads"]
      interval: 5s
      timeout: 3s
      retries: 10

  app:
    build: .
    depends_on:
      postgres:
        condition: service_healthy
    ports:
      - "8730:8730"                # tailnet-reachable
    environment:
      DATABASE_URL: "postgres://twitads:twitads@postgres:5432/twitads"
      TWITADS_ENV: dev
      TWITADS_LISTEN: "0.0.0.0:8730"
    volumes:
      - ./config.example.yaml:/etc/twitads/config.yaml:ro   # env vars override everything that matters
    command: ["-config", "/etc/twitads/config.yaml"]

volumes:
  pgdata:

cd ~/Projects/twit-ads/app
cp config.example.yaml config.yaml
docker compose up -d --build --wait
curl -s http://127.0.0.1:8730/healthz

Expected: {"status":"ok"}. Then stop the DB and confirm degradation:

docker compose stop postgres
curl -s -o /dev/null -w "%{http_code}\n" http://127.0.0.1:8730/healthz   # expect 503
docker compose start postgres

just test-db

Expected: TestMigrateAndConnect PASS (twice-applied migration is a no-op).

cd ~/Projects/twit-ads
git add app/cmd/ app/Dockerfile app/docker-compose.yml
git commit -m "feat(app): main wiring, Dockerfile, compose stack; healthz green end-to-end"

Task 7: Nightly backup timer

Files:

Interfaces:

[Unit]
Description=TWiT-Ads nightly pg_dump
Requires=docker.service

[Service]
Type=oneshot
Environment=BACKUP_DIR=%h/Backups/twit-ads
Environment=COMPOSE=%h/Projects/twit-ads/app/docker-compose.yml
ExecStartPre=/usr/bin/mkdir -p ${BACKUP_DIR}
ExecStart=/bin/sh -c 'docker compose -f ${COMPOSE} exec -T postgres pg_dump -U twitads twitads | gzip > ${BACKUP_DIR}/twitads-$(date +%%F).sql.gz'
ExecStartPost=/bin/sh -c 'find ${BACKUP_DIR} -name "twitads-*.sql.gz" -mtime +30 -delete'

[Unit]
Description=Nightly TWiT-Ads database backup

[Timer]
OnCalendar=*-*-* 02:30
Persistent=true

[Install]
WantedBy=timers.target

mkdir -p ~/.config/systemd/user
cp ~/Projects/twit-ads/app/systemd/twitads-backup.{service,timer} ~/.config/systemd/user/
systemctl --user daemon-reload
systemctl --user enable --now twitads-backup.timer
systemctl --user start twitads-backup.service
ls -la ~/Backups/twit-ads/
zcat ~/Backups/twit-ads/twitads-$(date +%F).sql.gz | head -20

Expected: one twitads-YYYY-MM-DD.sql.gz containing CREATE TABLE public.system_info. systemctl --user list-timers | grep twitads shows the 02:30 schedule.

cd ~/Projects/twit-ads
git add app/systemd/
git commit -m "feat(ops): nightly pg_dump timer with 30-day retention"

Task 8: Stage close-out

Files:

cd ~/Projects/twit-ads/app
go vet ./... && just test && just test-db
docker compose up -d --build --wait && curl -s http://127.0.0.1:8730/healthz

Expected: everything green, {"status":"ok"}.

In Plans/README.md change the A1 row to: | A1 Scaffold | A1-scaffold.md | executed — awaiting Leo verification |

cd ~/Projects/twit-ads
git add Plans/
git commit -m "chore(a1): stage complete, status board updated"

Decisions & deviations (executed 2026-07-06 by Kenobi/Fable 5)