mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-27 13:06:20 +02:00
3f2e1c68d7
Renumber the six VCS migrations off the 213/214/215 prefix collisions to a unique contiguous tail 216-221 (tables before indexes), and make 214_chat_session_project idempotent (ADD COLUMN IF NOT EXISTS) so the #5868 rename-induced re-run no-ops instead of crashing. Fixes the red migration lint on main and unblocks the dev deploy.
93 lines
4.1 KiB
SQL
93 lines
4.1 KiB
SQL
-- VCS (self-hosted Git provider: Forgejo, Gitea, GitLab) integration:
|
|
-- per-workspace token-based connections. Unlike the GitHub App model
|
|
-- (server/migrations/079), these providers have no app/installation concept —
|
|
-- each workspace stores its own instance URL plus an access token used for
|
|
-- API enrichment and a webhook secret used to verify inbound webhook signatures.
|
|
--
|
|
-- Both secrets are stored as base64-encoded secretbox ciphertext (never
|
|
-- plaintext). Decryption uses the MULTICA_VCS_SECRET_KEY box wired in
|
|
-- cmd/server/router.go.
|
|
--
|
|
-- Mirrored pull requests and commit statuses are stored in vcs_pull_request
|
|
-- and vcs_commit_status tables, separate from GitHub tables.
|
|
--
|
|
-- Per the project migration rules these tables carry NO foreign keys or
|
|
-- cascades: relationships and dependent cleanup are resolved in application
|
|
-- code (DeleteVCSConnection, DeleteWorkspace, and DeleteIssue each sweep the
|
|
-- rows below in a single atomic statement). The inline UNIQUE / PRIMARY KEY
|
|
-- constraints stay — they back the ON CONFLICT upsert targets in vcs.sql.
|
|
-- Secondary indexes live in follow-up single-statement CREATE INDEX
|
|
-- CONCURRENTLY migrations (217-221), which cannot share a file with these
|
|
-- CREATE TABLEs.
|
|
|
|
CREATE TABLE IF NOT EXISTS vcs_connection (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
provider TEXT NOT NULL DEFAULT 'forgejo'
|
|
CHECK (provider IN ('forgejo', 'gitea', 'gitlab')),
|
|
instance_url TEXT NOT NULL,
|
|
account_login TEXT NOT NULL,
|
|
access_token_encrypted TEXT NOT NULL,
|
|
webhook_secret_encrypted TEXT NOT NULL,
|
|
connected_by_id UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (workspace_id, instance_url)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS vcs_pull_request (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
connection_id UUID NOT NULL,
|
|
provider TEXT NOT NULL DEFAULT 'forgejo'
|
|
CHECK (provider IN ('forgejo', 'gitea', 'gitlab')),
|
|
repo_owner TEXT NOT NULL,
|
|
repo_name TEXT NOT NULL,
|
|
pr_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
state TEXT NOT NULL
|
|
CHECK (state IN ('open', 'closed', 'merged', 'draft')),
|
|
html_url TEXT NOT NULL,
|
|
branch TEXT,
|
|
head_sha TEXT NOT NULL DEFAULT '',
|
|
author_login TEXT,
|
|
author_avatar_url TEXT,
|
|
merged_at TIMESTAMPTZ,
|
|
closed_at TIMESTAMPTZ,
|
|
pr_created_at TIMESTAMPTZ NOT NULL,
|
|
pr_updated_at TIMESTAMPTZ NOT NULL,
|
|
additions INTEGER NOT NULL DEFAULT 0,
|
|
deletions INTEGER NOT NULL DEFAULT 0,
|
|
changed_files INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (connection_id, repo_owner, repo_name, pr_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS issue_vcs_pull_request (
|
|
issue_id UUID NOT NULL,
|
|
pull_request_id UUID NOT NULL,
|
|
close_intent BOOLEAN NOT NULL DEFAULT FALSE,
|
|
-- reference_only marks a link justified ONLY by a bare body mention (no
|
|
-- closing keyword and no title/branch reference). Mirrors the GitHub
|
|
-- issue_pull_request column (migration 127): such links are hidden from the
|
|
-- issue PR list and excluded from the close aggregate so a drive-by mention
|
|
-- neither shows as a working PR nor blocks a genuine Closes sibling.
|
|
reference_only BOOLEAN NOT NULL DEFAULT FALSE,
|
|
linked_by_type TEXT,
|
|
linked_by_id UUID,
|
|
linked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (issue_id, pull_request_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS vcs_commit_status (
|
|
connection_id UUID NOT NULL,
|
|
sha TEXT NOT NULL,
|
|
context TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
target_url TEXT,
|
|
description TEXT,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (connection_id, sha, context)
|
|
);
|