Skip to content

Database Schema

LogSys stores event data in PostgreSQL 16. The schema is owned by the pipeline (Kafka → Postgres); the backend API reads from it and writes only metadata (users, alerts, incidents, sources, applications, templates priorities).

Overview

erDiagram
    APPLICATIONS ||--o{ SOURCES : "groups"
    SOURCES ||--o{ EVENTS : "produces"
    SOURCES ||--o{ ALERTS : "triggers"
    EVENTS }o--o| TEMPLATES : "belongs to"
    TEMPLATES ||--o| TEMPLATE_PRIORITY : "has"
    INCIDENTS ||--o{ AI_INSIGHTS : "contains"
    EVENTS }o--o{ INCIDENTS : "correlated via"
    USERS ||--o{ TASKS : "assigned to"

Core Tables

events (partitioned)

The heart of the platform — enriched canonical events, partitioned by range on timestamp.

Column Type Notes
id BIGSERIAL Part of composite PK (id, timestamp)
schema_version TEXT Default '1.0'
timestamp TIMESTAMPTZ Partition key, indexed DESC
source VARCHAR(255) Originating data source
environment VARCHAR(50) dev, staging, prod, …
type VARCHAR(100) log, metric, trace, event
severity VARCHAR(20) DEBUG–CRITICAL
message TEXT Raw (PII-stripped) message
summary TEXT Parser-generated summary
template_id TEXT Drain3 template reference
host / pod_name VARCHAR Where the event happened
trace_id / span_id VARCHAR Distributed tracing correlation
occurrence_count INT Dedup-merged counter
tags JSONB Arbitrary metadata
dedup_key VARCHAR(255) SHA-256 dedup fingerprint (unique)
severity_rank SMALLINT Orderable severity
is_error_or_worse BOOL Precomputed flag for KPIs
priority VARCHAR(2) P1–P4 (ML)
priority_confidence FLOAT ML confidence
priority_source VARCHAR(20) ml, rules, manual
model_version VARCHAR(100) Model that made the prediction
inserted_at TIMESTAMPTZ Ingestion timestamp

templates

Column Type Notes
template_id TEXT (PK) Drain3 template key
template_text TEXT Pattern with placeholders
first_seen / last_seen TIMESTAMPTZ Lifespan
occurrence_count INT Aggregate occurrences
priority / confidence / model_version — Latest ML/override result

template_priority

Stable priority cache for templates (L3 PostgreSQL layer). Columns: template_id (PK), template, priority, confidence, model_version, trained_at, prediction_count, last_seen, created_at, updated_at.

dlq_events

Column Type
id BIGSERIAL PK
reason TEXT — why it failed
raw_payload JSONB — original event
received_at TIMESTAMPTZ

polling_state

Checkpointing for poll-based collectors: source_name (PK), last_cursor, updated_at.

Metadata Tables

Table Purpose
users Auth + RBAC roles (viewer…superadmin)
applications App grouping: name, environment, hosts[]
sources Connector registry, enabled, status, last_event_at
alerts Alert rules + firing state, linked to related_source
incidents Incident lifecycle + priority
ai_insights Snapshot-engine results; FK to incidents (migration 0016)
tasks Assignee/task tracking (migration 0004/0005)

Materialized Views

View Aggregates
mv_dashboard_aggregates Global totals, error rate, events/min, criticals/24h
mv_timeseries_hourly Hourly counts by severity (30-day window)
mv_top_templates Top templates by occurrence

Views are rebuilt during migrations and refreshed CONCURRENTLY after heavy writes (clear-all).

Ownership Boundary

Layer Writes
Pipeline events, templates, template_priority, dlq_events, polling_state
API backend users, applications, sources, alerts, incidents, ai_insights, tasks, priority overrides on events/template_priority

The backend never rewrites events directly — it only overrides priority/confidence on human correction.