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 (seedocs/SCHEMA_DIAGRAM.mdfor 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.
| 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 |
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.
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.
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"
teams/team_members now have organization-scoped CRUD in team.controller.js.
erDiagram
projects ||--o{ repositories : "repositories.project_id"
repositories ||--o{ dependency_files : "dependency_files.repository_id"
repositories ||--o{ dependencies : "dependencies.repository_id"
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.
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"
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).
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
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.
✅ = implemented and reachable via an API route ·
| 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.
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.
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.
| 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. |
| 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. |
| 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). |
| 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:
dependenciesoriginally had aUNIQUE (repository_id, package_name)constraint (perdocs/DB_SCHEMA.md). Parsing later added apackage_managercolumn so the same package name could exist once per manager (e.g. an npmrequestsand a piprequestsin the same repo). Rather than editing the schema doc and re-runninginit-db.js(which would drop all data), the controller migrates itself defensively on every call usingIF NOT EXISTS/IF EXISTSguards — worth knowing because it means the live schema on your DB differs slightly from whatdocs/DB_SCHEMA.mdshows, until someone reconciles the two.
| 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. |
| 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. |
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).
| 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. |
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.
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()
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.
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.
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 underREAD COMMITTEDvsSERIALIZABLE. - Deadlock demo: Transaction A locks a
dependenciesrow then asecurity_alertsrow; Transaction B locks them in reverse order.
Good candidates if you need another concurrency talking point for viva — currently theory-only.
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.jsThese 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.jsis 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.
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.