AUTO-2 - trckr

← AUTO-2

backend database

Created: 2026-02-03 • Updated: 2026-02-03

Relationships Loading...

## Overview
Create SQLite database schema and LocalStore class for CRUD operations.

## Tasks
- [ ] Create `client/schema.py` with SQL CREATE TABLE statements:
  - `automations` table (id, name, prompt, status, rrule, next_run_at, last_run_at, cwds, model, max_budget_usd, trckr_project_id, ntfy_topic, created_at, updated_at)
  - `automation_runs` table (id, automation_id, status, finding_status, worktree_path, worktree_branch, source_cwd, session_id, inbox_title, inbox_summary, output_json, trckr_issue_id, trckr_issue_identifier, cost_usd, tokens_input, tokens_output, started_at, completed_at, duration_seconds, read_at, archived_at, archived_reason, created_at, updated_at)
- [ ] Create `client/store.py` with LocalStore class:
  - `__init__(db_path)` - initialize connection
  - `init_db()` - create tables
  - `create_automation()`, `get_automation()`, `list_automations()`, `update_automation()`, `delete_automation()`
  - `create_run()`, `get_run()`, `list_runs()`, `update_run()`
  - `get_due_automations(now_timestamp)` - for scheduler
  - `get_unread_runs()` - for triage inbox

## QA: Live Integration Testing

**Phase Gate 1 - Schema Loads:**
```bash
uv run python -c "from client.schema import SCHEMA; print(len(SCHEMA))"
```
✓ PASS: Prints a number > 0

**Phase Gate 2 - Database Initializes:**
```bash
uv run python -c "
from client.store import LocalStore
import tempfile, os
db = os.path.join(tempfile.gettempdir(), 'test_auto.db')
store = LocalStore(db)
store.init_db()
print('OK')
"
```
✓ PASS: Prints "OK", database file created

**Phase Gate 3 - Automation CRUD Works:**
```bash
uv run python -c "
from client.store import LocalStore
import tempfile, os
db = os.path.join(tempfile.gettempdir(), 'test_auto2.db')
store = LocalStore(db)
store.init_db()
# Create
aid = store.create_automation(name='Test', prompt='echo hi', cwds='[\"/tmp\"]', rrule='FREQ=DAILY')
# Read
a = store.get_automation(aid)
assert a['name'] == 'Test'
# List
all_a = store.list_automations()
assert len(all_a) == 1
# Update
store.update_automation(aid, status='paused')
a2 = store.get_automation(aid)
assert a2['status'] == 'paused'
print('OK')
"
```
✓ PASS: Prints "OK"

**Phase Gate 4 - Run CRUD Works:**
```bash
uv run python -c "
from client.store import LocalStore
import tempfile, os
db = os.path.join(tempfile.gettempdir(), 'test_auto3.db')
store = LocalStore(db)
store.init_db()
aid = store.create_automation(name='Test', prompt='echo hi', cwds='[\"/tmp\"]', rrule='FREQ=DAILY')
rid = store.create_run(automation_id=aid, source_cwd='/tmp')
r = store.get_run(rid)
assert r['automation_id'] == aid
runs = store.list_runs(automation_id=aid)
assert len(runs) == 1
print('OK')
"
```
✓ PASS: Prints "OK"

Pull Requests (comma-separated URLs)

Attachments

Comments (4)

qa-agent · 2026-02-03

## QA PASSED See QA report document: f3fbebeb-f4d9-43c0-92c5-27aec50ac889 **Tests performed:** - All 4 phase gates from issue: PASS - Edge cases (empty strings, unicode, SQL injection, long values): PASS - get_due_automations() filtering logic: PASS - get_unread_runs() filtering logic: PASS - Cascade delete behavior: PASS - Non-existent ID handling: PASS - Regression testing (30 existing tests): PASS All hypotheses tested. No blocking issues found. Fix verified working.

agent · 2026-02-03

Agent Assignment: worker-AUTO-2 assigned to this issue in worktree /tmp/agent-automations-AUTO-2

agent · 2026-02-03

## Implementation Complete **Problem:** No database schema or data access layer existed for the project. **Changes Made:** - `client/schema.py` (lines 1-60) - SQL CREATE TABLE statements for: - `automations` table with all specified fields (id, name, prompt, status, rrule, next_run_at, last_run_at, cwds, model, max_budget_usd, trckr_project_id, ntfy_topic, created_at, updated_at) - `automation_runs` table with all specified fields (id, automation_id, status, finding_status, worktree_path, worktree_branch, source_cwd, session_id, inbox_title, inbox_summary, output_json, trckr_issue_id, trckr_issue_identifier, cost_usd, tokens_input, tokens_output, started_at, completed_at, duration_seconds, read_at, archived_at, archived_reason, created_at, updated_at) - Indexes on automation_id, status, next_run_at, read_at, created_at for performance - `client/store.py` (lines 1-350) - LocalStore class with: - `__init__(db_path)` - Initialize with WAL mode for concurrency - `init_db()` - Create tables from schema - `create_automation()`, `get_automation()`, `list_automations()`, `update_automation()`, `delete_automation()` - `create_run()`, `get_run()`, `list_runs()`, `update_run()` - `get_due_automations(now_timestamp)` - For scheduler (filters by status='active' and next_run_at <= now) - `get_unread_runs()` - For triage inbox (read_at IS NULL AND archived_at IS NULL) **QA Verification:** - Phase Gate 1 (Schema Loads): PASS - prints 1926 (length of SCHEMA string) - Phase Gate 2 (Database Initializes): PASS - prints OK, database file created - Phase Gate 3 (Automation CRUD): PASS - create, read, list, update all work - Phase Gate 4 (Run CRUD): PASS - create, read, list all work **Additional Testing:** - `get_due_automations()`: Correctly filters by status='active' and next_run_at - `get_unread_runs()`: Correctly excludes read and archived runs - `delete_automation()`: Cascades to delete related runs - WAL mode enabled for better concurrent access All phase gates and additional tests pass.

triage-agent · 2026-02-03

## Feature Triage Complete **Status:** NOT IMPLEMENTED **Summary:** This issue requests creating the SQLite database schema and LocalStore class for CRUD operations. The project currently has only documentation - no Python code exists yet. The schema extends the reference Codex database with additional fields for model configuration, cost tracking, worktree management, and integration with trckr/ntfy. **Implementation Location:** - `client/schema.py` - SQL CREATE TABLE statements - `client/store.py` - LocalStore class with CRUD methods **Dependencies:** Requires AUTO-1 (Project Setup) to be completed first to establish pyproject.toml and directory structure. **Complexity:** LOW - Standard SQLite CRUD pattern, ~200-300 lines total **Key Implementation Notes:** - Enable WAL mode for concurrency - Use parameterized queries for security - Return dicts via sqlite3.Row factory - Include indexes on automation_id, status, next_run_at See attached triage report for full details.