watched_dirs, triggers & uploaded_files Migrations & sqlc #65

New issue

Open

opened 2026-02-23 10:03:03 +00:00 by ottomata · 0 comments

ottomata commented

2026-02-23 10:03:03 +00:00

Owner

Tasks

Migration 000006_create_file_watch.up.sql:

CREATE TABLE watched_dirs (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    path       TEXT NOT NULL UNIQUE,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE triggers (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id       UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    watched_dir  UUID NOT NULL REFERENCES watched_dirs(id) ON DELETE CASCADE,
    glob_pattern TEXT NOT NULL,
    enabled      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE uploaded_files (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    filename    TEXT NOT NULL,
    path        TEXT NOT NULL,
    detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    trigger_id  UUID REFERENCES triggers(id),
    run_id      UUID REFERENCES runs(id)
);
CREATE INDEX uploaded_files_trigger_idx ON uploaded_files(trigger_id);
CREATE INDEX uploaded_files_run_idx ON uploaded_files(run_id);

sqlc queries: Full CRUD for all 3 tables + ListTriggersByDir :many, ListFilesByTrigger :many

Acceptance Criteria

All 3 tables created after make migrate-up
All sqlc queries generated and compile
Clean rollback

### Tasks **Migration** `000006_create_file_watch.up.sql`: ```sql CREATE TABLE watched_dirs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), path TEXT NOT NULL UNIQUE, created_by UUID NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE triggers ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, watched_dir UUID NOT NULL REFERENCES watched_dirs(id) ON DELETE CASCADE, glob_pattern TEXT NOT NULL, enabled BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE uploaded_files ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), filename TEXT NOT NULL, path TEXT NOT NULL, detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), trigger_id UUID REFERENCES triggers(id), run_id UUID REFERENCES runs(id) ); CREATE INDEX uploaded_files_trigger_idx ON uploaded_files(trigger_id); CREATE INDEX uploaded_files_run_idx ON uploaded_files(run_id); ``` **sqlc queries**: Full CRUD for all 3 tables + `ListTriggersByDir :many`, `ListFilesByTrigger :many` ### Acceptance Criteria - [ ] All 3 tables created after `make migrate-up` - [ ] All sqlc queries generated and compile - [ ] Clean rollback

ottomata added this to the Phase 7 project

2026-02-23 10:09:05 +00:00

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

ottomata/acsm#65

No description provided.

Rows
Columns