path: root/makima/migrations/20250122000001_checkpoint_patches.sql

-- Checkpoint patches table for storing git diffs to enable task recovery
-- When a local worktree is deleted/corrupted, the patch can be used to restore state

CREATE TABLE IF NOT EXISTS checkpoint_patches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE CASCADE,
    base_commit_sha VARCHAR(40) NOT NULL,     -- Commit to apply patch on top of
    patch_data BYTEA NOT NULL,                -- Compressed git diff (gzip)
    patch_size_bytes INTEGER NOT NULL,
    files_count INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,          -- TTL for auto-cleanup
    CONSTRAINT patch_size_limit CHECK (patch_size_bytes <= 10485760)  -- 10MB limit
);

CREATE INDEX idx_checkpoint_patches_task ON checkpoint_patches(task_id);
CREATE INDEX idx_checkpoint_patches_expires ON checkpoint_patches(expires_at);

-- Link checkpoints to their patches
ALTER TABLE task_checkpoints
ADD COLUMN IF NOT EXISTS patch_id UUID REFERENCES checkpoint_patches(id) ON DELETE SET NULL;

COMMENT ON TABLE checkpoint_patches IS 'Stores compressed git diffs for task recovery when local worktree is lost';
COMMENT ON COLUMN checkpoint_patches.base_commit_sha IS 'The commit SHA that the patch should be applied on top of';
COMMENT ON COLUMN checkpoint_patches.patch_data IS 'Gzip-compressed git diff data';
COMMENT ON COLUMN checkpoint_patches.expires_at IS 'Automatic cleanup time (TTL)';