Vapora/migrations/001_initial_schema.surql

-- Migration 001: Initial Schema
-- Creates core tables: projects, tasks, users
-- Establishes multi-tenant architecture with permissions

-- Projects table
DEFINE TABLE projects SCHEMAFULL
    PERMISSIONS
        FOR select WHERE tenant_id = $auth.tenant_id
        FOR create, update, delete WHERE tenant_id = $auth.tenant_id;

DEFINE FIELD id ON TABLE projects TYPE record<projects>;
DEFINE FIELD tenant_id ON TABLE projects TYPE string ASSERT $value != NONE;
DEFINE FIELD title ON TABLE projects TYPE string ASSERT $value != NONE AND string::len($value) > 0;
DEFINE FIELD description ON TABLE projects TYPE option<string>;
DEFINE FIELD status ON TABLE projects TYPE string ASSERT $value INSIDE ["active", "archived", "completed"] DEFAULT "active";
DEFINE FIELD features ON TABLE projects TYPE array<string> DEFAULT [];
DEFINE FIELD created_at ON TABLE projects TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at ON TABLE projects TYPE datetime DEFAULT time::now() VALUE time::now();

DEFINE INDEX idx_projects_tenant ON TABLE projects COLUMNS tenant_id;
DEFINE INDEX idx_projects_status ON TABLE projects COLUMNS status;
DEFINE INDEX idx_projects_tenant_status ON TABLE projects COLUMNS tenant_id, status;

-- Tasks table
DEFINE TABLE tasks SCHEMAFULL
    PERMISSIONS
        FOR select WHERE tenant_id = $auth.tenant_id
        FOR create, update, delete WHERE tenant_id = $auth.tenant_id;

DEFINE FIELD id ON TABLE tasks TYPE record<tasks>;
DEFINE FIELD tenant_id ON TABLE tasks TYPE string ASSERT $value != NONE;
DEFINE FIELD project_id ON TABLE tasks TYPE string ASSERT $value != NONE;
DEFINE FIELD title ON TABLE tasks TYPE string ASSERT $value != NONE AND string::len($value) > 0;
DEFINE FIELD description ON TABLE tasks TYPE option<string>;
DEFINE FIELD status ON TABLE tasks TYPE string ASSERT $value INSIDE ["todo", "doing", "review", "done"] DEFAULT "todo";
DEFINE FIELD assignee ON TABLE tasks TYPE string DEFAULT "unassigned";
DEFINE FIELD priority ON TABLE tasks TYPE string ASSERT $value INSIDE ["low", "medium", "high", "critical"] DEFAULT "medium";
DEFINE FIELD task_order ON TABLE tasks TYPE int DEFAULT 0;
DEFINE FIELD feature ON TABLE tasks TYPE option<string>;
DEFINE FIELD created_at ON TABLE tasks TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at ON TABLE tasks TYPE datetime DEFAULT time::now() VALUE time::now();

DEFINE INDEX idx_tasks_tenant ON TABLE tasks COLUMNS tenant_id;
DEFINE INDEX idx_tasks_project ON TABLE tasks COLUMNS project_id;
DEFINE INDEX idx_tasks_status ON TABLE tasks COLUMNS status;
DEFINE INDEX idx_tasks_assignee ON TABLE tasks COLUMNS assignee;
DEFINE INDEX idx_tasks_tenant_project ON TABLE tasks COLUMNS tenant_id, project_id;
DEFINE INDEX idx_tasks_project_status ON TABLE tasks COLUMNS project_id, status;
DEFINE INDEX idx_tasks_project_order ON TABLE tasks COLUMNS project_id, task_order;

-- Users table
DEFINE TABLE users SCHEMAFULL
    PERMISSIONS
        FOR select WHERE id = $auth.id OR "admin" IN $auth.roles
        FOR create WHERE "admin" IN $auth.roles
        FOR update WHERE id = $auth.id OR "admin" IN $auth.roles
        FOR delete WHERE "admin" IN $auth.roles;

DEFINE FIELD id ON TABLE users TYPE record<users>;
DEFINE FIELD email ON TABLE users TYPE string ASSERT $value != NONE AND string::is::email($value);
DEFINE FIELD username ON TABLE users TYPE string ASSERT $value != NONE AND string::len($value) >= 3;
DEFINE FIELD password_hash ON TABLE users TYPE string ASSERT $value != NONE;
DEFINE FIELD roles ON TABLE users TYPE array<string> DEFAULT ["user"];
DEFINE FIELD created_at ON TABLE users TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at ON TABLE users TYPE datetime DEFAULT time::now() VALUE time::now();

DEFINE INDEX idx_users_email ON TABLE users COLUMNS email UNIQUE;
DEFINE INDEX idx_users_username ON TABLE users COLUMNS username UNIQUE;