Skip to content

Latest commit

 

History

History
333 lines (254 loc) · 25.4 KB

SQL_OPERATIONS.md

File metadata and controls

333 lines (254 loc) · 25.4 KB

SQL Operations Reference — Polyglot DevOps Intelligence Platform

Scope of this document: this is a code-derived audit of the PostgreSQL side only — every raw SQL statement that actually exists in api-gateway/ today, which file it lives in, which function runs it, and what it does. Neo4j/Cypher is intentionally out of scope here (see docs/SCHEMA_DIAGRAM.md for the graph model). Written for exam/viva prep: read top-to-bottom for the full picture, or jump to a table/route via the index below.


0. Quick Index

Want to know... Go to
How many tables, and their columns? §1 Tables
How tables relate to each other (ERD)? §2 ER Diagram
Every CRUD operation, grouped by table? §3 CRUD Matrix
Every SQL query, grouped by file/function? §4 File-by-File Walkthrough
Transactions / BEGIN-COMMIT-ROLLBACK demos? §5 Transactions & Concurrency
Indexes and how they're proven to work? §6 Indexes
One-time setup/admin scripts (not API routes)? §7 Setup & Admin Scripts
Full endpoint → SQL map in one table? §8 Endpoint → SQL Cheat Sheet

1. Tables (17 total after RBAC hardening migration)

All defined in docs/DB_SCHEMA.md, created by api-gateway/utils/init-db.js and migrations 001_dbms_features.sql and 002_rbac_hardening.sql.

# Table Primary Key Purpose
1 organizations organization_id Top-level tenant/customer
2 users user_id (UUID, mirrors Supabase auth.users) App users, 6-role MVP model (platform_admin, org_admin, project_manager, security_analyst, developer, viewer)
3 teams team_id Sub-groups within an org
4 team_members team_member_id Join table: users ↔ teams
5 projects project_id Groups repositories under an org
6 project_members (project_id, user_id) Project membership for project-level isolation
7 repositories repository_id A synced GitHub repo
8 dependency_files file_id A manifest file (package.json, etc.) pointer into Supabase Storage
9 dependencies dependency_id A parsed package + version declared by a repo
10 cves cve_id Known vulnerability records
11 dependency_vulnerabilities id Join table: dependencies ↔ cves (many-to-many)
12 security_alerts alert_id A raised alert for a vulnerable dependency in a repo
13 reports report_id AI-generated security advisories generated by FastAPI microservice (Phase 9)
14 notifications notification_id Per-user notifications, created by scan findings
15 scan_runs scan_run_id Per-run scan status, metrics, and timing
16 audit_logs audit_id Organization-scoped actor/action/entity history, resource tracking, outcomes
17 dbms_lab_accounts account_id Isolated rows for ACID transaction demonstration

teams and team_members now have organization-scoped CRUD routes. notifications is created when a scan raises new findings and can be listed/marked read by its owner. scan_runs, audit_logs, and dbms_lab_accounts are installed by the additive npm run db:migrate migration; the lab accounts are isolated demo data.


2. Entity-Relationship Diagram

The core-schema diagram is unreadable at a glance, so this is split into the three clusters the code actually treats separately — who you are, what you own, and what's wrong with it — plus the JOIN chain that ties all three together on every request. Every arrow below is labeled with the literal foreign-key column that backs it, not just a vague verb, so you can match a line in a diagram to a line in a query.

2.1 Identity & organization — "who am I, and who do I work for"

erDiagram
    organizations ||--o{ users        : "users.organization_id"
    organizations ||--o{ teams        : "teams.organization_id"
    organizations ||--o{ projects     : "projects.organization_id"
    users         ||--o{ teams        : "teams.manager_id (manages)"
    teams         ||--o{ team_members : "team_members.team_id"
    users         ||--o{ team_members : "team_members.user_id"
    users         ||--o{ projects     : "projects.created_by"
Loading

teams/team_members now have organization-scoped CRUD in team.controller.js.

2.2 Project hierarchy — "what does an org actually have"

erDiagram
    projects      ||--o{ repositories      : "repositories.project_id"
    repositories  ||--o{ dependency_files  : "dependency_files.repository_id"
    repositories  ||--o{ dependencies      : "dependencies.repository_id"
Loading

This is the chain syncRepositories and extractDependencies write into, in that order: a project holds repos, a repo holds manifest files, parsing a manifest file populates dependencies on that same repo.

2.3 Security scanning — "how a dependency becomes an alert"

erDiagram
    dependencies ||--o{ dependency_vulnerabilities : "dependency_vulnerabilities.dependency_id"
    cves         ||--o{ dependency_vulnerabilities : "dependency_vulnerabilities.cve_id"
    dependencies ||--o{ security_alerts            : "security_alerts.dependency_id"
    repositories ||--o{ security_alerts            : "security_alerts.repository_id"
Loading

dependency_vulnerabilities is the junction row runSecurityScan inserts when a dependency matches a CVE; security_alerts is the human-facing record raised right after, in the same transaction (see §4.7 and §5.2).

2.4 The authorization chain every write/read actually walks

Not an ER relationship — this is the literal JOIN path almost every controller runs to prove "this row belongs to the org you're logged in as" before touching it:

flowchart LR
    REQ["Incoming request\n(JWT → user_id)"] -->|"WHERE u.user_id = $1"| U[users]
    U -->|"JOIN projects p\nON p.organization_id = u.organization_id"| P[projects]
    P -->|"JOIN repositories r\nON r.project_id = p.project_id"| R[repositories]
    R -->|"JOIN ... ON ...repository_id"| T["dependencies\ndependency_files\nsecurity_alerts"]

    style REQ fill:#00000000,stroke-dasharray: 3 3
Loading

Read it left to right as a single query plan: every one of getRepositories, ingestManifest, extractDependencies, runSecurityScan, getAlerts, and resolveAlert runs some prefix of this exact chain in its WHERE/JOIN clauses before it lets you see or change a row.

This users → organizations → projects → repositories → (dependencies|alerts|files) join chain is how the app enforces "you can only see/touch your own org's data" — you'll see it repeated in almost every JOIN below.


3. CRUD Matrix by Table

✅ = implemented and reachable via an API route · ⚠️ = implemented but only in a one-off script, not a route · — = not implemented

Table Create Read Update Delete
organizations ✅ createOrg ✅ getOrgs — —
users ✅ (DB trigger, not app code — see §4.9) ✅ getCurrentUser ✅ updateUserRole, + internal update in createOrg —
projects ✅ syncRepositories (find-or-create) ✅ (embedded in syncRepositories) — —
repositories ✅ syncRepositories (upsert) ✅ getRepositories ✅ syncRepositories (upsert), last_synced_at bump —
dependency_files ✅ ingestManifest ✅ extractDependencies (lookup) ✅ extractDependencies (last_scanned bump) —
dependencies ✅ extractDependencies (upsert) ✅ runSecurityScan (join), ⚠️ explain-index-proof.js ✅ extractDependencies (upsert) —
cves ⚠️ seed-cves.js (bulk COPY) ✅ runSecurityScan (join) — ⚠️ seed-cves.js (DELETE FROM cves before reseeding)
dependency_vulnerabilities ✅ runSecurityScan (read implicitly via join in alerts) — ⚠️ seed-cves.js (cleared before reseed)
security_alerts ✅ runSecurityScan ✅ getAlerts ✅ resolveAlert ⚠️ seed-cves.js (cleared before reseed)
reports ✅ generateReport ✅ listReports — —
teams ✅ createTeam ✅ listTeams — —
team_members ✅ addTeamMember ✅ listTeamMembers — ✅ removeTeamMember
notifications ✅ scanner ✅ getNotifications ✅ markNotificationRead —
scan_runs ✅ runSecurityScan ✅ getScanHistory, getScanTrends ✅ scan completion/failure —
audit_logs ✅ audited actions ✅ getAuditLogs — —
dbms_lab_accounts ✅ migration seed ✅ runTransferLab ✅ transfer demo —

Reading: the platform is Create/Read/Update-heavy; there is no Delete endpoint exposed via the API anywhere. The only DELETE/DROP statements in the whole codebase live in one-time setup/seed scripts you run from the terminal, not in anything the frontend can trigger.


4. File-by-File Walkthrough

Every raw SQL statement below is copy-pasted from the actual source — line numbers are approximate but the file + function name will always get you there fast.

4.1 api-gateway/config/db.config.js — the connection itself

No queries here, but everything downstream depends on it: exports pgPool, a pg.Pool built from DATABASE_URL, max: 10 connections. Every controller does const { pgPool } = require('../config/db.config') and either calls pgPool.query(...) directly (auto-managed connection) or pgPool.connect() to get a dedicated client for a multi-statement transaction.

4.2 controllers/org.controller.js

Function Route SQL What it does
createOrg POST /api/v1/orgs BEGIN → INSERT INTO organizations (...) VALUES (...) RETURNING * → UPDATE users SET organization_id = $1 WHERE user_id = $2 → COMMIT Creates an org and attaches the creator to it, as one transaction — see §5.1 for why.
getOrgs GET /api/v1/orgs SELECT * FROM organizations ORDER BY created_at DESC Lists all orgs.

4.3 controllers/repo.controller.js

Function Route SQL What it does
syncRepositories POST /api/v1/repos/sync BEGIN → SELECT project_id FROM projects WHERE organization_id=$1 AND project_name=$2 (find-or-create) → INSERT INTO projects (...) RETURNING project_id (if not found) → loop: INSERT INTO repositories (...) VALUES (...) ON CONFLICT (project_id, repo_name) DO UPDATE SET ... RETURNING * → COMMIT Upserts a project, then upserts every repo GitHub returned for it. Classic UPSERT pattern via ON CONFLICT.
getRepositories GET /api/v1/repos SELECT r.*, p.project_name FROM repositories r JOIN projects p ON r.project_id=p.project_id JOIN users u ON p.organization_id=u.organization_id WHERE u.user_id=$1 ORDER BY r.last_synced_at DESC 3-table JOIN, scoped to the caller's org via the users→projects→repositories chain.

4.4 controllers/manifest.controller.js

Function Route SQL What it does
ingestManifest POST /api/v1/manifests/ingest SELECT r.repo_name, p.organization_id FROM repositories r JOIN projects p ON ... JOIN users u ON ... WHERE r.repository_id=$1 AND u.user_id=$2 (auth check) → INSERT INTO dependency_files (...) VALUES (...) RETURNING * Downloads a manifest file from GitHub, uploads it to Supabase Storage, then records the storage pointer in Postgres. If the Postgres insert fails after the file already uploaded, it deletes the orphaned storage file (and if even that fails, logs a retry row into an ad-hoc cleanup_tasks table it creates on the fly with CREATE TABLE IF NOT EXISTS).

4.5 controllers/parser.controller.js

Function Route SQL What it does
extractDependencies (migration step, runs every call) POST /api/v1/parser/extract ALTER TABLE dependencies ADD COLUMN IF NOT EXISTS original_constraint ... / ... package_manager ..., ALTER TABLE dependencies DROP CONSTRAINT IF EXISTS ..., then a DO $$ ... END $$ block that adds a new named UNIQUE (repository_id, package_name, package_manager) constraint if missing Self-healing schema migration — see §4.6 note below.
extractDependencies (main logic) same route SELECT storage_path, repository_id, file_name, package_manager FROM dependency_files WHERE file_id=$1 → SELECT 1 FROM users u JOIN projects p ... JOIN repositories r ... WHERE u.user_id=$1 AND r.repository_id=$2 (auth check) → SELECT r.repo_name, o.domain, o.organization_name FROM repositories r JOIN projects p ... JOIN organizations o ... WHERE r.repository_id=$1 → loop: INSERT INTO dependencies (...) VALUES (...) ON CONFLICT (repository_id, package_name, package_manager) DO UPDATE SET ... RETURNING * → UPDATE dependency_files SET last_scanned=NOW() WHERE file_id=$1, all inside BEGIN/COMMIT Downloads the manifest from Storage, parses package.json/requirements.txt (in JS, not SQL), then upserts every parsed dependency.

4.6 note — why the migration exists: dependencies originally had a UNIQUE (repository_id, package_name) constraint (per docs/DB_SCHEMA.md). Parsing later added a package_manager column so the same package name could exist once per manager (e.g. an npm requests and a pip requests in the same repo). Rather than editing the schema doc and re-running init-db.js (which would drop all data), the controller migrates itself defensively on every call using IF NOT EXISTS/IF EXISTS guards — worth knowing because it means the live schema on your DB differs slightly from what docs/DB_SCHEMA.md shows, until someone reconciles the two.

4.7 controllers/scanner.controller.js — the security scan engine

Function Route SQL What it does
runSecurityScan POST /api/v1/scanner/scan BEGIN → SELECT r.repository_id FROM repositories r JOIN projects p ... JOIN users u ... WHERE r.repository_id=$1 AND u.user_id=$2 FOR UPDATE OF r (ownership check + row lock) → SELECT d.dependency_id, c.cve_id, c.severity, c.cve_number FROM dependencies d INNER JOIN cves c ON c.description ILIKE '%' || d.package_name || '%' WHERE d.repository_id=$1 AND d.is_deprecated=false (the actual scan) → loop: INSERT INTO dependency_vulnerabilities (...) VALUES (...) ON CONFLICT (dependency_id, cve_id) DO NOTHING RETURNING id, and if that inserted a new row, INSERT INTO security_alerts (...) VALUES (...) → COMMIT Cross-references every non-deprecated dependency in a repo against the cves table with a set-based JOIN (not a loop-per-dependency in JS), then records new (dependency, CVE) matches into the junction table and raises one alert per new match. FOR UPDATE OF r means two people scanning the same repo at once serialize instead of racing — see §5.2.
getAlerts GET /api/v1/scanner/alerts SELECT sa.*, r.repository_id, r.repo_name, d.package_name, d.current_version FROM security_alerts sa JOIN repositories r ... JOIN projects p ... JOIN users u ... JOIN dependencies d ... WHERE u.user_id=$1 [AND sa.repository_id=$2] 4-table JOIN; the repository_id filter clause is built dynamically only when the query param is present.
resolveAlert PATCH /api/v1/scanner/alerts/:alert_id/resolve UPDATE security_alerts sa SET status='resolved', resolved_at=NOW() FROM repositories r JOIN projects p ... JOIN users u ... WHERE sa.repository_id=r.repository_id AND u.user_id=$1 AND sa.alert_id=$2 RETURNING sa.alert_id, sa.status, sa.resolved_at An UPDATE ... FROM — joins in the WHERE to make sure the caller's org actually owns the alert before flipping its status.

4.8 controllers/user.controller.js

Function Route SQL What it does
getCurrentUser GET /api/v1/users/me SELECT u.*, o.organization_name FROM users u LEFT JOIN organizations o ON u.organization_id=o.organization_id WHERE u.user_id=$1 LEFT JOIN (not JOIN) because a brand-new user has organization_id = NULL and should still get a profile back, just with organization_name: null.
updateUserRole PUT /api/v1/users/:userId/role Transactional row lock, update, and audit insert Admins can change roles platform-wide; managers can change roles in their org, but cannot grant or alter admin accounts.

4.9 controllers/health.controller.js

SELECT NOW() AS current_time; — a trivial liveness probe used by GET /api/v1/health to confirm the pool can actually reach Postgres (as opposed to just being configured).

4.10 controllers/ai.controller.js & services/ai.service.js (Phase 9)

Function Route SQL What it does
generateRepoReport POST /api/v1/ai/reports/:repoId SELECT r.repository_id, r.repo_name, r.language, p.project_id, p.organization_id FROM repositories r JOIN projects p ON r.project_id=p.project_id JOIN users u ON (u.role='admin' OR p.organization_id=u.organization_id) WHERE r.repository_id=$1 AND u.user_id=$2 → SELECT sa.alert_id, sa.severity, sa.message, sa.status, d.package_name, d.current_version, d.package_manager FROM security_alerts sa JOIN dependencies d ON sa.dependency_id=d.dependency_id WHERE sa.repository_id=$1 AND sa.status='open' ORDER BY sa.created_at DESC LIMIT 50 → (FastAPI call) → INSERT INTO reports (organization_id, report_type, content) VALUES ($1, 'security_advisor', $2) RETURNING * Aggregates repository and security alert data, combines it with Neo4j graph context, dispatches it to the FastAPI microservice, and saves the generated advisory into reports.
listRepoReports GET /api/v1/ai/reports/:repoId SELECT r.repo_name, p.organization_id FROM repositories r JOIN projects p ON r.project_id=p.project_id JOIN users u ON (u.role='admin' OR p.organization_id=u.organization_id) WHERE r.repository_id=$1 AND u.user_id=$2 → SELECT report_id, organization_id, report_type, content, generated_at FROM reports WHERE organization_id=$1 AND report_type='security_advisor' ORDER BY generated_at DESC LIMIT 20 Retrieves previously generated security advisor reports for a repository.

4.11 What has no SQL at all

controllers/github.controller.js — pure GitHub REST API proxying (axios), no DB access. Listed here so you don't go looking for queries that aren't there.


5. Transactions & Concurrency

Every multi-statement write in the app is wrapped BEGIN → work → COMMIT, with a catch block doing ROLLBACK, using a dedicated client from pgPool.connect() (never the shared pool directly — mixing transaction statements across pooled connections would corrupt them).

sequenceDiagram
    participant C as Controller
    participant P as pgPool
    participant DB as Postgres
    C->>P: connect()
    P-->>C: client
    C->>DB: BEGIN
    C->>DB: INSERT / UPDATE / SELECT ... (multiple)
    alt all succeed
        C->>DB: COMMIT
    else any throws
        C->>DB: ROLLBACK
    end
    C->>P: client.release()
Loading

5.1 Why createOrg is transactional

Creating the org row but failing to attach the creator (users.organization_id) would leave every downstream org-scoped query (repos, manifests, alerts — all of them join through users.organization_id) silently broken for that user. Both writes commit together or not at all.

5.2 Row locking demo — runSecurityScan

SELECT ... FOR UPDATE OF r locks the specific repositories row for the duration of the transaction. If a second request tries to scan the same repository concurrently, its own SELECT ... FOR UPDATE blocks until the first transaction commits/rolls back — preventing two scans from racing to insert duplicate alerts. This is the codebase's live demonstration of pessimistic locking / isolation.

5.3 Documented-but-not-yet-coded demos

docs/DB_SCHEMA.md §5 describes two further concurrency demos that reuse dependencies and security_alerts but aren't implemented as actual runnable code anywhere in api-gateway/ yet:

  • Isolation-level demo: two transactions both UPDATE dependencies SET latest_version=... WHERE repository_id=X, compared under READ COMMITTED vs SERIALIZABLE.
  • Deadlock demo: Transaction A locks a dependencies row then a security_alerts row; Transaction B locks them in reverse order.

Good candidates if you need another concurrency talking point for viva — currently theory-only.


6. Indexes

Defined in api-gateway/utils/indexes.sql, applied by utils/run-indexes.js (wraps the whole file in one BEGIN/COMMIT):

CREATE INDEX IF NOT EXISTS idx_dependencies_package ON dependencies(package_name);
CREATE INDEX IF NOT EXISTS idx_alerts_repository   ON security_alerts(repository_id);
CREATE INDEX IF NOT EXISTS idx_cves_number         ON cves(cve_number);
Index Speeds up Used by
idx_dependencies_package filtering dependencies by package_name the ILIKE join in runSecurityScan
idx_alerts_repository "all alerts for repo X" getAlerts (when repository_id filter is passed)
idx_cves_number CVE lookup by cve_number future/planned CVE-number lookups

Proof the index actually helps: utils/explain-index-proof.js bulk-loads 50,000 sample dependency rows via COPY, then runs EXPLAIN ANALYZE SELECT * FROM dependencies WHERE package_name = $1 twice — once with enable_indexscan/enable_bitmapscan forced off (baseline Seq Scan), once after creating the index (Index Scan/Bitmap Heap Scan). Run it yourself:

cd api-gateway
SEED_OPT_IN=true node utils/explain-index-proof.js

7. Setup & Admin Scripts (not hit by the API)

These run from your terminal, not from any frontend action — but they contain real SQL worth knowing for viva:

Script SQL technique What it does
utils/init-db.js DROP TABLE ... CASCADE (16 app tables) → executes the full DDL block from docs/DB_SCHEMA.md → creates the Supabase Auth sync trigger and backfills users Rebuilds the schema from scratch. Destructive — see §5 caveat below.
utils/seed-cves.js DELETE FROM dependency_vulnerabilities/security_alerts/cves → COPY cves (...) FROM STDIN WITH (FORMAT csv, HEADER true) Bulk-loads up to 5,000 synthetic CVE rows using Postgres's COPY protocol (streaming, not per-row INSERT — the standard way to bulk-load in Postgres). Deletes dependents first to respect the FK/unique constraints on repeated runs. Refuses to run in NODE_ENV=production and requires an explicit SEED_OPT_IN=true flag.
utils/run-indexes.js runs indexes.sql inside one transaction Deploys the 3 performance indexes from §6.
utils/explain-index-proof.js COPY (50k rows) + EXPLAIN ANALYZE + SET enable_indexscan/enable_bitmapscan The before/after index-performance demo described in §6.

⚠️ init-db.js is a full destructive reset — it drops all app tables (CASCADE) before recreating them. Never point it at a database with data you care about without a backup.


8. Endpoint → SQL Cheat Sheet

One line per route — use this as your fast-reference table while studying.

Method Route Controller · Function Tables touched
GET /api/v1/health health.controller.js · checkHealth none (SELECT NOW())
POST /api/v1/orgs org.controller.js · createOrg organizations, users
GET /api/v1/orgs org.controller.js · getOrgs organizations
GET /api/v1/users/me user.controller.js · getCurrentUser users, organizations
PUT /api/v1/users/:userId/role user.controller.js · updateUserRole users
POST /api/v1/repos/sync repo.controller.js · syncRepositories projects, repositories
GET /api/v1/repos repo.controller.js · getRepositories repositories, projects, users
POST /api/v1/manifests/ingest manifest.controller.js · ingestManifest repositories, projects, users, dependency_files
POST /api/v1/parser/extract parser.controller.js · extractDependencies dependency_files, users, projects, repositories, organizations, dependencies
POST /api/v1/scanner/scan scanner.controller.js · runSecurityScan repositories, projects, users, dependencies, cves, dependency_vulnerabilities, security_alerts
GET /api/v1/scanner/alerts scanner.controller.js · getAlerts security_alerts, repositories, projects, users, dependencies
PATCH /api/v1/scanner/alerts/:alert_id/resolve scanner.controller.js · resolveAlert security_alerts, repositories, projects, users
POST/GET /api/v1/github/* github.controller.js (no SQL — pure GitHub API proxy)
POST /api/v1/ai/reports/:repoId ai.controller.js · generateReport repositories, projects, users, security_alerts, dependencies, reports
GET /api/v1/ai/reports/:repoId ai.controller.js · getReports repositories, projects, users, reports

Generated from a direct read of api-gateway/{controllers,utils,config,routes} as of the code on the sec-scanner branch. If a controller changes, this doc will drift — re-run the audit rather than trusting it blindly during a viva.