Polyglot DevOps Intelligence Platform

A multi-tenant dependency and vulnerability intelligence platform built as a DBMS course project. It syncs GitHub repositories, ingests their dependency manifests, cross-references them against a CVE dataset with set-based SQL, mirrors the result into a graph database for blast-radius analysis, and turns it all into AI-written security reports.

📑 Table of Contents

What this project is
Key features
Tech stack
System architecture
How the platform works end to end
Database design — ER diagram, relational schema, normalization, indexes, triggers, transactions
Graph database design (Neo4j)
UML diagrams
Authentication and RBAC
AI service
API reference
Frontend
DBMS concepts demonstrated
Project structure
Getting started
Testing
Troubleshooting
Documentation index
License

🎯 What this project is

Modern software depends on hundreds of third-party packages, and one vulnerable package can put every repository that uses it, directly or through a chain of other packages, at risk. This platform answers three questions for an organization:

Question	Answered by
Which of my repositories use a vulnerable package?	Relational SQL: joins across `dependencies`, `cves` and the `dependency_vulnerabilities` junction table
If this CVE lands, how far does it spread?	Graph traversal in Neo4j: `Repository → Package → Package → CVE`
What should I do about it, in plain language?	AI service: risk score, blast radius summary and a remediation plan

The project is deliberately polyglot: one problem, three data models, each used where it is strongest.

Data model	Store	Why it is used here
Relational (tabular)	Supabase PostgreSQL, queried with raw SQL, no ORM	Users, organizations, repositories, alerts. Joins, transactions, locking and indexing are all hand-written so they can be demonstrated
Graph	Neo4j	"Package A depends on B depends on C" is a deep traversal. It is awkward as a recursive SQL CTE and natural as a Cypher path
Object storage	Supabase Storage	Raw manifest files (`package.json`, `requirements.txt`) kept outside the database

✨ Key features

GitHub integration: sign in with GitHub or Google (Supabase Auth), list and sync repositories into projects.
Manifest ingestion and parsing: upload package.json / requirements.txt to storage, then extract dependencies into the relational schema.
Transactional security scanner: set-based SQL join of dependencies against CVEs, with row-level locking, scan history, notifications and audit trail.
Graph analytics: blast radius per CVE, circular dependency detection, and shared package exposure across repositories.
AI security advisor: risk score, executive summary and remediation playbook per repository, with an LLM engine and a deterministic fallback.
Multi-tenant RBAC: six roles, a permission matrix, tenant isolation on every resource, and an audit log with secret redaction.
Analytical dashboard: 30-day scan trends using DATE_TRUNC and a 7-day rolling window function, severity breakdowns and charts.
Database Lab: live EXPLAIN ANALYZE (index scan vs forced sequential scan), a commit/rollback transaction demo, and a deadlock/concurrency demo.
One-click demo personas: six pre-seeded users, one per role, for demonstrating role-based dashboards.

🧰 Tech stack

Frontend

Backend and orchestration

Data and infrastructure

AI

Identity and external services

Testing and tooling

🏗️ System architecture

Three services, each with one job. The API gateway is the only service that holds database credentials.

flowchart LR
    classDef fe fill:#20232A,stroke:#61DAFB,color:#61DAFB,stroke-width:2px
    classDef gw fill:#1B2E1F,stroke:#8CC84B,color:#8CC84B,stroke-width:2px
    classDef ai fill:#0B2B29,stroke:#00E5C7,color:#00E5C7,stroke-width:2px
    classDef db fill:#0F2540,stroke:#3B82F6,color:#7FB2FF,stroke-width:2px
    classDef ext fill:#2B1A3A,stroke:#B388FF,color:#D4BBFF,stroke-width:2px

    subgraph Browser["Browser"]
        UI["React 19 dashboard<br/>Vite, Tailwind, Zustand<br/>:5173"]:::fe
    end

    subgraph Gateway["API Gateway (Node 20, Express 5) :4000"]
        MW["Middleware<br/>requireAuth, requirePermission"]:::gw
        CT["Routes, controllers, services<br/>raw SQL and Cypher"]:::gw
        MW --> CT
    end

    subgraph AIsvc["AI Service (FastAPI) :8000"]
        GEN["Report generator<br/>LLM engine + heuristic fallback"]:::ai
    end

    subgraph Data["Data and storage"]
        PG[("Supabase PostgreSQL<br/>relational data")]:::db
        ST[("Supabase Storage<br/>manifest files")]:::db
        AU["Supabase Auth<br/>JWT, OAuth"]:::db
        N4[("Neo4j<br/>dependency graph")]:::db
    end

    subgraph Ext["External"]
        GH(("GitHub API")):::ext
        OR(("OpenRouter LLM")):::ext
    end

    UI -- "REST + Supabase JWT" --> MW
    UI -- "OAuth login" --> AU
    MW -. "verify JWT via JWKS" .-> AU
    CT -- "raw SQL, transactions" --> PG
    CT -- "upload and download" --> ST
    CT -- "Cypher" --> N4
    CT -- "repos and manifests" --> GH
    CT -- "POST /ai/generate-report<br/>x-api-key" --> GEN
    GEN -- "chat completion" --> OR

Rules that keep the design clean

The frontend talks only to the gateway. It never calls the AI service or any database directly (it uses Supabase only for sign-in).
The AI service is stateless: it receives alerts and graph context in the request body and returns a report. It reads and writes no database.
Postgres, Neo4j and Storage credentials exist only in api-gateway/.env.

Deployment view

flowchart TB
    subgraph Dev["Developer machine / host"]
        F["frontend<br/>npm run dev :5173"]
        G["api-gateway<br/>npm run dev :4000"]
        A["ai-service<br/>uvicorn :8000"]
    end
    subgraph Compose["docker-compose.yml (optional local data stores)"]
        LP[("postgres :5432")]
        LN[("neo4j :7474 / :7687")]
    end
    subgraph Cloud["Managed cloud"]
        SB[("Supabase<br/>Postgres, Auth, Storage")]
        AN[("Neo4j Aura")]
        OR(("OpenRouter"))
    end
    F --> G
    G --> A
    G --> SB
    G --> AN
    A --> OR
    G -. "local alternative" .-> LP
    G -. "local alternative" .-> LN

🔄 How the platform works end to end

The ingestion and analysis pipeline

flowchart LR
    A["1. Sign in<br/>GitHub / Google"] --> B["2. Sync repositories<br/>POST /repos/sync"]
    B --> C["3. Ingest manifest<br/>POST /manifests/ingest<br/>file to Supabase Storage"]
    C --> D["4. Parse dependencies<br/>POST /parser/extract<br/>rows in dependencies"]
    D --> E["5. Security scan<br/>POST /scanner/scan<br/>SQL join with cves"]
    E --> F["6. Graph sync<br/>automatic after scan<br/>MERGE into Neo4j"]
    F --> G["7. Graph analytics<br/>blast radius, cycles,<br/>shared packages"]
    E --> H["8. AI report<br/>POST /ai/reports/:repoId"]
    G --> H
    H --> I["9. Dashboard<br/>alerts, trends, reports"]
    E --> I

Step	What happens	Where it lives
Sync	GitHub repositories are upserted into `repositories` under a project, using `ON CONFLICT` so re-syncing is idempotent	repo.controller.js, github.controller.js
Ingest	The manifest is fetched from GitHub, stored in Supabase Storage, and a row is added to `dependency_files`	manifest.controller.js
Parse	`package.json` (npm) and `requirements.txt` (pip) are parsed and upserted into `dependencies`	parser.controller.js
Scan	A transaction locks the repository row, joins dependencies against CVEs, records matches and raises alerts	scanner.controller.js, scanner.service.js
Graph	After a successful scan, the repository's packages and CVE links are mirrored into Neo4j in the background	graph.controller.js
Report	Alerts plus graph context go to the AI service; the result is stored in `reports`	ai.service.js

Note on CVE matching. The project has no real package-to-CVE feed. The scanner matches a CVE to a dependency when the package name appears in the CVE description (ILIKE). This is intentional: it demonstrates set-based joins, junction tables and idempotent inserts, which is what the course grades. A production scanner would match on ecosystem, package and version ranges.

Security scan sequence (one transaction)

sequenceDiagram
    autonumber
    actor U as Analyst
    participant FE as Frontend
    participant GW as API Gateway
    participant PG as PostgreSQL
    participant N4 as Neo4j

    U->>FE: Click "Run scan"
    FE->>GW: POST /scanner/scan {repository_id}
    GW->>GW: requireAuth, requirePermission(scanner.run)
    GW->>PG: BEGIN
    GW->>PG: verifyRepoAccess (tenant check)
    GW->>PG: SELECT ... FROM repositories FOR UPDATE
    Note right of PG: Concurrent scans of the same repo wait here
    GW->>PG: INSERT scan_runs (status running)
    GW->>PG: SELECT dependencies JOIN cves
    GW->>PG: INSERT dependency_vulnerabilities ON CONFLICT DO NOTHING
    GW->>PG: INSERT security_alerts for newly detected pairs
    GW->>PG: UPDATE scan_runs SET status = completed
    GW->>PG: INSERT audit_logs (security.scan_executed)
    GW->>PG: COMMIT
    GW-->>FE: 200 matches evaluated, new alerts raised
    GW--)N4: background syncGraphForRepo (MERGE)
    Note over GW,PG: On error: ROLLBACK, then scan_runs marked failed

AI report sequence

sequenceDiagram
    autonumber
    participant FE as Frontend
    participant GW as API Gateway
    participant PG as PostgreSQL
    participant N4 as Neo4j
    participant AI as AI Service
    participant LLM as OpenRouter

    FE->>GW: POST /ai/reports/:repoId
    GW->>GW: requirePermission(reports.generate)
    GW->>PG: verify repository access, load open alerts (limit 50)
    GW->>N4: blast radius and dependency cycles
    GW->>AI: POST /ai/generate-report (x-api-key)
    alt OpenRouter key configured
        AI->>LLM: chat completion (Gemini 2.0 Flash)
        LLM-->>AI: Markdown report
    else no key, or LLM failed
        AI->>AI: deterministic heuristic engine
    end
    AI-->>GW: report, summary, risk_score, action_plan, engine_used
    GW->>PG: INSERT INTO reports
    GW-->>FE: 200 report

🗃️ Database design

The relational schema lives in docs/DB_SCHEMA.md (the DDL that init-db.js executes) and in api-gateway/migrations/ (additive scan history, audit, RBAC and lab tables).

Entity-relationship diagram

erDiagram
    ORGANIZATIONS ||--o{ USERS : "employs"
    ORGANIZATIONS ||--o{ TEAMS : "owns"
    ORGANIZATIONS ||--o{ PROJECTS : "owns"
    ORGANIZATIONS ||--o{ REPORTS : "receives"
    ORGANIZATIONS ||--o{ AUDIT_LOGS : "scopes"

    USERS ||--o{ TEAMS : "manages"
    USERS ||--o{ TEAM_MEMBERS : "joins"
    TEAMS ||--o{ TEAM_MEMBERS : "has"
    USERS ||--o{ PROJECTS : "creates"
    USERS ||--o{ PROJECT_MEMBERS : "assigned to"
    PROJECTS ||--o{ PROJECT_MEMBERS : "has"
    USERS ||--o{ NOTIFICATIONS : "receives"
    USERS ||--o{ SCAN_RUNS : "initiates"
    USERS ||--o{ AUDIT_LOGS : "performs"

    PROJECTS ||--o{ REPOSITORIES : "contains"
    REPOSITORIES ||--o{ DEPENDENCY_FILES : "has manifests"
    REPOSITORIES ||--o{ DEPENDENCIES : "uses"
    REPOSITORIES ||--o{ SECURITY_ALERTS : "raises"
    REPOSITORIES ||--o{ SCAN_RUNS : "scanned by"

    DEPENDENCIES ||--o{ DEPENDENCY_VULNERABILITIES : "affected by"
    CVES ||--o{ DEPENDENCY_VULNERABILITIES : "affects"
    DEPENDENCIES ||--o{ SECURITY_ALERTS : "triggers"

    ORGANIZATIONS {
        int organization_id PK
        varchar organization_name
        varchar domain UK
        varchar subscription_plan
        timestamp created_at
    }
    USERS {
        uuid user_id PK "maps to Supabase auth.users"
        int organization_id FK
        varchar full_name
        varchar email UK
        varchar role "6 roles plus legacy admin and manager"
        timestamp created_at
        timestamp last_login
    }
    TEAMS {
        int team_id PK
        int organization_id FK
        varchar team_name
        uuid manager_id FK
        timestamp created_at
    }
    TEAM_MEMBERS {
        int team_member_id PK
        int team_id FK
        uuid user_id FK
        timestamp joined_at
    }
    PROJECTS {
        int project_id PK
        int organization_id FK
        varchar project_name
        uuid created_by FK
        timestamp created_at
    }
    PROJECT_MEMBERS {
        int project_id PK, FK
        uuid user_id PK, FK
        varchar role
        uuid added_by FK
        timestamptz created_at
    }
    REPOSITORIES {
        int repository_id PK
        int project_id FK
        varchar repo_name "unique with project_id"
        varchar default_branch
        varchar language
        timestamp last_synced_at
    }
    DEPENDENCY_FILES {
        int file_id PK
        int repository_id FK
        varchar file_name
        varchar package_manager
        text storage_path "pointer to Supabase Storage"
        timestamp last_scanned
    }
    DEPENDENCIES {
        int dependency_id PK
        int repository_id FK
        varchar package_name "unique with repository_id and package_manager"
        varchar current_version
        varchar latest_version
        varchar license
        boolean is_deprecated
        varchar original_constraint
        varchar package_manager
        timestamp introduced_at
    }
    CVES {
        int cve_id PK
        varchar cve_number UK
        varchar severity
        numeric cvss_score
        text description
        date published_date
    }
    DEPENDENCY_VULNERABILITIES {
        int id PK
        int dependency_id FK "unique with cve_id"
        int cve_id FK
        varchar status "open, patched, ignored"
        timestamp detected_at
        varchar fixed_version
    }
    SECURITY_ALERTS {
        int alert_id PK
        int repository_id FK
        int dependency_id FK
        varchar severity
        text message
        varchar status "open or resolved"
        timestamp created_at
        timestamp resolved_at
    }
    SCAN_RUNS {
        bigint scan_run_id PK
        int repository_id FK
        uuid initiated_by FK
        varchar status "running, completed, failed"
        int matches_evaluated
        int new_alerts_raised
        text error_message
        timestamptz started_at
        timestamptz finished_at
    }
    REPORTS {
        int report_id PK
        int organization_id FK
        varchar report_type
        text content "AI report as JSON"
        timestamp generated_at
    }
    NOTIFICATIONS {
        int notification_id PK
        uuid user_id FK
        varchar title
        text message
        varchar type
        boolean is_read
        timestamp created_at
    }
    AUDIT_LOGS {
        bigint audit_id PK
        int organization_id FK
        uuid actor_user_id FK
        varchar action
        varchar entity_type
        text entity_id
        varchar resource_type
        varchar resource_id
        varchar outcome
        jsonb before_data
        jsonb after_data
        timestamptz created_at
    }
    DBMS_LAB_ACCOUNTS {
        int account_id PK
        varchar account_name
        numeric balance "CHECK balance >= 0"
        timestamptz updated_at
    }

dbms_lab_accounts is intentionally isolated: it is a two-row table used only by the transaction and deadlock demos, so labs never touch real tenant data.

Relational schema, grouped by domain

flowchart TB
    classDef pk fill:#1f3a5f,stroke:#7fb2ff,color:#fff
    classDef junction fill:#3a2a5f,stroke:#b388ff,color:#fff
    classDef ops fill:#1b3a2a,stroke:#6ee7a8,color:#fff

    subgraph IAM["Identity and tenancy"]
        ORG["organizations<br/>PK organization_id<br/>UK domain"]:::pk
        USR["users<br/>PK user_id uuid<br/>FK organization_id<br/>UK email"]:::pk
        TM["teams<br/>PK team_id<br/>FK organization_id, manager_id"]:::pk
        TMM["team_members<br/>PK team_member_id<br/>FK team_id, user_id<br/>UK team_id,user_id"]:::junction
        PRJ["projects<br/>PK project_id<br/>FK organization_id, created_by"]:::pk
        PM["project_members<br/>PK project_id,user_id<br/>FK added_by"]:::junction
    end

    subgraph SRC["Source and dependencies"]
        REPO["repositories<br/>PK repository_id<br/>FK project_id<br/>UK project_id,repo_name"]:::pk
        DF["dependency_files<br/>PK file_id<br/>FK repository_id"]:::pk
        DEP["dependencies<br/>PK dependency_id<br/>FK repository_id<br/>UK repository_id,package_name,package_manager"]:::pk
    end

    subgraph SEC["Vulnerability intelligence"]
        CVE["cves<br/>PK cve_id<br/>UK cve_number"]:::pk
        DV["dependency_vulnerabilities<br/>PK id<br/>FK dependency_id, cve_id<br/>UK dependency_id,cve_id"]:::junction
        SA["security_alerts<br/>PK alert_id<br/>FK repository_id, dependency_id"]:::pk
    end

    subgraph OPS["Operations and analytics"]
        SR["scan_runs<br/>PK scan_run_id<br/>FK repository_id, initiated_by"]:::ops
        RP["reports<br/>PK report_id<br/>FK organization_id"]:::ops
        NT["notifications<br/>PK notification_id<br/>FK user_id"]:::ops
        AL["audit_logs<br/>PK audit_id<br/>FK organization_id, actor_user_id"]:::ops
        LAB["dbms_lab_accounts<br/>PK account_id"]:::ops
    end

    ORG --> USR
    ORG --> TM
    ORG --> PRJ
    TM --> TMM
    USR --> TMM
    PRJ --> PM
    USR --> PM
    PRJ --> REPO
    REPO --> DF
    REPO --> DEP
    DEP --> DV
    CVE --> DV
    REPO --> SA
    DEP --> SA
    REPO --> SR
    ORG --> RP
    USR --> NT
    ORG --> AL

Normalization

Decision	Normal form	Reason
`dependency_vulnerabilities` is a junction table	3NF	A dependency can have many CVEs and a CVE can affect many dependencies. Storing CVE data on `dependencies` would repeat descriptions per package
`cves` is separate from the junction	2NF	Severity, CVSS and description depend only on `cve_id`; merging would duplicate them per affected dependency
`dependencies` key is `(repository_id, package_name, package_manager)`	BCNF	Every other attribute depends on the whole key, and no non-key attribute determines part of it
`team_members` and `project_members` junction tables	3NF	Resolve user-to-team and user-to-project many-to-many relationships

Indexes

Index	Table and columns	Purpose
`idx_dependencies_package`	`dependencies(package_name)`	Package lookups in the scan join
`idx_alerts_repository`	`security_alerts(repository_id)`	Alerts per repository
`idx_cves_number`	`cves(cve_number)`	CVE lookup by number
`idx_scan_runs_repo_started`	`scan_runs(repository_id, started_at DESC)`	Scan history and trends
`idx_audit_logs_org_created`	`audit_logs(organization_id, created_at DESC)`	Tenant audit queries
`idx_audit_logs_actor_created`	`audit_logs(actor_user_id, created_at DESC)`	Per-actor audit queries
`idx_notifications_user_unread`	`notifications(user_id, created_at DESC) WHERE is_read = false`	Partial index for unread notifications
`idx_team_members_user`	`team_members(user_id)`	Membership lookups
`idx_project_members_user`, `idx_project_members_project`	`project_members`	RBAC project checks

Run node utils/explain-index-proof.js (adds about 50k demo rows) or the Database Lab page to see EXPLAIN (ANALYZE, BUFFERS) before and after an index.

Trigger: syncing Supabase Auth users

sequenceDiagram
    participant U as New user
    participant SA as Supabase Auth
    participant T as Trigger on_auth_user_created
    participant PU as public.users
    U->>SA: Sign in with GitHub or Google
    SA->>SA: INSERT INTO auth.users
    SA->>T: AFTER INSERT FOR EACH ROW
    T->>PU: handle_new_user() inserts user_id, email, full_name, role developer
    Note over T,PU: SECURITY DEFINER PL/pgSQL function

New users start as developer. Promote the first admin manually (see Getting started).

Transaction and concurrency design

Mechanism	Where used
`SELECT ... FOR UPDATE` row lock	Scanner serializes concurrent scans of one repository; team and role updates lock the rows they change
`BEGIN ... COMMIT / ROLLBACK`	Scan, sync, role change, team changes all run in explicit transactions
`ISOLATION LEVEL READ COMMITTED` and `SERIALIZABLE`	Database Lab transfer demo
Last-admin safeguard	Role demotion counts the remaining org admins inside the same transaction
`INSERT ... ON CONFLICT`	Idempotent upserts for sync, parse, and scan (about 20 call sites)
Batched `unnest()` inserts and `COPY` streams	Scan inserts and bulk seeding

sequenceDiagram
    participant A as Transaction A
    participant DB as PostgreSQL
    participant B as Transaction B
    A->>DB: BEGIN, lock repository 5 FOR UPDATE
    B->>DB: BEGIN, lock repository 5 FOR UPDATE
    Note over B,DB: B waits for A
    A->>DB: scan, insert alerts, COMMIT
    DB-->>B: lock granted
    B->>DB: scan finds nothing new (ON CONFLICT DO NOTHING), COMMIT

🕸️ Graph database design (Neo4j)

The gateway mirrors the relational data into Neo4j with idempotent MERGE statements, keyed on the same natural identifiers (Repository.repo_id, Package.name, CVE.cve_number).

flowchart LR
    R(("Repository<br/>repo_id, name"))
    P1(("Package<br/>name"))
    P2(("Package<br/>name"))
    P3(("Package<br/>name"))
    C(("CVE<br/>cve_number<br/>severity, cvss_score"))
    R -- "USES" --> P1
    R -- "USES" --> P2
    P1 -- "DEPENDS_ON" --> P3
    P2 -- "DEPENDS_ON" --> P3
    P3 -- "DEPENDS_ON" --> P2
    P3 -- "AFFECTED_BY" --> C

Node	Properties
`Repository`	`repo_id`, `name`
`Package`	`name`
`CVE`	`cve_number`, `severity`, `cvss_score`

Relationship	Meaning
`(:Repository)-[:USES]->(:Package)`	Direct dependency
`(:Package)-[:DEPENDS_ON]->(:Package)`	Package-to-package dependency (enables transitive paths and cycles)
`(:Package)-[:AFFECTED_BY]->(:CVE)`	Package is linked to a vulnerability

Blast radius: every repository that reaches a CVE within 5 hops

MATCH path = (r:Repository)-[:USES*1..5]->(p:Package)-[:AFFECTED_BY]->(c:CVE {cve_number: $cveNumber})
RETURN DISTINCT r.name AS repository, p.name AS vulnerablePackage,
       c.severity AS severity, c.cvss_score AS cvssScore, length(path) AS depth
ORDER BY depth ASC, c.cvss_score DESC

Circular dependencies

MATCH path = (p:Package)-[:DEPENDS_ON*1..6]->(p)
WITH p, relationships(path) AS rels, [n IN nodes(path) | n.name] AS cyclePath
RETURN DISTINCT p.name AS package, size(rels) AS cycleLength, cyclePath
ORDER BY cycleLength ASC LIMIT 20

Shared packages: packages used by two or more repositories, flagged high-risk when they carry a CVE

MATCH (r:Repository)-[:USES]->(p:Package)
WITH p, collect(DISTINCT r.name) AS repos, count(DISTINCT r) AS repoCount
WHERE repoCount >= $minRepos
OPTIONAL MATCH (p)-[:AFFECTED_BY]->(c:CVE)
RETURN p.name AS package, repos, repoCount, collect(DISTINCT c.cve_number) AS cves

For non-platform users every query adds WHERE r.repo_id IN $allowedIds OR r.name IN $allowedNames, so a tenant only sees its own subgraph.

📐 UML diagrams

Draw.io sources and exported images are in docs/. The diagrams below are the same models in Mermaid so they render on GitHub.

Use case diagram

flowchart LR
    V(["Visitor"])
    VW(["Viewer"])
    DV(["Developer"])
    SA(["Security Analyst"])
    PM(["Project Manager"])
    OA(["Org Admin"])
    PA(["Platform Admin"])

    subgraph SYS["Polyglot DevOps Intelligence Platform"]
        U1(["Sign in with GitHub or Google"])
        U2(["Create organization"])
        U3(["Connect GitHub and sync repositories"])
        U4(["Upload and parse manifests"])
        U5(["Run security scan"])
        U6(["View alerts and findings"])
        U7(["Resolve security alert"])
        U8(["Generate AI report"])
        U9(["View AI reports"])
        U10(["View graph analytics"])
        U11(["Manage project members"])
        U12(["Assign roles to users"])
        U13(["Inspect audit logs"])
        U14(["Configure platform and cross-org view"])
    end

    V --> U1
    VW --> U6
    VW --> U9
    VW --> U10
    DV --> U3
    DV --> U4
    DV --> U5
    DV --> U6
    DV --> U10
    SA --> U5
    SA --> U6
    SA --> U7
    SA --> U8
    SA --> U10
    PM --> U3
    PM --> U4
    PM --> U5
    PM --> U7
    PM --> U8
    PM --> U11
    OA --> U2
    OA --> U11
    OA --> U12
    OA --> U13
    OA --> U8
    PA --> U2
    PA --> U12
    PA --> U13
    PA --> U14

Exact permissions are in the RBAC matrix.

Domain class diagram

classDiagram
    direction LR
    class Organization {
        +int organization_id
        +string organization_name
        +string domain
        +string subscription_plan
    }
    class User {
        +uuid user_id
        +string full_name
        +string email
        +Role role
        +datetime last_login
    }
    class Team {
        +int team_id
        +string team_name
    }
    class Project {
        +int project_id
        +string project_name
    }
    class Repository {
        +int repository_id
        +string repo_name
        +string default_branch
        +string language
        +datetime last_synced_at
    }
    class DependencyFile {
        +int file_id
        +string file_name
        +string package_manager
        +string storage_path
    }
    class Dependency {
        +int dependency_id
        +string package_name
        +string current_version
        +string package_manager
        +bool is_deprecated
    }
    class CVE {
        +int cve_id
        +string cve_number
        +string severity
        +decimal cvss_score
    }
    class DependencyVulnerability {
        +string status
        +string fixed_version
        +datetime detected_at
    }
    class SecurityAlert {
        +int alert_id
        +string severity
        +string status
        +datetime resolved_at
    }
    class ScanRun {
        +bigint scan_run_id
        +string status
        +int matches_evaluated
        +int new_alerts_raised
    }
    class Report {
        +int report_id
        +string report_type
        +string content
    }
    class Notification {
        +int notification_id
        +string title
        +bool is_read
    }
    class AuditLog {
        +bigint audit_id
        +string action
        +string outcome
        +json before_data
        +json after_data
    }
    class Role {
        <<enumeration>>
        platform_admin
        org_admin
        project_manager
        security_analyst
        developer
        viewer
    }

    Organization "1" o-- "0..*" User : employs
    Organization "1" o-- "0..*" Team : owns
    Organization "1" o-- "0..*" Project : owns
    Organization "1" o-- "0..*" Report : receives
    Organization "1" o-- "0..*" AuditLog : scopes
    Team "0..*" -- "0..*" User : members
    Project "0..*" -- "0..*" User : assigned
    User --> Role
    Project "1" *-- "0..*" Repository : contains
    Repository "1" *-- "0..*" DependencyFile : manifests
    Repository "1" *-- "0..*" Dependency : uses
    Repository "1" *-- "0..*" SecurityAlert : raises
    Repository "1" *-- "0..*" ScanRun : history
    Dependency "1" -- "0..*" DependencyVulnerability
    CVE "1" -- "0..*" DependencyVulnerability
    Dependency "1" -- "0..*" SecurityAlert : triggers
    User "1" -- "0..*" ScanRun : initiates
    User "1" -- "0..*" Notification : receives
    User "1" -- "0..*" AuditLog : performs

Application class diagram (backend layers)

classDiagram
    direction TB
    class Routes {
        <<layer>>
        orgs, users, projects, repos
        github, manifests, parser
        scanner, graph, analytics
        ai, teams, platform
    }
    class AuthMiddleware {
        <<middleware>>
        +requireAuth(req, res, next)
        -verify JWT via Supabase JWKS
        -demo persona tokens (dev only)
    }
    class RoleMiddleware {
        <<middleware>>
        +requireRole(roles)
        +requirePermission(action)
    }
    class Controllers {
        <<layer>>
        scanner, repo, manifest, parser
        graph, analytics, org, user
        project, team, platform, ai, github
    }
    class ScannerService {
        <<service>>
        +performScan(client, repositoryId)
    }
    class AiService {
        <<service>>
        +generateRepoReport(repoId, userId)
        +listRepoReports(repoId, userId)
    }
    class RbacUtil {
        <<util>>
        +PERMISSION_MATRIX
        +hasPermission(role, action)
        +canAssignRole(caller, target)
        +getCallerContext(userId)
        +getCallerRepoScope(caller)
        +verifyRepoAccess(caller, repoId)
        +verifyAlertAccess(caller, alertId)
    }
    class AuditUtil {
        <<util>>
        +writeAudit(client, entry)
        +auditQuery(caller, filters)
    }
    class DbConfig {
        <<config>>
        +pgPool
        +getNeo4jSession()
    }
    class AiServiceApp {
        <<FastAPI app>>
        +POST /ai/generate-report
        +GET /health
    }
    class Generator {
        <<python module>>
        +generate_ai_report(req)
        +calculate_risk_score(req)
        +generate_heuristic_report(req)
    }

    Routes --> AuthMiddleware
    Routes --> RoleMiddleware
    Routes --> Controllers
    RoleMiddleware --> RbacUtil
    Controllers --> ScannerService
    Controllers --> AiService
    Controllers --> RbacUtil
    Controllers --> AuditUtil
    Controllers --> DbConfig
    ScannerService --> DbConfig
    AiService --> DbConfig
    AiService ..> AiServiceApp : HTTP
    AiServiceApp --> Generator

State diagrams

stateDiagram-v2
    direction LR
    [*] --> open : scan raises alert
    open --> resolved : analyst resolves (alerts.resolve)
    resolved --> [*]
    note right of open
        Re-scanning never duplicates an alert:
        ON CONFLICT DO NOTHING on (dependency_id, cve_id)
    end note

stateDiagram-v2
    direction LR
    [*] --> running : INSERT scan_runs
    running --> completed : COMMIT
    running --> failed : ROLLBACK, error_message stored
    completed --> [*]
    failed --> [*]

Activity diagram: signing in

flowchart TD
    S([Open /login]) --> Q{Sign-in method}
    Q -- "GitHub or Google" --> O[Supabase OAuth redirect]
    O --> T[Receive Supabase session JWT]
    T --> TR[DB trigger creates public.users row as developer]
    Q -- "Demo persona (local dev)" --> D[Store demo token and persona in localStorage]
    TR --> P[GET /users/me loads role and organization]
    D --> P
    P --> R{Role allows page?}
    R -- yes --> OK([Render dashboard page])
    R -- no --> X([Show restricted page])

🔐 Authentication and RBAC

Authentication

The browser signs in through Supabase Auth (GitHub or Google) and receives a JWT signed with an asymmetric key (ES256).
Every gateway request carries Authorization: Bearer <jwt>. auth.middleware.js verifies the signature against Supabase's JWKS endpoint and checks issuer and audience.
role.middleware.js loads the caller from the users table and checks the permission matrix. The role comes from the database, never from the request body.

flowchart TD
    REQ["Incoming request"] --> A{"Bearer token present?"}
    A -- no --> E401["401 Unauthorized"]
    A -- yes --> B{"JWT valid?<br/>signature, issuer, audience"}
    B -- no --> E401
    B -- yes --> C["Load caller from users table<br/>role, organization_id"]
    C --> D{"Profile found?"}
    D -- no --> E404["404 profile not synchronized"]
    D -- yes --> F{"hasPermission(role, action)?"}
    F -- no --> E403["403 Forbidden"]
    F -- yes --> G{"Resource belongs to caller's organization?<br/>(platform_admin bypasses)"}
    G -- no --> E404b["404 not found or not accessible"]
    G -- yes --> H["Run handler, write audit log if privileged"]

Roles and permission matrix

The six roles are non-hierarchical and permission-based. Legacy admin maps to org_admin and manager to project_manager. The authoritative spec is docs/RBAC_POLICY.md, and a unit test fails if the code and that table ever disagree.

Permission	`platform_admin`	`org_admin`	`project_manager`	`security_analyst`	`developer`	`viewer`
Platform config, cross-org audit	✅
Create organization	✅	✅
View organization, projects, repos	✅	✅	✅	✅	✅	✅
List org members	✅	✅	✅
Assign roles	✅ all	✅ not platform_admin
Manage project members	✅	✅	✅
Sync repositories	✅	✅	✅		✅
Upload and parse manifests	✅	✅	✅		✅
Run security scan	✅	✅	✅	✅	✅
View alerts, trends, graph analytics	✅	✅	✅	✅	✅	✅
Resolve alerts	✅	✅	✅	✅
Generate AI reports	✅	✅	✅	✅
View AI reports	✅	✅	✅	✅	✅	✅
View audit logs	✅ all orgs	✅ own org

Safeguards

Tenant isolation: every resource ID is verified through the chain organizations → projects → repositories → … before it is read or written. Neo4j queries are filtered to the caller's repositories.
No self-escalation: a user cannot change their own role.
Last-admin protection: the last org_admin of an organization cannot be demoted.
Audit trail: privileged actions write to audit_logs in the same transaction, with tokens and passwords redacted.
Demo tokens are opt-in: the demo-user-<id> tokens used for one-click personas are accepted only when DEMO_AUTH_ENABLED=true, never when NODE_ENV=production, and only for the six seeded persona IDs.

Demo personas

npm run db:seed-mock creates one user per role in the organization "Acme Cloud DevOps", plus sample repositories, dependencies, CVEs, alerts, scan history and a Neo4j graph. The login page shows them as one-click buttons when DEMO_AUTH_ENABLED=true.

Persona	Role	Email
Alex Vance	`platform_admin`	[email protected]
Sarah Chen	`org_admin`	[email protected]
Marcus Brody	`project_manager`	[email protected]
Elena Rostova	`security_analyst`	[email protected]
David Kim	`developer`	[email protected]
Victor Stone	`viewer`	[email protected]

🤖 AI service

A stateless FastAPI service that turns alerts and graph context into a security report. It does not scan, sync, or touch any database.

Endpoint	Purpose
`POST /ai/generate-report`	Generate a report. Guarded by `x-api-key` when `AI_SERVICE_API_KEY` is set
`GET /health`	Liveness and configuration flags

flowchart TD
    IN["ReportRequest<br/>repo_name, vulnerability_count,<br/>critical_cves, high_cves,<br/>alerts, graph_context"] --> K{"OPENROUTER_API_KEY<br/>starts with sk-or- ?"}
    K -- yes --> L["LLM engine<br/>Gemini 2.0 Flash via OpenRouter"]
    L --> V{"Reply longer than 50 chars<br/>and no error?"}
    V -- yes --> OUT1["engine_used = openrouter-llm"]
    V -- no --> H
    K -- no --> H["Heuristic engine<br/>deterministic rules"]
    H --> OUT2["engine_used = heuristic-rule-engine"]
    OUT1 --> RESP["ReportResponse<br/>report (Markdown), summary,<br/>risk_score 0-100, action_plan"]
    OUT2 --> RESP

Risk score

Signal	Points
Each CRITICAL CVE	+30
Each HIGH CVE	+15
Each other open alert	+4
Each blast-radius hit	+5
Any circular dependency	+15

The total is capped at 100. Levels: 75 and above is CRITICAL, 45 and above is HIGH, 20 and above is MODERATE, otherwise LOW.

📡 API reference

Base URL http://localhost:4000/api/v1. Interactive docs (Swagger UI) at http://localhost:4000/api-docs. All routes except /health require a Bearer token.

Area	Method and path	Permission
Health	`GET /health`	public
Organizations	`POST /orgs`, `GET /orgs`	`org.create`, `org.view`
Users	`GET /users/me`	any signed-in user
	`GET /users`	`members.list`
	`PUT /users/:userId/role`	`members.assign_role`
Projects	`GET /projects/:projectId/members`	`project.view`
	`POST` and `DELETE /projects/:projectId/members[/:userId]`	`project.manage_members`
Teams	`GET /teams`, `GET /teams/:teamId/members`	signed-in
	`POST /teams`, `POST` and `DELETE /teams/:teamId/members`	admin or manager roles
GitHub	`POST /github/token`, `GET /github/repos`	signed-in, `repos.sync`
Repositories	`POST /repos/sync`, `GET /repos`	`repos.sync`, `repos.view`
Manifests	`POST /manifests/ingest`	`manifests.upload`
Parser	`POST /parser/extract`	`dependencies.extract`
Scanner	`POST /scanner/scan`	`scanner.run`
	`GET /scanner/alerts`	`scanner.view`
	`PATCH /scanner/alerts/:alert_id/resolve`	`alerts.resolve`
Graph	`POST /graph/sync`	`scanner.run`
	`GET /graph/stats`, `GET /graph/vulnerable-paths`	`analytics.view`
Analytics	`GET /analytics/blast-radius/:cve_number`	`analytics.view`
	`GET /analytics/circular-deps`, `GET /analytics/shared-packages`	`analytics.view`
	`POST /analytics/seed-circular-demo`	`platform.config`, disabled in production
AI	`POST /ai/reports/:repoId`	`reports.generate`
	`GET /ai/reports/:repoId`	`reports.view`
Scan history	`GET /scans/history`, `GET /scans/trends`	`scanner.view`
Notifications	`GET /notifications`, `PATCH /notifications/:id/read`	signed-in
Audit	`GET /audit-logs`	`audit.view`
Database Lab	`GET /dbms/query-plan`, `POST /dbms/transfer-lab`, `POST /dbms/concurrency-lab`	signed-in

Responses use the envelope { "success": boolean, "message"?: string, "data"?: ... }.

🖥️ Frontend

flowchart TD
    ROOT["BrowserRouter"] --> L["/ Landing page"]
    ROOT --> LG["/login (AuthRoute)"]
    ROOT --> DL["/dashboard (ProtectedRoute + DashboardLayout)"]
    DL --> O["Overview<br/>KPIs, 30-day trend chart,<br/>severity posture"]
    DL --> ORG["organizations"]
    DL --> RP["repositories"]
    DL --> MF["manifests"]
    DL --> SC["scanner<br/>scan, alerts, AI report"]
    DL --> TM["team"]
    DL --> PF["profile"]
    DL --> GR["graph<br/>blast radius, cycles, shared packages"]
    DL --> IN["insights<br/>Database Lab, audit log"]
    DL --> RL["roles<br/>RBAC matrix, persona switcher"]

Page	What it shows	Visible to
Overview	Repository, alert and scan KPIs; 30-day scan trend (`DATE_TRUNC` plus 7-day rolling average); severity posture	everyone
Organizations	List and create organizations	admins
Repositories	Sync from GitHub, then ingest and scan	by permission
Manifests	Upload and parse dependency files	`developer` and above
Security Scanner	Run scans, filter and resolve alerts, generate AI reports	everyone (actions by permission)
Graph Analytics	Blast radius, circular dependencies, shared package exposure	everyone
Insights and Activity	Scan history, notifications, query plans, transaction and concurrency labs, audit log (admins only)	everyone
Team	Teams and members	admins and managers
Profile	Your role and organization	everyone
RBAC and Roles	Permission matrix and persona switcher	everyone

State is kept in two Zustand stores (auth, theme) plus a dashboard context for the selected repository. All HTTP goes through src/lib/api.ts, which attaches the current token per request.

🎓 DBMS concepts demonstrated

Concept	Where to see it
ER modelling and relational mapping	ER diagram, docs/DB_SCHEMA.md
Normalization (2NF, 3NF, BCNF)	Normalization
Constraints (PK, FK, UNIQUE, CHECK, DEFAULT)	`init-db.js` DDL, `dbms_lab_accounts.balance >= 0`
Many-to-many junction tables	`dependency_vulnerabilities`, `team_members`, `project_members`
Joins and set-based processing	Scanner join, `unnest()` batch inserts
Aggregation and window functions	Scan trends: `date_trunc`, `count() FILTER`, `avg() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`
Indexing, partial indexes, `EXPLAIN ANALYZE`	Indexes, Database Lab, `explain-index-proof.js`
Triggers and stored functions (PL/pgSQL)	`handle_new_user` on `auth.users`
Transactions (ACID)	Scan, sync and role changes; commit versus rollback lab
Isolation levels	Database Lab: `READ COMMITTED` and `SERIALIZABLE`
Locking, concurrency, deadlock	`FOR UPDATE` in the scanner; concurrency-scan-demo.js
Idempotent upserts	`INSERT ... ON CONFLICT`
Bulk loading	`COPY` streams (seed-cves.js)
JSONB	`audit_logs.before_data`, `after_data`
Schema migrations	api-gateway/migrations/ with a `schema_migrations` ledger
Row-level tenant isolation	Org-scoped joins, RBAC policy
Polyglot persistence	Postgres, Neo4j and object storage together
Graph model and traversal	Neo4j design, blast radius and cycle queries

Deeper write-ups: DBMS_CONCEPTS_AND_IMPLEMENTATION.md, DBMS_CORE_CONCEPTS_GUIDE.md, SQL_OPERATIONS.md.

🗂️ Project structure

polyglot-devops/
├── api-gateway/                 Node 20 + Express 5 (the only service with DB credentials)
│   ├── server.js                App entry, Swagger, global error handler
│   ├── routes/                  One router per area, mounted under /api/v1
│   ├── controllers/             Request handlers with raw SQL and Cypher
│   ├── services/                scanner.service.js, ai.service.js
│   ├── middlewares/             auth.middleware.js, role.middleware.js
│   ├── config/                  db.config.js (pg pool, Neo4j driver), supabase, swagger
│   ├── utils/                   rbac, audit, init-db, migrations, seeds, DB demo scripts
│   ├── migrations/              001_dbms_features.sql, 002_rbac_hardening.sql
│   ├── docs/                    Swagger JSDoc per area
│   └── tests/                   rbac.test.js (live DB) and unit/ (offline, node:test)
├── ai-service/                  FastAPI report generator
│   ├── main.py, api/routes/     Endpoints
│   ├── services/generator.py    LLM engine and heuristic engine
│   ├── core/security.py         Shared-secret dependency
│   ├── models/schemas.py        Pydantic request and response models
│   └── tests/                   pytest suite
├── frontend/                    React 19 + Vite dashboard
│   └── src/
│       ├── pages/               Landing, Login, dashboard/*
│       ├── components/          console panels, landing sections
│       ├── layouts/             DashboardLayout
│       ├── lib/                 api, authStore, types, mockUsers
│       └── test/, **/__tests__/ vitest suite
├── docs/                        PRD, TRD, schema, RBAC, SQL catalogue, ER and UML (draw.io)
├── phases/                      Phase_01 to Phase_10 build log
├── docker-compose.yml           Local Postgres and Neo4j
├── CLAUDE.md                    Contributor and tooling guide
└── README.md

🚀 Getting started

Prerequisites

Node.js 20+ and npm
Python 3.10+ (the Docker image uses 3.11). The AI service uses uv to manage Python and its dependencies
A Supabase project (Postgres, Auth, Storage), a Neo4j instance, and optionally an OpenRouter API key
A GitHub OAuth app if you want to sync real repositories

1. Install

cd api-gateway && npm install && cd ..
cd frontend    && npm install && cd ..
cd ai-service  && uv sync && cd ..   # creates .venv and installs the locked dependencies

2. Configure environment

Copy each service's .env.example to .env and fill in your own values. .env files are gitignored, so never commit them. Placeholder values in the examples are not real credentials; replace every one.

cp api-gateway/.env.example api-gateway/.env
cp ai-service/.env.example  ai-service/.env
cp frontend/.env.example    frontend/.env

Restart npm run dev after changing frontend/.env, since Vite reads it only at startup.

API gateway (`api-gateway/.env`)

Variable	Required	Default	Purpose
`PORT`		`4000`	Port the gateway listens on
`NODE_ENV`		`development`	Set to `production` to disable demo logins, seed scripts and the circular-demo endpoint
`DEMO_AUTH_ENABLED`		`false`	`true` allows `demo-user-<id>` tokens for the six demo personas. Local development only
`AI_SERVICE_DEV_URL`	✅	`http://localhost:8000`	Base URL of the AI service the gateway calls
`AI_SERVICE_PRO_URL`			AI service URL used in production builds
`AI_SERVICE_API_KEY`		empty	Sent as `x-api-key` to the AI service. Must match `ai-service/.env`
`SUPABASE_URL`	✅		Supabase project URL. JWTs are verified against `<SUPABASE_URL>/auth/v1/.well-known/jwks.json`
`SUPABASE_SERVICE_ROLE_KEY`	✅		Service-role key used for Supabase Storage (manifests). Keep it server-side only
`SUPABASE_STORAGE_BUCKET`		`manifests`	Storage bucket for uploaded manifest files
`SUPABASE_JWT_SECRET`			Only used by the local test scripts in `api-gateway/utils/`. Not needed to run the app
`DATABASE_URL`	✅		Postgres connection string. Use the IPv4 pooler (port `6543`) to avoid connection exhaustion
`NEO4J_URI`	✅	`bolt://localhost:7687`	Neo4j connection URI (`neo4j+s://…` for Aura)
`NEO4J_USERNAME`	✅	`neo4j`	Neo4j user
`NEO4J_PASSWORD`	✅	`password`	Neo4j password
`GITHUB_CLIENT_ID`	✅		GitHub OAuth app client ID (repository import)
`GITHUB_CLIENT_SECRET`	✅		GitHub OAuth app client secret
`SEED_OPT_IN`		`false`	Must be `true` (or pass `--seed-opt-in`) to run `seed-cves.js` or `explain-index-proof.js`. Both are destructive or add data
`SEED_CVE_COUNT`		`5000`	Number of sample CVEs `seed-cves.js` generates
`SEED_DEP_COUNT`		`50000`	Number of demo dependency rows `explain-index-proof.js` adds
`SCANNER_TEST_TOKEN`			Bearer token used by `utils/test-scanner-live.js`

AI service (`ai-service/.env`)

Variable	Default	Purpose
`PORT`	`8000`	Port the AI service listens on
`AI_SERVICE_API_KEY`	empty	Shared secret the gateway must send as `x-api-key`. Empty disables the check (local development only)
`AI_ALLOWED_ORIGINS`	empty	Comma-separated browser origins allowed to call the service. The gateway calls server-to-server, so leave empty
`OPENROUTER_API_KEY`	empty	Enables the LLM report engine. Must start with `sk-or-`. Empty uses the heuristic engine
`GEMINI_API_KEY`	empty	Optional Gemini key for fallback. Reported by `/health`; not yet called by the report code
`NEO4J_URI`		Reported by `/health` only. The AI service does not read the graph itself

Frontend (`frontend/.env`)

Variable	Required	Default	Purpose
`VITE_API_DEV_URL`	✅	`http://localhost:4000`	Gateway URL in development
`VITE_API_PRO_URL`			Gateway URL used when `VITE_MODE=production`
`VITE_MODE`		`development`	`development` or `production`
`VITE_SUPABASE_URL`	✅		Supabase project URL for sign-in
`VITE_SUPABASE_ANON_KEY`	✅		Supabase public anon key. Safe to ship to the browser

The frontend's VITE_ variables are embedded in the browser bundle, so never put a service-role key or database password in frontend/.env.

3. Initialize the database

cd api-gateway
node utils/init-db.js       # creates tables from docs/DB_SCHEMA.md and the auth trigger (DESTRUCTIVE)
npm run db:migrate          # additive: scan history, audit logs, RBAC tables, lab accounts
node utils/run-indexes.js   # performance indexes
SEED_OPT_IN=true node utils/seed-cves.js   # sample CVE dataset (DESTRUCTIVE to CVE tables)
npm run db:seed-mock        # optional: six demo personas, repos, alerts and graph

Script	Safe on an existing database?
`init-db.js`	No. Drops and recreates all tables
`seed-cves.js`	No. Replaces CVE-related tables. Needs `SEED_OPT_IN=true`, refuses in production
`run-indexes.js`, `npm run db:migrate`	Yes, idempotent
`npm run db:seed-mock`	Yes, idempotent; refuses in production
`explain-index-proof.js`	Adds about 50k demo rows to `dependencies`

On Supabase Cloud the pooled connection cannot create triggers on the auth schema. If init-db.js prints Could not create replication trigger, run this once in the Supabase SQL editor:

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.users (user_id, email, full_name, role)
  VALUES (new.id, new.email,
          COALESCE(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
          'developer');
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

Promote your first admin:

UPDATE users SET role = 'platform_admin' WHERE email = '[email protected]';

4. Run everything

# terminal 1
cd api-gateway && npm run dev                                   # http://localhost:4000
# terminal 2
cd ai-service  && uv run uvicorn main:app --reload --port 8000
# terminal 3
cd frontend    && npm run dev                                   # http://localhost:5173

Verify: curl localhost:4000/health should report postgres and neo4j as ONLINE, and curl localhost:8000/health should report up.

Running the AI service (FastAPI)

The AI service is a FastAPI app in ai-service/. It is managed with uv, which reads pyproject.toml and the locked uv.lock.

1. Install uv (once, if you don't have it)

brew install uv                     # macOS
# or: curl -LsSf https://astral.sh/uv/install.sh | sh

2. Install dependencies (creates ai-service/.venv and uses Python 3.10+; uv downloads one if needed)

cd ai-service
uv sync                 # runtime dependencies
uv sync --group dev     # also installs pytest and httpx for the test suite

3. Configure ai-service/.env as described in step 2. Only PORT, AI_SERVICE_API_KEY, AI_ALLOWED_ORIGINS and OPENROUTER_API_KEY are read.

4. Start the server

uv run uvicorn main:app --reload --port 8000     # development: restarts on code changes
uv run uvicorn main:app --port 8000              # plain run, no reload

uv run uses the project's .venv without activating it, so you never need source .venv/bin/activate.

URL	What it is
`http://localhost:8000/health`	Liveness and which optional keys are configured
`http://localhost:8000/docs`	Interactive Swagger UI for the endpoints
`http://localhost:8000/openapi.json`	OpenAPI schema
`POST http://localhost:8000/ai/generate-report`	Generates a security report. The gateway calls this

Test a report request by hand. If AI_SERVICE_API_KEY is set, send the same value in x-api-key:

curl -s -X POST http://localhost:8000/ai/generate-report \
  -H "content-type: application/json" \
  -H "x-api-key: $AI_SERVICE_API_KEY" \
  -d '{"repo_name":"demo","vulnerability_count":1,"high_cves":["CVE-2021-23337"],
       "alerts":[{"package_name":"lodash","severity":"HIGH","message":"CVE-2021-23337"}]}'

A 401 Invalid or missing service API key. response means the header is missing or doesn't match AI_SERVICE_API_KEY.

5. Run the tests (offline, no database or LLM needed)

uv run pytest

Running it in Docker (builds from ai-service/Dockerfile; the image installs from uv.lock)

cd ai-service
docker build -t polyglot-ai-service .
docker run --rm -p 8000:8000 --env-file .env polyglot-ai-service

Inside the container the server runs without --reload.

Optional local data stores: docker compose up -d starts Postgres (5432) and Neo4j (7474, 7687). The three application services still run on the host.

🧪 Testing

Suite	Command	Needs
Gateway unit tests (auth, RBAC matrix vs policy doc, analytics and graph tenant scoping, trends)	`cd api-gateway && npm run test:unit`	nothing, fully offline
Gateway RBAC and tenant-isolation suite (37 assertions)	`cd api-gateway && node tests/rbac.test.js`	live database from `.env`; creates and removes its own fixtures
Both gateway suites	`cd api-gateway && npm test`	live database
Frontend (auth store, API client, login, role-gated pages)	`cd frontend && npm test`	nothing
Frontend type check, lint, build	`npx tsc -b`, `npm run lint`, `npm run build`	nothing
AI service	`cd ai-service && uv run pytest`	nothing, LLM calls are stubbed

flowchart LR
    subgraph Offline["Offline (no services needed)"]
        U1["Gateway unit tests<br/>node:test"]
        U2["Frontend tests<br/>Vitest + Testing Library"]
        U3["AI service tests<br/>pytest"]
        U4["tsc, ESLint, build"]
    end
    subgraph Live["Needs live database"]
        L1["RBAC and tenant isolation<br/>37 assertions"]
    end
    Offline --> Live

🩺 Troubleshooting

Symptom	Fix
"Network Error" in the frontend	`VITE_API_DEV_URL` does not match the gateway port (4000). Restart Vite after editing
"Invalid or expired token"	`SUPABASE_URL` in `api-gateway/.env` must be correct; tokens are verified against its JWKS endpoint, no shared secret is needed
"User profile not synchronized"	The `handle_new_user` trigger is missing. Run the SQL block in step 3 and sign in again
New users cannot create anything	Everyone starts as `developer`. Promote the first admin with the SQL above
Role change not reflected	The profile is cached after first load. Reload the page
Demo persona buttons return 401	Set `DEMO_AUTH_ENABLED=true` in `api-gateway/.env` (local only) and run `npm run db:seed-mock`
AI service returns 401	`AI_SERVICE_API_KEY` differs between the gateway and the AI service, or is set on only one
AI reports say `heuristic-rule-engine`	`OPENROUTER_API_KEY` is missing, does not start with `sk-or-`, or the provider call failed. The fallback is by design
"Database transaction failed during sync"	Open DevTools, Network, the failed request, Response for the real Postgres error. Usually a deleted organization or a duplicate project name
Manifest ingest returns 404	The path must exist on the default branch and be relative to the repository root (for example `frontend/package.json`)
GitHub organization repos are missing	The organization restricts third-party OAuth apps. Approve the app in the organization's settings

📚 Documentation index

Document	Contents
docs/PRD.md, docs/TRD.md	Product and technical requirements
docs/DB_SCHEMA.md	Relational DDL, normalization notes, indexes
docs/SCHEMA-DIAGRAMS.md	ER, physical schema, concurrency and sequence diagrams
docs/SQL_OPERATIONS.md	Every SQL query by controller, CRUD matrix, index notes
docs/RBAC_POLICY.md	Roles, permission matrix, ownership rules, audit events
docs/FUNCTIONALITIES-AND-UML.md	Functionalities mapped to DBMS concepts, UML
docs/DBMS_CONCEPTS_AND_IMPLEMENTATION.md, docs/DBMS_CORE_CONCEPTS_GUIDE.md	Concept-by-concept implementation guides
`docs/.drawio`, `docs/uml_class_.jpeg`	ER (Chen and visual), UML domain, UML workflow and class diagrams
phases/	Phase 01 to 10 build log
CLAUDE.md	Commands, conventions and rules for contributors and tooling

📄 License

See LICENSE.

Name		Name	Last commit message	Last commit date
ai-service		ai-service
api-gateway		api-gateway
docs		docs
frontend		frontend
phases		phases
.gitignore		.gitignore
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
dbms_presentation_outline.md		dbms_presentation_outline.md
docker-compose.yml		docker-compose.yml

Latest commit

History

Folders and files

Repository files navigation