m001 was leaving partial state on first boot: CREATE TABLE succeeded
but `CREATE UNIQUE INDEX ... ON tasks.tasks (...)` failed silently in
sqlite (schema prefix on the target table, not the index name, is not
what sqlite expects for ATTACHed databases). Because LNbits never
recorded the migration version, every restart retried m001 and crashed
on `CREATE TABLE tasks already exists`.
Two fixes:
- Use IF NOT EXISTS on every CREATE so a partial run is safe to re-run.
- Schema-prefix the index *name*, not the target table:
`CREATE INDEX tasks.idx ON tbl (...)` instead of `CREATE INDEX idx ON tasks.tbl (...)`.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Schema mirrors the webapp tasks module's ScheduledEvent + EventCompletion
shape:
- tasks.tasks (NIP-52 kind 31922 cache): (pubkey, d_tag) is the
parameterized-replaceable key. JSON-encoded participants / categories
/ recurrence columns are decoded on read via _parse_task_row so each
model can keep clean field validators.
- tasks.completions (kind 31925 cache): unique on
(task_address, pubkey, occurrence). occurrence is NULL for one-shot
tasks; create_completion deletes any prior claim for the same triple
so the latest event wins.
- tasks.settings: singleton row with public_listing toggle.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Empty skeleton mirroring the events extension layout: config/manifest,
pyproject + Makefile + ruff/mypy config, FastAPI routers wired into
tasks_ext, NostrClient bootstrap stubs, and an empty static/routes.json.
Models, migrations, CRUD, Nostr publisher/sync, and the API/template
layers land in follow-up commits.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
