syntaxis/docs/howto/database_sqlite_info.md

Workspace Database Creation & Migrations - Complete Overview

  Current System Architecture

  Your project uses two complementary approaches for database management:

  1. syntaxis-core (Embedded Migrations)

  - Location: core/crates/syntaxis-core/migrations/
  - Migration file: 20250101000001_init.sql
  - Approach: Embedded SQL schema (run programmatically)
  - Automatic: ✅ Runs on every PersistenceLayer::new() call

  2. syntaxis-api (SQLx Migrations)

  - Location: core/crates/syntaxis-api/migrations/
  - Migration files:
    - 20250113_001_initial_schema.sql
    - 20250114_enhanced_features.sql
    - 20250115_003_revoked_tokens.sql
  - Approach: Traditional SQLx migration files (not automatically run)
  - Automatic: ⚠️ Requires manual setup (see below)

  How Database Initialization Works

  syntaxis-core (Automatic)

  Code in syntaxis-core/src/persistence.rs:85-200:

  /// Run database migrations (called automatically)
  async fn run_migrations(pool: &SqlitePool) -> Result<()> {
      // 1. Enable foreign keys
      sqlx::query("PRAGMA foreign_keys = ON")
          .execute(pool)
          .await?;

      // 2. Create tables (IF NOT EXISTS - safe to run multiple times)
      sqlx::query("CREATE TABLE IF NOT EXISTS projects (...)")
          .execute(pool)
          .await?;

      // ... more tables ...
  }

  When it runs:
  - Automatically when you call PersistenceLayer::new(db_path) ✅
  - Also when you use PersistenceLayer::global(tool_name) ✅
  - Also when you use PersistenceLayer::new_memory() (for tests) ✅

  Tables created:
  1. projects - Main project metadata
  2. checklist_items - Tasks and phases
  3. phase_transitions - Audit trail of phase changes
  4. security_assessments - Security scan results
  5. tool_configurations - Tool settings per project
  6. activity_logs - Change log
  7. backup_history - Backup records

  syntaxis-api (Manual/Future)

  The migration files exist but aren't automatically run. This is intentional - syntaxis-api is designed to be optional and use
  syntaxis-core's schema.

  Database Locations

  After installation, databases are stored in:

  ~/.local/share/core/
  ├── workspace.db          # Main database (created by workspace CLI/TUI)
  ├── api-database.db       # API database (if running syntaxis-api)
  └── ... (other data files)

  Configuration for paths:
  - Set in wrapper script by install-cli.nu
  - Env var: WORKSPACE_DATA_DIR=~/.local/share/syntaxis

  How to Create & Initialize the Database

  Option 1: Automatic (Recommended)

  Simply run any workspace command - database is created automatically:

  workspace project list
  # ✅ Creates ~/.local/share/core/workspace.db
  # ✅ Runs migrations automatically
  # ✅ Ready to use

  Option 2: Programmatic (for developers)

  use workspace_core::persistence::PersistenceLayer;

  #[tokio::main]
  async fn main() -> Result<()> {
      // Option A: Create at custom location
      let db = PersistenceLayer::new("/path/to/database.db").await?;

      // Option B: Create at standard location
      let db = PersistenceLayer::global("workspace").await?;

      // Option C: In-memory for testing
      let db = PersistenceLayer::new_memory().await?;

      // Database is ready with all tables created and migrations applied
      Ok(())
  }

  Option 3: For syntaxis-api (Manual Setup - Future)

  If you want to use syntaxis-api with its own migrations:

  # Install sqlx-cli
  cargo install sqlx-cli --no-default-features --features sqlite

  # Run migrations
  sqlx migrate run \
    --database-url "sqlite:///path/to/api.db" \
    --source "core/crates/syntaxis-api/migrations"

  # API database is ready

  What Gets Created

  Schema (in syntaxis-core)

  -- All these tables with indexes:
  projects              -- Core project data
  checklist_items       -- Tasks (with phase, priority, due dates, etc)
  phase_transitions     -- Audit trail
  security_assessments  -- Security scan results
  tool_configurations   -- Tool settings
  activity_logs         -- Change tracking
  backup_history        -- Backup records

  Pragmas (Best Practices)

  PRAGMA foreign_keys = ON;  -- Enforce referential integrity
  -- All CREATE statements use: IF NOT EXISTS
  -- Safe to run multiple times ✅

  Integration with Wrapper Scripts

  The wrapper scripts ensure database directory exists:

  In install-cli.nu, the wrapper sets:
  $env.WORKSPACE_DATA_DIR = "~/.local/share/syntaxis"

  Then syntaxis-core code uses this path to store the database.

  Testing & Development

  For tests, use in-memory database (no file I/O):

  #[tokio::test]
  async fn test_project_creation() {
      // In-memory database created on test startup
      let db = PersistenceLayer::new_memory().await.unwrap();

      // Database is fully initialized with all tables
      // Test your code here
      // Database automatically cleaned up when test ends
  }

  What You Should Know

  ✅ Automatic: Database created on first use
  ✅ Idempotent: Migrations use IF NOT EXISTS - safe to run multiple times
  ✅ Type-safe: SQLx compile-time checked queries
  ✅ No setup needed: Just run workspace project list
  ⚠️ syntaxis-api migrations: Not auto-run (by design) - use manual setup if needed
  ✅ Standard locations: Uses ~/.local/share/ (XDG spec)

  Summary

  | Aspect         | Status                                              |
  |----------------|-----------------------------------------------------|
  | Auto-creation  | ✅ Yes (syntaxis-core)                              |
  | Location       | ~/.local/share/core/                 |
  | Schema version | 20250101 (syntaxis-core), 20250115 (syntaxis-api) |
  | Tables         | 8 tables with indexes                               |
  | Foreign keys   | ✅ Enabled                                           |
  | Migrations     | Embedded in code (automatic)                        |
  | Manual setup   | Not needed for syntaxis-core                       |
  | API setup      | Optional (sqlx-cli required)                        |

  Everything is already set up and will work automatically when you run the workspace tools! 🎉