03-Database-Migrations

0. Bootstrap

No extension migration is required on the default (MySQL/MariaDB) path. The pgvector extension is created only when the connection driver is pgsql, and it is done inside the rag_chunks migration itself (see §7) so a single migration set works on both engines.

1. projects

Schema::create('projects', function (Blueprint $t) {
	$t->id();
	$t->string('name');
	$t->string('slug')->unique();
	$t->text('description')->nullable();
	$t->string('repository_url')->nullable();
	$t->string('local_workspace_path')->nullable();
	$t->string('default_branch')->default('main');
	$t->string('current_branch')->nullable();
	$t->string('framework_detected')->nullable();
	$t->string('language_detected')->nullable();
	$t->string('status')->default('creating')->index();
	$t->foreignId('created_by')->constrained('users')->cascadeOnDelete();
	$t->timestamps();
	$t->softDeletes();
});

2. project_connections

Schema::create('project_connections', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->string('kind'); // 'git', 'github_app', 's3', ...
	$t->json('config'); // encrypted at app layer if secrets
	$t->timestamps();
	$t->unique(['project_id','kind']);
});

3. agent_runs

Schema::create('agent_runs', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('user_id')->constrained()->cascadeOnDelete();
	$t->string('title');
	$t->longText('prompt');
	$t->string('status')->default('draft')->index();
	$t->string('current_step')->nullable();
	$t->string('selected_agent');
	$t->string('selected_model');
	$t->string('branch_name')->nullable();
	$t->timestamp('started_at')->nullable();
	$t->timestamp('completed_at')->nullable();
	$t->text('failed_reason')->nullable();
	$t->json('metadata_json')->nullable();
	$t->timestamps();
	$t->index(['project_id','status']);
	$t->index(['user_id','created_at']);
});

4. agent_events

Schema::create('agent_events', function (Blueprint $t) {
	$t->bigIncrements('id');
	$t->foreignId('run_id')->constrained('agent_runs')->cascadeOnDelete();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->string('event_type', 64);
	$t->string('title');
	$t->text('message')->nullable();
	$t->json('payload_json')->nullable();
	$t->string('severity', 16)->default('info');
	$t->timestamp('created_at')->useCurrent();
	$t->index(['run_id','id']);
	$t->index(['project_id','created_at']);
	$t->index(['event_type']);
});

5. workspace_files

Schema::create('workspace_files', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('run_id')->constrained('agent_runs')->cascadeOnDelete();
	$t->text('path');
	$t->string('action', 16); // read|create|update|delete
	$t->string('before_hash', 64)->nullable();
	$t->string('after_hash', 64)->nullable();
	$t->longText('diff')->nullable();
	$t->foreignId('snapshot_id')->nullable()->constrained('workspace_snapshots')->nullOnDelete();
	$t->timestamp('created_at')->useCurrent();
	$t->index(['run_id']);
	$t->index(['project_id']);
});

6. workspace_snapshots

Schema::create('workspace_snapshots', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('run_id')->nullable()->constrained('agent_runs')->nullOnDelete();
	$t->string('snapshot_type', 32); // pre_run|pre_command|manual|final
	$t->string('git_commit_hash', 64)->nullable();
	$t->string('archive_path')->nullable();
	$t->text('description')->nullable();
	$t->timestamp('created_at')->useCurrent();
	$t->index(['project_id','snapshot_type']);
	$t->index(['run_id']);
});

7. rag_chunks (driver-aware)

public function up(): void
{
    $driver = DB::connection()->getDriverName();

    Schema::create('rag_chunks', function (Blueprint $t) {
        $t->id();
        $t->foreignId('project_id')->constrained()->cascadeOnDelete();
        $t->string('source_type', 64);
        $t->string('source_path', 1024)->nullable();
        $t->string('symbol_name')->nullable();
        $t->string('chunk_title')->nullable();
        $t->longText('chunk_text');
        $t->json('metadata_json')->nullable();
        $t->string('content_hash', 64);
        $t->unsignedInteger('token_count')->nullable();
        $t->timestamps();
        $t->index(['project_id', 'source_type']);
        $t->index(['project_id', 'content_hash']);
        $t->index('content_hash');
    });

    if ($driver === 'pgsql') {
        DB::statement('CREATE EXTENSION IF NOT EXISTS vector');
        DB::statement('ALTER TABLE rag_chunks ADD COLUMN embedding vector(3072)');
        DB::statement('CREATE INDEX rag_chunks_embedding_idx ON rag_chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100)');
    } else {
        // MySQL / MariaDB default: embedding as JSON; cosine similarity in app layer.
        Schema::table('rag_chunks', fn (Blueprint $t) => $t->json('embedding')->nullable());
    }
}

Default (MySQL/MariaDB): embedding is a JSON column; the RagContextService does cosine similarity in PHP. Works to ~50k chunks per project.

Upgrade path (PostgreSQL + pgvector): flip DB_CONNECTION=pgsql, re-run migrations on a fresh DB or use the idempotent migration above, then php artisan agent:reindex --all.

Not supported in v1: Qdrant/Milvus/Weaviate sidecars. The driver-aware migration is the only contract.

8. agent_providers

Schema::create('agent_providers', function (Blueprint $t) {
	$t->id();
	$t->string('key')->unique(); // openai, claude_api, claude_code, local_shell
	$t->string('display_name');
	$t->json('capabilities'); // ["chat","tools","code_exec","streaming"]
	$t->boolean('enabled')->default(true);
	$t->timestamps();
});

9. agent_settings

Schema::create('agent_settings', function (Blueprint $t) {
	$t->id();
	$t->string('key')->unique();
	$t->longText('value')->nullable(); // encrypted at the model level when secret=true
	$t->boolean('is_secret')->default(false);
	$t->string('category')->default('general');
	$t->text('description')->nullable();
	$t->timestamps();
});

10. git_operations

Schema::create('git_operations', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('run_id')->nullable()->constrained('agent_runs')->nullOnDelete();
	$t->string('operation', 32);
	$t->json('args_json')->nullable();
	$t->json('result_json')->nullable();
	$t->longText('stdout')->nullable();
	$t->longText('stderr')->nullable();
	$t->integer('exit_code')->nullable();
	$t->timestamp('created_at')->useCurrent();
	$t->index(['project_id','operation']);
});

11. documentation_updates

Schema::create('documentation_updates', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('run_id')->nullable()->constrained('agent_runs')->nullOnDelete();
	$t->string('doc_path');
	$t->text('change_summary');
	$t->longText('diff')->nullable();
	$t->timestamp('created_at')->useCurrent();
	$t->index(['project_id','doc_path']);
});

12. command_executions

Schema::create('command_executions', function (Blueprint $t) {
	$t->id();
	$t->foreignId('project_id')->constrained()->cascadeOnDelete();
	$t->foreignId('run_id')->constrained('agent_runs')->cascadeOnDelete();
	$t->text('command');
	$t->json('args_json')->nullable();
	$t->string('working_dir')->nullable();
	$t->integer('timeout_seconds')->default(120);
	$t->timestamp('started_at')->nullable();
	$t->timestamp('finished_at')->nullable();
	$t->integer('exit_code')->nullable();
	$t->string('stdout_path')->nullable(); // S3 key
	$t->string('stderr_path')->nullable();
	$t->boolean('was_killed')->default(false);
	$t->boolean('was_blocked')->default(false);
	$t->string('block_reason')->nullable();
	$t->index(['run_id','started_at']);
});

13. run_reviews

Schema::create('run_reviews', function (Blueprint $t) {
	$t->id();
	$t->foreignId('run_id')->constrained('agent_runs')->cascadeOnDelete();
	$t->foreignId('reviewer_user_id')->constrained('users')->cascadeOnDelete();
	$t->string('decision', 16); // approved|rejected|commented
	$t->text('notes')->nullable();
	$t->timestamp('created_at')->useCurrent();
});

14. Seeders

• AgentProviderSeeder — inserts openai, claude_api, claude_code, local_shell.

• AgentSettingsSeeder — inserts default keys (see 08 — Server Params).

15. Rollback discipline

• Every migration is fully reversible (down() drops in opposite order).

• Never edit a shipped migration; create a new one.