Integration Guide

# docker-compose.yml
services:
  vericto-proxy:
    image: ghcr.io/vericto/vericto-proxy:latest
    ports:
      - "5433:5433"
    environment:
      VERICTO_API_URL: "https://api.vericto.com"
      VERICTO_API_KEY: "${VERICTO_API_KEY}"
      VERICTO_DATABASE_ID: "${VERICTO_DATABASE_ID}"
      VERICTO_WIRE_PROTOCOL: "postgres"   # or "mysql"
      UPSTREAM_HOST: "your-db-host.rds.amazonaws.com"
      UPSTREAM_PORT: "5432"
      PROXY_LISTEN_PORT: "5433"
      # Remote DB (RDS): encrypt the proxy → database hop.
      UPSTREAM_SSLMODE: "require"   # or "verify-full" + UPSTREAM_SSLROOTCERT

  app:
    environment:
      # Point your app to the proxy — same user:pass as before
      DATABASE_URL: "postgres://user:pass@vericto-proxy:5433/mydb"
    depends_on:
      - vericto-proxy

# vericto-proxy-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: vericto-proxy
spec:
  replicas: 2
  selector:
    matchLabels:
      app: vericto-proxy
  template:
    metadata:
      labels:
        app: vericto-proxy
    spec:
      containers:
        - name: vericto-proxy
          image: ghcr.io/vericto/vericto-proxy:latest
          ports:
            - containerPort: 5433
          env:
            - name: VERICTO_API_URL
              value: "https://api.vericto.com"
            - name: VERICTO_API_KEY
              valueFrom:
                secretKeyRef:
                  name: vericto-secrets
                  key: api-key
            - name: VERICTO_DATABASE_ID
              value: "your-database-id"
            - name: VERICTO_WIRE_PROTOCOL
              value: "postgres"   # or "mysql"
            - name: UPSTREAM_HOST
              value: "postgres-service.default.svc.cluster.local"
            - name: UPSTREAM_PORT
              value: "5432"
            - name: PROXY_LISTEN_PORT
              value: "5433"
          resources:
            requests:
              cpu: "250m"
              memory: "128Mi"
            limits:
              cpu: "500m"
              memory: "256Mi"
---
apiVersion: v1
kind: Service
metadata:
  name: vericto-proxy
spec:
  selector:
    app: vericto-proxy
  ports:
    - port: 5433
      targetPort: 5433

{
  "family": "vericto-proxy",
  "networkMode": "awsvpc",
  "requiresCompatibilities": ["FARGATE"],
  "cpu": "256",
  "memory": "512",
  "containerDefinitions": [{
    "name": "vericto-proxy",
    "image": "ghcr.io/vericto/vericto-proxy:latest",
    "portMappings": [{"containerPort": 5433}],
    "environment": [
      {"name": "VERICTO_API_URL", "value": "https://api.vericto.com"},
      {"name": "VERICTO_DATABASE_ID", "value": "your-database-id"},
      {"name": "VERICTO_WIRE_PROTOCOL", "value": "postgres"},
      {"name": "UPSTREAM_HOST", "value": "your-rds.xxxxx.us-east-1.rds.amazonaws.com"},
      {"name": "UPSTREAM_PORT", "value": "5432"},
      {"name": "PROXY_LISTEN_PORT", "value": "5433"}
    ],
    "secrets": [
      {"name": "VERICTO_API_KEY", "valueFrom": "arn:aws:ssm:us-east-1:xxx:parameter/vericto/api-key"}
    ]
  }]
}

# Shell installer (Linux / macOS)
curl -fsSL https://github.com/vericto/vericto-cli/releases/latest/download/vericto-cli-installer.sh | sh

# npm / npx (CI runners already have Node)
npm install -g @vericto/vericto-cli
# or, zero-install in CI:
npx @vericto/vericto-cli check migrations/*.sql

# Docker (container step in CI)
docker run --rm -e VERICTO_API_KEY \
  ghcr.io/vericto/vericto-cli:latest check migrations/*.sql

# From source (Rust toolchain)
git clone https://github.com/vericto/vericto-cli && cargo install --path vericto-cli

# Prebuilt binaries for Linux (x86_64/aarch64), macOS (x86_64/aarch64) and
# Windows (x86_64) are published on every release, each with a SHA-256 checksum:
#   https://github.com/vericto/vericto-cli/releases
# Homebrew and Scoop are planned.

# GitHub Actions — .github/workflows/vericto.yml
name: Vericto SQL Check
on: pull_request
permissions:
  contents: read
  security-events: write   # required to upload SARIF to the Security tab
jobs:
  sql-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }   # --changed needs history for the merge base
      - run: npm install -g @vericto/vericto-cli
      - run: vericto check --changed --format sarif --output vericto.sarif
        env:
          VERICTO_API_KEY: ${{ secrets.VERICTO_API_KEY }}
      - uses: github/codeql-action/upload-sarif@v4   # inline PR annotations
        if: always()   # upload findings even when the check step exits non-zero
        with:
          sarif_file: vericto.sarif

# GitLab CI — .gitlab-ci.yml
vericto-check:
  stage: test
  image: node:20
  script:
    - npm install -g @vericto/vericto-cli
    - vericto check --changed --format gitlab-codequality --output gl-code-quality.json
  variables:
    VERICTO_API_KEY: $VERICTO_API_KEY
  artifacts:
    reports:
      codequality: gl-code-quality.json   # findings render inline on the MR
  rules:
    - changes: ["migrations/**/*.sql"]

# CircleCI — .circleci/config.yml
version: 2.1
jobs:
  vericto-check:
    docker:
      - image: cimg/node:20.11
    steps:
      - checkout
      - run: npm install -g @vericto/vericto-cli
      # exit code 1 fails the job; --changed needs full history
      - run: vericto check --changed --format sarif --output vericto.sarif
      - store_artifacts:
          path: vericto.sarif
workflows:
  sql-check:
    jobs:
      - vericto-check:
          context: vericto        # holds VERICTO_API_KEY

// Jenkins — Jenkinsfile (declarative pipeline)
pipeline {
  agent { docker { image 'node:20' } }
  environment { VERICTO_API_KEY = credentials('vericto-api-key') }
  stages {
    stage('Vericto SQL Check') {
      steps {
        sh 'npm install -g @vericto/vericto-cli'
        // Non-zero exit code fails the stage
        sh 'vericto check --changed --format sarif --output vericto.sarif'
      }
      post {
        always { archiveArtifacts artifacts: 'vericto.sarif', allowEmptyArchive: true }
      }
    }
  }
}

# curl — evaluate one or more queries
curl -s -X POST https://api.vericto.com/api/v1/ci/check-key \
  -H "X-API-Key: $VERICTO_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "dialect": "postgres",
    "file_name": "0001_init.sql",
    "queries": [
      { "line": 1, "sql": "ALTER TABLE users DROP COLUMN email;" }
    ]
  }'

# Response:
# {
#   "summary": { "total": 1, "blocked": 1, "allowed": 0, "ruleset_version": "…" },
#   "queries": [
#     { "line": 1, "status": "BLOCKED", "rule_code": "VERICTO-014",
#       "severity": "high", "ast_node_path": "…", "suggested_fix": "…" }
#   ],
#   "exit_code": 1
# }

// Node.js — validate a query before applying a migration
const res = await fetch('https://api.vericto.com/api/v1/ci/check-key', {
  method: 'POST',
  headers: {
    'X-API-Key': process.env.VERICTO_API_KEY,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    dialect: 'postgres',
    queries: [{ line: 1, sql: sqlToValidate }],
  }),
}).then(r => r.json());

if (res.exit_code === 1) {
  const blocked = res.queries.find(q => q.status === 'BLOCKED');
  throw new Error(`Blocked by ${blocked.rule_code}: ${blocked.ast_node_path}`);
}

# Python — pre-deploy migration check
import requests, sys, glob, os

queries, files = [], []
for i, path in enumerate(sorted(glob.glob("migrations/*.sql")), start=1):
    with open(path) as f:
        queries.append({"line": i, "sql": f.read()})
        files.append(path)

r = requests.post(
    "https://api.vericto.com/api/v1/ci/check-key",
    headers={"X-API-Key": os.environ["VERICTO_API_KEY"]},
    json={"dialect": "postgres", "queries": queries},
).json()

if r["exit_code"] == 1:
    for q in r["queries"]:
        if q["status"] == "BLOCKED":
            print(f"BLOCKED: {files[q['line']-1]} — {q['rule_code']}")
    sys.exit(1)
print("All migrations safe ✓")

// Java — validate a query before applying a migration
import java.net.http.*;
import java.net.URI;

var body = """
    { "dialect": "postgres",
      "queries": [{ "line": 1, "sql": "ALTER TABLE users DROP COLUMN email;" }] }
    """;

var req = HttpRequest.newBuilder()
    .uri(URI.create("https://api.vericto.com/api/v1/ci/check-key"))
    .header("X-API-Key", System.getenv("VERICTO_API_KEY"))
    .header("Content-Type", "application/json")
    .POST(HttpRequest.BodyPublishers.ofString(body))
    .build();

var res = HttpClient.newHttpClient()
    .send(req, HttpResponse.BodyHandlers.ofString());

// The endpoint returns 200; the verdict is in the body (parse exit_code).
if (res.body().contains("\"exit_code\":1")) {
    System.err.println("Blocked: " + res.body());
    System.exit(1);
}

// Go — validate a query before applying a migration
package main

import (
	"bytes"
	"encoding/json"
	"net/http"
	"os"
)

func main() {
	body, _ := json.Marshal(map[string]any{
		"dialect": "postgres",
		"queries": []map[string]any{
			{"line": 1, "sql": "ALTER TABLE users DROP COLUMN email;"},
		},
	})

	req, _ := http.NewRequest("POST",
		"https://api.vericto.com/api/v1/ci/check-key", bytes.NewReader(body))
	req.Header.Set("X-API-Key", os.Getenv("VERICTO_API_KEY"))
	req.Header.Set("Content-Type", "application/json")

	resp, err := http.DefaultClient.Do(req)
	if err != nil {
		os.Exit(4) // backend/network error
	}
	defer resp.Body.Close()

	var out struct {
		ExitCode int `json:"exit_code"`
		Queries  []struct {
			Status   string `json:"status"`
			RuleCode string `json:"rule_code"`
		} `json:"queries"`
	}
	json.NewDecoder(resp.Body).Decode(&out)

	if out.ExitCode == 1 {
		os.Exit(1) // something was blocked → fail the build
	}
}

// .NET (C#) — validate a query before applying a migration
using System.Net.Http.Json;

var http = new HttpClient();
http.DefaultRequestHeaders.Add(
    "X-API-Key", Environment.GetEnvironmentVariable("VERICTO_API_KEY"));

var payload = new {
    dialect = "postgres",
    queries = new[] { new { line = 1, sql = "ALTER TABLE users DROP COLUMN email;" } },
};

var res = await http.PostAsJsonAsync(
    "https://api.vericto.com/api/v1/ci/check-key", payload);
var result = await res.Content.ReadFromJsonAsync<CheckResult>();

if (result?.exit_code == 1)
{
    Console.Error.WriteLine("Blocked by a security rule");
    Environment.Exit(1);
}

record Query(int line, string status, string? rule_code);
record CheckResult(int exit_code, Query[] queries);