PostgreSQL Database
SecuSiem — PostgreSQL Database
SecuSiem uses PostgreSQL 16 as the primary database for storing tenant metadata, API keys, alerts, and usage data.
PostgreSQL is deployed as a StatefulSet in the secusiem-system namespace with:
- Image:
postgres:16-alpine
- Storage: 100Gi on
csi-cinder-high-speed
- CPU: 500m request / 2000m limit
- Memory: 1Gi request / 2Gi limit
- Liveness probe:
pg_isready (every 10s)
- Readiness probe:
pg_isready (every 5s)
Primary table representing client organizations.
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | Tenant UUID |
name | VARCHAR(255) | Organization name |
slug | VARCHAR(100) UNIQUE | URL-safe identifier |
loki_org_id | VARCHAR(100) UNIQUE | Loki multi-tenant ID (e.g., tenant-acme) |
s3_bucket | VARCHAR(255) UNIQUE | OVH S3 bucket name |
grafana_org_id | INTEGER | Grafana organization ID |
authentik_group_id | VARCHAR(100) | Authentik group ID |
pricing_tier | ENUM | starter, business, enterprise |
stripe_customer_id | VARCHAR(100) UNIQUE | Stripe customer ID |
stripe_subscription_id | VARCHAR(100) UNIQUE | Stripe subscription ID |
status | ENUM | active, suspended, trial, cancelled |
trial_ends_at | DATETIME | Trial period end date |
contact_email | VARCHAR(255) | Contact email |
contact_name | VARCHAR(255) | Contact name |
settings | JSON | Custom configuration |
extra_metadata | JSON | Additional metadata |
created_at | DATETIME | Creation date |
updated_at | DATETIME | Last update |
deleted_at | DATETIME | Soft delete timestamp |
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | UUID |
tenant_id | VARCHAR(36) FK | Reference to tenant |
key_hash | VARCHAR(255) UNIQUE | bcrypt hash of the key |
key_prefix | VARCHAR(20) | Prefix for identification (e.g., sk_live_AbCd...) |
name | VARCHAR(255) | Descriptive name |
scopes | JSON | Permissions (logs:write, logs:read, etc.) |
rate_limit_rpm | INTEGER | Requests per minute limit |
is_active | BOOLEAN | Active or revoked |
expires_at | DATETIME | Expiration date |
last_used_at | DATETIME | Last usage timestamp |
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | UUID |
tenant_id | VARCHAR(36) FK | Reference to tenant |
period_start | DATETIME | Period start |
period_end | DATETIME | Period end |
logs_ingested_mb | FLOAT | Ingested log volume (MB) |
logs_stored_gb | FLOAT | Stored volume (GB) |
alerts_generated | INTEGER | Number of alerts |
api_calls | INTEGER | Number of API calls |
claude_api_calls | INTEGER | Number of Claude analyses |
estimated_cost_usd | FLOAT | Estimated cost |
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | UUID |
tenant_id | VARCHAR(36) FK | Reference to tenant |
rule_id | VARCHAR(100) | Detection rule ID |
severity | ENUM | low, medium, high, critical |
title | VARCHAR(500) | Alert title |
description | TEXT | Alert description |
mitre_tactic | VARCHAR(20) | MITRE ATT&CK tactic |
mitre_technique | VARCHAR(20) | MITRE ATT&CK technique |
source_ip | VARCHAR(45) | Source IP address |
is_resolved | BOOLEAN | Resolved status |
resolved_at | DATETIME | Resolution timestamp |
created_at | DATETIME | Creation date |
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | UUID |
tenant_id | VARCHAR(36) FK | Tenant (null for global rules) |
rule_id | VARCHAR(100) | Rule identifier |
name | VARCHAR(255) | Rule name |
patterns | JSON | Regex patterns array |
severity | ENUM | Severity level |
mitre_tactic | VARCHAR(20) | ATT&CK tactic |
mitre_technique | VARCHAR(20) | ATT&CK technique |
is_active | BOOLEAN | Rule status |
threshold | INTEGER | Match count threshold |
window_seconds | INTEGER | Time window for threshold |
| Column | Type | Description |
|---|
id | VARCHAR(36) PK | UUID |
tenant_id | VARCHAR(36) FK | Tenant |
user_id | VARCHAR(100) | User who performed the action |
action | VARCHAR(100) | Action performed |
resource_type | VARCHAR(50) | Resource type |
resource_id | VARCHAR(100) | Resource identifier |
details | JSON | Additional details |
ip_address | VARCHAR(45) | Client IP |
created_at | DATETIME | Timestamp |