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! 🎉