Database and Migrations

<h2 id="bkmrk-database-and-migrati">Database and Migrations — SecuScan v0.28.0</h2>

<h3 id="bkmrk-database-overview">Database Overview</h3> <table id="bkmrk-aspectdetail-dbmspos"> <thead><tr><th>Aspect</th><th>Detail</th></tr></thead> <tbody> <tr><td><strong>DBMS</strong></td><td>PostgreSQL 15 (alpine)</td></tr> <tr><td><strong>Multi-tenancy</strong></td><td>Row Level Security (RLS) with <code>app.current_tenant_id</code></td></tr> <tr><td><strong>Migrations</strong></td><td>26 SQL migrations embedded via <code>go:embed</code></td></tr> <tr><td><strong>Migration tool</strong></td><td>golang-migrate (auto-run on startup)</td></tr> <tr><td><strong>JSONB types</strong></td><td>Custom JSONMap/JSONArray types (sql.Scanner + driver.Valuer)</td></tr> <tr><td><strong>Indexes</strong></td><td>GIN indexes on JSONB columns for fast queries</td></tr> <tr><td><strong>Storage (k8s)</strong></td><td>PVC 10Gi (StatefulSet)</td></tr> </tbody> </table>

<h3 id="bkmrk-migration-catalogue">Migration Catalogue</h3> <table id="bkmrk-migrationdescription"> <thead><tr><th>Migration</th><th>Description</th><th>Key Tables</th></tr></thead> <tbody> <tr><td>000001</td><td>Base tables</td><td>tenants, users</td></tr> <tr><td>000002</td><td>Targets and scans</td><td>targets, scans, scan_results</td></tr> <tr><td>000003</td><td>Findings and reports</td><td>findings, reports</td></tr> <tr><td>000004</td><td>Scan scheduling</td><td>scan_schedules</td></tr> <tr><td>000005</td><td>Notifications</td><td>notifications</td></tr> <tr><td>000006</td><td>Report templates</td><td>report_templates</td></tr> <tr><td>000007</td><td>Code scanning</td><td>ssh_keys, code_repositories, code_scans, code_issues</td></tr> <tr><td>000008</td><td>Agents</td><td>agents, agent_inventories, agent_analyses</td></tr> <tr><td>000009</td><td>Trigger function fix</td><td>update_updated_at trigger</td></tr> <tr><td>000010</td><td>SSO columns</td><td>auth_provider, external_id on users</td></tr> <tr><td>000011</td><td>Billing tables + RLS</td><td>subscriptions, invoices, payment_methods</td></tr> <tr><td>000012</td><td>Language stats</td><td>language_stats JSONB on code_scans</td></tr> <tr><td>000013</td><td>Issue categories</td><td>category column on code_issues</td></tr> <tr><td>000014</td><td>AI analysis fields</td><td>ai_classification, ai_analysis on code_issues</td></tr> <tr><td>000015</td><td>Code scan enhancements</td><td>Additional code scan metadata</td></tr> <tr><td>000016</td><td>Pentest tables</td><td>pentest_authorizations, pentest_runs, pentest_findings</td></tr> <tr><td>000017</td><td>Findings refactor</td><td>CVE enrichment, CVSS scores</td></tr> <tr><td>000018</td><td>CVE enrichment</td><td>cve_id, cvss_score, exploitable fields</td></tr> <tr><td>000019</td><td>Markdown summary</td><td>summary_markdown on findings</td></tr> <tr><td>000020</td><td>Feature gates</td><td>feature_flags table</td></tr> <tr><td>000021</td><td>Billing quotas</td><td>Quota tracking columns</td></tr> <tr><td>000022</td><td>GitHub import</td><td>github_url, import_source on repositories</td></tr> <tr><td>000023</td><td>Plan unlimited + admin</td><td>Unlimited plan, admin user management</td></tr> <tr><td>000024</td><td>Admin tenants, stats, audit</td><td>audit_logs, admin_settings</td></tr> <tr><td>000025</td><td>AI report background</td><td>ai_report, ai_report_status on code_scans</td></tr> <tr><td>000026</td><td>Suppressions + app version</td><td>code_issue_suppressions, app_version/version_tag on code_scans</td></tr> </tbody> </table>

<h3 id="bkmrk-row-level-security-%28">Row Level Security (RLS)</h3> <p id="bkmrk-all-tenant-scoped-ta">All tenant-scoped tables use RLS to ensure complete data isolation:</p> <pre id="bkmrk----rls-pattern-used-"><code class="language-sql">-- RLS pattern used on all tenant tables ALTER TABLE table_name ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON table_name USING (tenant_id = current_setting('app.current_tenant_id')::UUID); </code></pre> <p id="bkmrk-the-go-api-sets-the-">The Go API sets the tenant context before every query:</p> <pre id="bkmrk-set-local-app.curren"><code class="language-sql">SET LOCAL app.current_tenant_id = 'tenant-uuid-here';</code></pre>

<h3 id="bkmrk-key-tables">Key Tables</h3>

<h4 id="bkmrk-core-tables">Core Tables</h4> <ul id="bkmrk-tenants%3A-organizatio"> <li><strong>tenants</strong>: Organization/company (plan, limits, billing)</li> <li><strong>users</strong>: User accounts (email, password hash, role, auth_provider)</li> <li><strong>targets</strong>: Scan targets (domain, IP, description)</li> <li><strong>scans</strong>: Network scans (type, status, progress, results)</li> <li><strong>findings</strong>: Vulnerability findings (severity, CVE, CVSS, status, AI classification)</li> </ul>

<h4 id="bkmrk-code-scanning-tables">Code Scanning Tables</h4> <ul id="bkmrk-ssh_keys%3A-ssh-keys-f"> <li><strong>ssh_keys</strong>: SSH keys for repository access (AES-GCM encrypted)</li> <li><strong>code_repositories</strong>: Git repositories (URL, branch, auth method)</li> <li><strong>code_scans</strong>: Code scan runs (status, results, language_stats, app_version, version_tag)</li> <li><strong>code_issues</strong>: Code security issues (severity, category, CWE, AI classification)</li> <li><strong>code_issue_suppressions</strong>: False positive suppressions with SHA256 fingerprint (v0.28.0)</li> </ul>

<h4 id="bkmrk-agent-tables">Agent Tables</h4> <ul id="bkmrk-agents%3A-deployed-age"> <li><strong>agents</strong>: Deployed agents (agent_id string + internal UUID)</li> <li><strong>agent_inventories</strong>: System inventories (OS, packages, services, ports as JSONB)</li> <li><strong>agent_analyses</strong>: Vulnerability analyses (AI-powered)</li> </ul>

<h4 id="bkmrk-billing-tables">Billing Tables</h4> <ul id="bkmrk-subscriptions%3A-strip"> <li><strong>subscriptions</strong>: Stripe subscriptions (plan, status, dates)</li> <li><strong>invoices</strong>: Invoice history</li> <li><strong>payment_methods</strong>: Stored payment methods</li> </ul>

<h4 id="bkmrk-admin-tables">Admin Tables</h4> <ul id="bkmrk-audit_logs%3A-audit-tr"> <li><strong>audit_logs</strong>: Audit trail for admin actions</li> <li><strong>admin_settings</strong>: Global platform settings</li> </ul>

<h3 id="bkmrk-jsonb-best-practices">JSONB Best Practices</h3> <ul id="bkmrk-default-values%3A-alwa"> <li><strong>Default values</strong>: Always <code>DEFAULT '[]'::jsonb</code> for arrays</li> <li><strong>GIN indexes</strong>: On JSONB columns for fast containment queries</li> <li><strong>Custom types</strong>: <code>JSONMap</code> and <code>JSONArray</code> in Go implement <code>sql.Scanner</code> and <code>driver.Valuer</code></li> <li><strong>Flexibility</strong>: Schema evolution without migrations (language_stats, scan_results)</li> </ul>

<h3 id="bkmrk-migration-best-pract">Migration Best Practices</h3> <ul id="bkmrk-embedding%3A-all-sql-f"> <li><strong>Embedding</strong>: All SQL files embedded in Go binary via <code>//go:embed</code></li> <li><strong>Numbering</strong>: Format <code>000XXX_description.up.sql</code> / <code>000XXX_description.down.sql</code></li> <li><strong>Idempotency</strong>: Always use <code>IF NOT EXISTS</code> for reproducibility</li> <li><strong>Down files</strong>: Always create down migration for rollback capability</li> <li><strong>RLS policies</strong>: Apply before inserting data to avoid subtle errors</li> <li><strong>Nullable FKs</strong>: Make foreign keys nullable when the code does not always populate them</li> </ul>

<h3 id="bkmrk-code-issue-suppressi">Code Issue Suppressions Table (v0.28.0)</h3> <pre id="bkmrk-create-table-if-not-"><code class="language-sql">CREATE TABLE IF NOT EXISTS code_issue_suppressions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), repository_id UUID NOT NULL REFERENCES code_repositories(id) ON DELETE CASCADE, fingerprint VARCHAR(64) NOT NULL, -- SHA256 hash reason VARCHAR(50) NOT NULL, -- false_positive, accepted_risk, wont_fix justification TEXT, suppressed_by UUID REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE UNIQUE INDEX ON code_issue_suppressions(tenant_id, repository_id, fingerprint); </code></pre>