database-toolset-general

When to use this skill

Use this skill whenever you’re about to do real work with MiChat’s database toolset (create/inspect/migrate/query/update/backup).

Default stance: one DB per profile (multiple tables). Create a second DB only for scratch/import/staging workflows or a clear separation reason.

Practical workflow (the safe default)

1. Discover / pick DB

• If unsure what exists, list DBs.
• Otherwise default to a single main DB name (e.g. personal).

2. Ensure it exists

• If a DB might not exist yet, ensure it with create enabled.

3. Inspect before changing

• Use schema inspection to understand the current tables/columns.

4. Back up before risky changes

• Before DDL or destructive edits, take a backup with a short label.

5. Migrate/seed in a batch

• Prefer batched execution in a transaction for migrations and small seed data.
• Keep batches short and readable.

6. Verify

• Re-check schema and run small verification SELECTs.

Tips and gotchas

1) SELECT vs non-SELECT routing

• SELECT/CTE belongs in the query tool; using the execute tool for SELECT will be rejected.

2) Paging and hard caps

• Query results are paged with an offset:<n> cursor.
• Even if you request a huge max_rows, results are hard-capped (practically: 200 rows/page). Use next_cursor to continue.
• For stable paging, include an ORDER BY.

3) db_execute_many failure semantics (choose intentionally)

• Atomic default: transaction=true, stop_on_error=true.
  • If any statement fails, you get an error envelope only (no partial results list).
  • Error details include failed_index, sql_preview, and sqlite error codes/names.
• If you need partial progress / diagnostics:
  • use transaction=false.
  • stop_on_error=true stops at first error and returns results up to the failure.
  • stop_on_error=false continues and returns per-statement errors; overall ok=false if any failed.
• Rule: when transaction=true, stop_on_error must be true.

4) RETURNING is for small results

• Statements with RETURNING cap returned rows (e.g. 50 per statement in batched execution).
• If you need lots of rows back, prefer:
  1. write changes without relying on a large RETURNING payload, then
  2. fetch what you need with a paged SELECT.

5) Debugging a failed batch quickly

• Use the batch error’s failed_index + sql_preview to pinpoint the failing statement.
• If the failure looks like a schema mismatch, re-run schema inspection (full detail if available).

Recommended conventions (lightweight)

• Use params (positional/named) instead of interpolating values into SQL.
• Prefer idempotent DDL where possible (e.g., IF NOT EXISTS).
• Use consistent timestamps (e.g., datetime('now')).
• Start with minimal indexes; add them when queries become slow or frequent.

Optional: scratch/import staging DB (second DB)

Use a second DB only when it makes your workflow simpler:

• messy imports
• dedup/normalization staging
• experimental schema work Then copy cleaned data into the main DB and discard/ignore the scratch DB.

Troubleshooting checklist

• not found: ensure DB exists (create enabled)
• paging confusion: add ORDER BY; follow next_cursor
• batch failure: check failed_index and the sqlite error; consider rerun with transaction=false for more visibility