smolsqls-pm

name

smolsqls-pm

description

Track this repo's own work — projects, plans, and tasks — in a live smolsqls database on alpha (dogfooding the product). Use whenever you plan or manage smolsqls work: creating/updating a project, writing or reading a plan (markdown design doc), adding/moving tasks, or answering "what's in progress / what's next / what's the plan for X". This is where plans live for the smolsqls repo (superseding local .plans/ markdown). Triggers on: "add a task", "what's in progress", "track this", "store the plan", "project tracker", "what's next", "mark done".

smolsqls project tracker (in a smolsqls DB)

A simple Linear-style tracker for the smolsqls repo, stored in a smolsqls database on the alpha deployment — we run the project on the product. Queries go through the shared Elixir tool skills/query-db/smolsqls_query.exs (see the query-alpha-db skill for the raw HTTP contract).

Data model

• project — a named initiative/area that groups work over time (e.g. "Tenant SQL hardening"). Coarse, long-lived.
• plan — a markdown design document attached to a project (Problem / Design / Decisions — what a .plans/*.md file used to be). A project can have several over time. This is the thinking.
• task — a discrete, trackable action item with a status, in a project and optionally linked to the plan that spawned it. This is the doing.

project groups → plan describes → tasks execute. Schema in schema.sql.

	statuses
project	active · paused · done · archived
plan	draft · active · done · superseded
task	todo · in_progress · blocked · done · cancelled (priority low/med/high/urgent)

Credentials — from the environment, never committed

The query tool reads a dedicated database's creds from the environment, auto-loading the git-ignored file .claude/smolsqls-pm.env (.claude/ is ignored) if present:

SMOLSQLS_PM_URL       (default https://alpha.smolsqls.com)
SMOLSQLS_PM_DB_ID
SMOLSQLS_PM_DB_TOKEN  (per-database auth_token, Bearer)

If the id/token are missing, stop — provision the database (see Setup) or ask the user; never guess an id or token.

The query tool

skills/query-db/smolsqls_query.exs — a self-contained Elixir CLI (Mix.install pulls Req; no project compile needed). Run from the repo root:

# convenience alias for this shell (or expand it inline in each call)
pmq() { elixir skills/query-db/smolsqls_query.exs --db pm "$@"; }

pmq "SELECT ..."                      # prints an aligned table
pmq "INSERT ... RETURNING id"         # writes; prints rows / num_changes
pmq "SELECT ..." --args '["x", 1]'    # bind ? placeholders (positional)
pmq "INSERT ..." --args-file a.json   # args array from a file (big values)
pmq --file skills/smolsqls-pm/schema.sql   # apply a .sql migration
pmq --json "SELECT ..."               # raw JSON instead of a table

Bind values with ? placeholders + --args — never string-interpolate. One statement per query (the endpoint runs a single statement and rejects transaction control).

Common operations

# What's on my plate — open tasks across active projects, highest priority first
pmq "SELECT p.slug, t.status, t.priority, t.title
       FROM tasks t JOIN projects p ON p.id = t.project_id
      WHERE t.status IN (?, ?) AND p.status = ?
      ORDER BY CASE t.priority WHEN ?4 THEN 0 WHEN ?5 THEN 1 WHEN ?6 THEN 2 ELSE 3 END,
               t.position" \
    --args '["in_progress","todo","active","urgent","high","med"]'

# List projects
pmq "SELECT slug, name, status FROM projects ORDER BY status, name"

# Create a project
pmq "INSERT INTO projects (slug, name, description) VALUES (?, ?, ?) RETURNING id, slug" \
    --args '["tenant-sql-hardening","Tenant SQL hardening","Deny ATTACH/DETACH/VACUUM for tenant SQL."]'

# Attach a plan. Small body: inline via --args. Large body: build the args array
# with the markdown read from a file, then --args-file.
jq -nc --arg p tenant-sql-hardening --arg s 2026-07-06-authorizer \
       --arg t "Authorizer + extension loading" --rawfile b .plans/whatever.md --arg st active \
       '[$p,$s,$t,$b,$st]' > /tmp/plan_args.json
pmq "INSERT INTO plans (project_id, slug, title, body_md, status)
     VALUES ((SELECT id FROM projects WHERE slug = ?), ?, ?, ?, ?)" --args-file /tmp/plan_args.json

# Add a task (optionally linked to a plan)
pmq "INSERT INTO tasks (project_id, plan_id, title, priority)
     VALUES ((SELECT id FROM projects WHERE slug = ?),
             (SELECT id FROM plans   WHERE slug = ?), ?, ?) RETURNING id" \
    --args '["tenant-sql-hardening","2026-07-06-authorizer","Wrap query/describe/sequence","high"]'

# Move a task; marking done stamps completed_at
pmq "UPDATE tasks
        SET status = ?,
            completed_at = CASE WHEN ? = 'done' THEN datetime('now') ELSE completed_at END,
            updated_at = datetime('now')
      WHERE id = ?" --args '["done","done",1]'

# Read a plan's markdown
pmq "SELECT body_md FROM plans WHERE slug = ?" --args '["2026-07-06-authorizer"]'

# Project overview — task counts by status
pmq "SELECT t.status, COUNT(*) AS n FROM tasks t JOIN projects p ON p.id = t.project_id
      WHERE p.slug = ? GROUP BY t.status" --args '["tenant-sql-hardening"]'

RETURNING rows come back in the output. Always bump updated_at on an UPDATE (there are no triggers).

Setup / provisioning

The tracker database is provisioned once. Creating it is a management call (tenant api_key); the database auth_token is returned only at create:

# 1. create the database (returns data.id and data.auth_token — capture both)
curl -sS -X POST "${SMOLSQLS_PM_URL:-https://alpha.smolsqls.com}/v1/databases" \
  -H "authorization: Bearer $SMOLSQLS_ALPHA_API_KEY" \
  -H 'content-type: application/json' \
  -d '{"name": "smolsqls-pm"}'

# 2. store creds in the git-ignored env file (auto-loaded by the tool; never commit)
#    printf 'export SMOLSQLS_PM_DB_ID=%s\nexport SMOLSQLS_PM_DB_TOKEN=%s\n' "$ID" "$TOKEN" > .claude/smolsqls-pm.env

# 3. apply the schema (splits on ';' and posts each statement)
elixir skills/query-db/smolsqls_query.exs --db pm --file skills/smolsqls-pm/schema.sql

# 4. verify
pmq "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"

Re-applying is safe (CREATE TABLE/INDEX IF NOT EXISTS).

Notes

• This supersedes .plans/ for the smolsqls repo. When planning work here, create/attach a plan row and tasks instead of a local markdown file. (The repo's global convention still mentions .plans/; the human may update it.)
• Backups: this is real data on alpha — it inherits alpha's backup/replication. For a point-in-time copy, use the management backups endpoint, not tenant SQL (VACUUM is denied for tenants).