PiCloud/crates/manager-core/migrations/0005_apps.sql

-- Phase 3b multi-app scoping — see blueprint §11.5.
--
-- Apps are the top-level isolation boundary for scripts, routes, domain
-- claims and (forward) data. The orchestrator dispatches Host → app_id →
-- route trie; cross-app resource access is not possible.
--
-- This migration is unconditional:
--   1. Creates the three new tables (apps, app_domains, app_slug_history).
--   2. Always inserts a "default" app claiming `localhost` so existing
--      installs get a usable home for their pre-existing scripts/routes.
--   3. Backfills app_id on scripts, routes, execution_logs from the
--      default app row, then promotes the columns to NOT NULL + FK.
--
-- Fresh installs get the same "default" app row; an in-Rust bootstrap
-- step (manager-core::app_bootstrap) decides whether to seed a Hello
-- World script into it. Doing the seed in Rust keeps it testable and
-- lets the script source live in a real .rhai file.

CREATE TABLE apps (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- URL-safe identifier; mutable via the rename flow which records
    -- the prior slug in app_slug_history for permanent 301 redirects.
    -- Format validation (`^[a-z0-9][a-z0-9-]{0,62}$`, reserved-word
    -- check) lives in Rust handlers, not SQL.
    slug            TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Domain claims. Most-specific wins at request time; same-shape
-- collisions are rejected at claim time via the UNIQUE(shape_key).
-- shape_key encoding:
--   exact:<lowercased-host>            for shape='exact'
--   wildcard:<lowercased-suffix>       for shape='wildcard' AND 'parameterized'
-- (parameterized is the same shape as wildcard for collision — the
-- parameter name is a binding, not a discriminator. See blueprint §11.5.)
CREATE TABLE app_domains (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id          UUID NOT NULL REFERENCES apps(id) ON DELETE CASCADE,
    pattern         TEXT NOT NULL,
    shape           TEXT NOT NULL CHECK (shape IN ('exact', 'wildcard', 'parameterized')),
    shape_key       TEXT NOT NULL UNIQUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX app_domains_app_id_idx ON app_domains (app_id);

-- Permanent 301 redirects after a slug rename. A row dies only when
-- another app explicitly claims the retired slug (with confirmation in
-- the UI). On_delete cascade: if the owning app is deleted, its history
-- row goes too (otherwise the redirect would point at a dead app).
CREATE TABLE app_slug_history (
    slug            TEXT PRIMARY KEY,
    current_app_id  UUID NOT NULL REFERENCES apps(id) ON DELETE CASCADE,
    retired_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed the default app + a localhost claim. Used by both upgrade and
-- fresh-install paths; the Rust bootstrap layers Hello World on top
-- only when the install was fresh.
WITH default_app AS (
    INSERT INTO apps (slug, name, description)
    VALUES ('default', 'Default', 'The default application — assigned to all pre-existing scripts and routes during the multi-app migration.')
    RETURNING id
)
INSERT INTO app_domains (app_id, pattern, shape, shape_key)
SELECT id, 'localhost', 'exact', 'exact:localhost' FROM default_app;

-- Add app_id to scripts. The default app already exists (above), so
-- there is exactly one row to look up.
ALTER TABLE scripts ADD COLUMN app_id UUID;
UPDATE scripts SET app_id = (SELECT id FROM apps WHERE slug = 'default');
ALTER TABLE scripts ALTER COLUMN app_id SET NOT NULL;
ALTER TABLE scripts
    ADD CONSTRAINT scripts_app_id_fk FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE RESTRICT;

-- Per-app name uniqueness. Two apps can each have a script called
-- "echo"; previously they could not.
DROP INDEX scripts_name_uidx;
CREATE UNIQUE INDEX scripts_name_uidx ON scripts (app_id, LOWER(name));

CREATE INDEX scripts_app_id_idx ON scripts (app_id);

-- Add app_id to routes, mirroring the script's app.
ALTER TABLE routes ADD COLUMN app_id UUID;
UPDATE routes
   SET app_id = scripts.app_id
  FROM scripts
 WHERE routes.script_id = scripts.id;
ALTER TABLE routes ALTER COLUMN app_id SET NOT NULL;
ALTER TABLE routes
    ADD CONSTRAINT routes_app_id_fk FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE;

-- Replace the route uniqueness index so two apps can claim identical
-- (host_kind, host, path_kind, path, method) tuples — they live in
-- separate route trees and never see each other.
DROP INDEX routes_unique_binding_idx;
CREATE UNIQUE INDEX routes_unique_binding_idx
    ON routes (app_id, host_kind, host, path_kind, path, COALESCE(method, ''));

CREATE INDEX routes_app_id_idx ON routes (app_id);

-- Add app_id to execution_logs. Materialized at write time so future
-- script-moves (or eventual export/import) don't silently retag history.
ALTER TABLE execution_logs ADD COLUMN app_id UUID;
UPDATE execution_logs
   SET app_id = scripts.app_id
  FROM scripts
 WHERE execution_logs.script_id = scripts.id;
ALTER TABLE execution_logs ALTER COLUMN app_id SET NOT NULL;
ALTER TABLE execution_logs
    ADD CONSTRAINT execution_logs_app_id_fk FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE;

CREATE INDEX execution_logs_app_id_created_at_idx
    ON execution_logs (app_id, created_at DESC);