db

package
v0.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Aug 23, 2026 License: MIT Imports: 6 Imported by: 0

README

🗄️ Database Layer & Domain Models (internal/db)

This package manages the relational database persistence, GORM schema definitions, auto-migrations, and transactional domain entity models for PingGopher.


🏛️ Architecture & Database Engine

  • ORM Framework: GORM v1.25+
  • Default Storage (Dev/Local): Pure Go CGO-Free SQLite (github.com/glebarez/sqlite backed by modernc.org/sqlite).
  • Production Storage: PostgreSQL (fully supported via GORM Postgres driver with zero schema modifications).
  • Primary Keys: Cryptographically secure 128-bit UUIDs (github.com/google/uuid) populated automatically via GORM lifecycle hooks.
  • Relational Integrity: Foreign Key constraints enabled at connection time via SQLite DSN _pragma=foreign_keys(1).

📊 Entity-Relationship Diagram (ERD)

erDiagram
    User ||--o{ Monitor : "owns (1:N)"
    Monitor ||--o{ PingLog : "logs execution (1:N)"
    Monitor ||--o{ Incident : "tracks outage (1:N)"

    User {
        uuid id PK
        string email UK
        string password_hash
        datetime created_at
        datetime updated_at
    }

    Monitor {
        uuid id PK
        uuid user_id FK
        string name
        text url
        integer check_interval_seconds
        string status
        datetime ssl_expiration_date
        datetime created_at
        datetime updated_at
    }

    PingLog {
        uuid id PK
        uuid monitor_id FK
        integer status_code
        integer response_time_ms
        text error_message
        datetime created_at
    }

    Incident {
        uuid id PK
        uuid monitor_id FK
        datetime started_at
        datetime resolved_at
        text cause
        string status
        datetime created_at
        datetime updated_at
    }

🗂️ Data Dictionary & Schemas

1. users Table

Stores tenant account credentials and identity metadata.

Column Type Constraints Description
id uuid PRIMARY KEY Unique User UUID
email varchar(255) UNIQUE INDEX, NOT NULL User email address
password_hash varchar(255) NOT NULL Bcrypt hashed password
created_at datetime NOT NULL Account creation timestamp
updated_at datetime NOT NULL Account modification timestamp

2. monitors Table

Stores HTTP, Ping, and Synthetic monitoring target configurations.

Column Type Constraints Description
id uuid PRIMARY KEY Unique Monitor UUID
user_id uuid INDEX, FK -> users.id (CASCADE) Target owner ID
name varchar(255) NOT NULL Human-readable monitor name
url text NOT NULL Target URL (e.g., https://api.example.com)
check_interval_seconds integer DEFAULT 60, NOT NULL Frequency of execution in seconds
status varchar(50) DEFAULT 'PAUSED', NOT NULL Operational status: UP, DOWN, DEGRADED, PAUSED
ssl_expiration_date datetime INDEX, NULLABLE Cached SSL certificate expiry date
created_at datetime NOT NULL Target creation timestamp
updated_at datetime NOT NULL Target modification timestamp

3. ping_logs Table

Append-only time-series ledger recording probe execution metrics.

Column Type Constraints Description
id uuid PRIMARY KEY Unique Log Entry UUID
monitor_id uuid INDEX, FK -> monitors.id (CASCADE) Associated Monitor ID
status_code integer NOT NULL HTTP response code (e.g. 200, 503)
response_time_ms integer NOT NULL Probe latency in milliseconds
error_message text NULLABLE Network or HTTP error string
created_at datetime INDEX, NOT NULL Execution timestamp

4. incidents Table

Tracks downtime events, root causes, and resolution timelines for SLA reporting.

Column Type Constraints Description
id uuid PRIMARY KEY Unique Incident UUID
monitor_id uuid INDEX, FK -> monitors.id (CASCADE) Associated Monitor ID
started_at datetime NOT NULL Incident start timestamp
resolved_at datetime NULLABLE Incident resolution timestamp
cause text NULLABLE Root cause description
status varchar(50) DEFAULT 'OPEN', NOT NULL Status: OPEN, INVESTIGATING, RESOLVED
created_at datetime NOT NULL Record creation timestamp
updated_at datetime NOT NULL Record modification timestamp

⚡ Lifecycle Hooks & UUID Auto-Generation

Every struct implements GORM's BeforeCreate hook to automatically assign a new UUID if Nil:

func (m *Monitor) BeforeCreate(tx *gorm.DB) error {
	if m.ID == uuid.Nil {
		m.ID = uuid.New()
	}
	return nil
}

🧪 Testing & Migrations

Automated Unit Test Suite (db_test.go)

We use an isolated SQLite test database (test_pinggopher.db) with automatic teardown (os.Remove) to verify schema integrity and relational operations:

Test Method Test Focus & Verification
TestDatabaseInitializationAndModels - Verifies connection initialization via InitDB().
- Tests GORM schema auto-migration across User, Monitor, PingLog, and Incident.
- Verifies BeforeCreate GORM lifecycle hooks auto-generate non-nil UUIDs.
- Validates relational foreign key constraints (ON DELETE CASCADE).
- Verifies nested relational preloading (Preload("Monitors.PingLogs") and Preload("Monitors.Incidents")).
Running Database Unit Tests
go test -v ./internal/db

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func InitDB

func InitDB(dbPath string) (*gorm.DB, error)

InitDB initializes the GORM database connection using CGO-free pure Go SQLite driver and runs automatic migrations.

func PruneOldLogs

func PruneOldLogs(database *gorm.DB, retentionDays int) (int64, error)

PruneOldLogs purges PingLog entries older than retentionDays (default 30 days) to prevent database bloat.

Types

type Incident

type Incident struct {
	ID         uuid.UUID      `gorm:"type:uuid;primaryKey" json:"id"`
	MonitorID  uuid.UUID      `gorm:"type:uuid;index;not null" json:"monitor_id"`
	Monitor    Monitor        `gorm:"foreignKey:MonitorID" json:"-"`
	StartedAt  time.Time      `gorm:"not null" json:"started_at"`
	ResolvedAt *time.Time     `json:"resolved_at,omitempty"`
	Cause      string         `gorm:"type:text" json:"cause"`
	Status     IncidentStatus `gorm:"type:varchar(50);default:'OPEN';not null" json:"status"`
	CreatedAt  time.Time      `json:"created_at"`
	UpdatedAt  time.Time      `json:"updated_at"`
}

Incident tracks outages, degraded performance events, and resolution workflows.

func (*Incident) BeforeCreate

func (i *Incident) BeforeCreate(tx *gorm.DB) error

BeforeCreate GORM hook to generate UUID if not set.

type IncidentStatus

type IncidentStatus string

IncidentStatus defines the resolution lifecycle of an incident.

const (
	IncidentOpen          IncidentStatus = "OPEN"
	IncidentInvestigating IncidentStatus = "INVESTIGATING"
	IncidentResolved      IncidentStatus = "RESOLVED"
)

type Monitor

type Monitor struct {
	ID                   uuid.UUID     `gorm:"type:uuid;primaryKey" json:"id"`
	UserID               uuid.UUID     `gorm:"type:uuid;index;not null" json:"user_id"`
	User                 User          `gorm:"foreignKey:UserID" json:"-"`
	Name                 string        `gorm:"type:varchar(255);not null" json:"name"`
	URL                  string        `gorm:"type:text;not null" json:"url"`
	CheckIntervalSeconds int           `gorm:"default:60;not null" json:"check_interval_seconds"`
	Status               MonitorStatus `gorm:"type:varchar(50);default:'PAUSED';not null" json:"status"`
	SSLExpirationDate    *time.Time    `gorm:"index" json:"ssl_expiration_date,omitempty"`
	WebhookURL           string        `gorm:"type:text" json:"webhook_url,omitempty"`
	CreatedAt            time.Time     `json:"created_at"`
	UpdatedAt            time.Time     `json:"updated_at"`

	PingLogs  []PingLog  `gorm:"foreignKey:MonitorID;constraint:OnDelete:CASCADE" json:"ping_logs,omitempty"`
	Incidents []Incident `gorm:"foreignKey:MonitorID;constraint:OnDelete:CASCADE" json:"incidents,omitempty"`
}

Monitor represents an external uptime or synthetic monitoring target.

func (*Monitor) BeforeCreate

func (m *Monitor) BeforeCreate(tx *gorm.DB) error

BeforeCreate GORM hook to generate UUID if not set.

type MonitorStatus

type MonitorStatus string

MonitorStatus defines the operational status of a monitor target.

const (
	StatusUp       MonitorStatus = "UP"
	StatusDown     MonitorStatus = "DOWN"
	StatusDegraded MonitorStatus = "DEGRADED"
	StatusPaused   MonitorStatus = "PAUSED"
)

type PingLog

type PingLog struct {
	ID             uuid.UUID `gorm:"type:uuid;primaryKey" json:"id"`
	MonitorID      uuid.UUID `gorm:"type:uuid;index:idx_monitor_created,priority:1;not null" json:"monitor_id"`
	Monitor        Monitor   `gorm:"foreignKey:MonitorID" json:"-"`
	StatusCode     int       `gorm:"not null" json:"status_code"`
	ResponseTimeMS int       `gorm:"not null" json:"response_time_ms"`
	ErrorMessage   string    `gorm:"type:text" json:"error_message,omitempty"`
	CreatedAt      time.Time `gorm:"index:idx_monitor_created,priority:2" json:"created_at"`
}

PingLog records the result of an individual synthetic probe or HTTP check execution.

func (*PingLog) BeforeCreate

func (p *PingLog) BeforeCreate(tx *gorm.DB) error

BeforeCreate GORM hook to generate UUID if not set.

type User

type User struct {
	ID           uuid.UUID `gorm:"type:uuid;primaryKey" json:"id"`
	Email        string    `gorm:"type:varchar(255);uniqueIndex;not null" json:"email"`
	PasswordHash string    `gorm:"type:varchar(255);not null" json:"-"`
	CreatedAt    time.Time `json:"created_at"`
	UpdatedAt    time.Time `json:"updated_at"`

	Monitors []Monitor `gorm:"foreignKey:UserID;constraint:OnDelete:CASCADE" json:"monitors,omitempty"`
}

User represents tenant user accounts.

func (*User) BeforeCreate

func (u *User) BeforeCreate(tx *gorm.DB) error

BeforeCreate GORM hook to generate UUID if not set.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL