postgres

package module
v0.3.3 Latest Latest
Warning

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

 Go to latest Published: Feb 19, 2026 License: MIT Imports: 14 Imported by: 0

README

slack-manager-postgres-plugin

Go Reference Go Report Card CI

A PostgreSQL storage backend for Slack Manager. Implements the types.DB interface using pgxpool for connection pooling.

Installation

go get github.com/slackmgr/slack-manager-postgres-plugin

Requires Go 1.25+ and PostgreSQL 13+.

Usage

import (
    postgres "github.com/slackmgr/slack-manager-postgres-plugin"
)

client := postgres.New(
    postgres.WithHost("localhost"),
    postgres.WithPort(5432),
    postgres.WithUser("myuser"),
    postgres.WithPassword("mypassword"),
    postgres.WithDatabase("mydb"),
    postgres.WithSSLMode(postgres.SSLModeRequire),
)

if err := client.Connect(ctx); err != nil {
    log.Fatal(err)
}
defer client.Close(ctx)

if err := client.Init(ctx, false); err != nil {
    log.Fatal(err)
}

Init creates the required tables if they do not exist, validates the schema, and starts the background TTL cleanup goroutine. It is idempotent — calling it multiple times on the same Client is safe. Pass skipSchemaValidation: true to skip the schema validation step.

Configuration

All options are provided via With* constructor functions. Only WithUser and WithDatabase are required — everything else has a sensible default.

Option Default Description
WithHost(string) "localhost" Database host
WithPort(int) 5432 Database port
WithUser(string) (required) Database user
WithPassword(string) "" Database password
WithDatabase(string) (required) Database name
WithSSLMode(SSLMode) SSLModePrefer SSL connection mode
WithIssuesTable(string) "issues" Issues table name
WithAlertsTable(string) "alerts" Alerts table name
WithMoveMappingsTable(string) "move_mappings" Move mappings table name
WithChannelProcessingStateTable(string) "channel_processing_state" Channel processing state table name
WithAlertsTimeToLive(time.Duration) 30 days TTL applied to alert records. Must be greater than zero.
WithIssuesTimeToLive(time.Duration) 180 days TTL applied to closed issue records. Open issues never expire. Must be greater than zero.
WithTTLCleanupInterval(time.Duration) 1h How often the background goroutine physically deletes expired rows. Must be greater than zero.
WithTTLCleanupDisabled() — Disables the background TTL cleanup goroutine. Expired rows are excluded from reads but never physically deleted.
WithPoolMaxConnections(int32) pgx default Max pool connections
WithPoolMinConnections(int32) pgx default Min pool connections
WithPoolMinIdleConnections(int32) pgx default Min idle connections
WithPoolMaxConnectionLifetime(time.Duration) pgx default Max connection lifetime
WithPoolMaxConnectionLifetimeJitter(time.Duration) pgx default Jitter added to max connection lifetime
WithPoolMaxConnectionIdleTime(time.Duration) pgx default Max connection idle time
WithPoolHealthCheckPeriod(time.Duration) pgx default Health check interval
SSL Modes
postgres.SSLModeDisable    // No SSL
postgres.SSLModeAllow      // Try non-SSL first, then SSL
postgres.SSLModePrefer     // Try SSL first, then non-SSL (default)
postgres.SSLModeRequire    // SSL only, no certificate verification
postgres.SSLModeVerifyCA   // SSL with CA certificate verification
postgres.SSLModeVerifyFull // SSL with CA and hostname verification
Custom Table Names

Custom table names are useful for multi-tenant setups or running integration tests alongside production data:

client := postgres.New(
    postgres.WithUser("myuser"),
    postgres.WithDatabase("mydb"),
    postgres.WithIssuesTable("myapp_issues"),
    postgres.WithAlertsTable("myapp_alerts"),
    postgres.WithMoveMappingsTable("myapp_move_mappings"),
    postgres.WithChannelProcessingStateTable("myapp_channel_processing_state"),
)

Table names must be valid PostgreSQL unquoted identifiers (letters, digits, underscores; must not start with a digit).

TTL (Time-To-Live)

TTL is always active. By default, alert records expire after 30 days and closed issue records expire after 180 days. Open issues never expire.

When a record's TTL expires it is immediately excluded from all read operations. A background goroutine (started by Init, stopped by Close) periodically runs a DELETE to physically remove expired rows. The cleanup interval defaults to 1 hour and is configurable via WithTTLCleanupInterval. The goroutine can be disabled entirely with WithTTLCleanupDisabled — expired rows will still be excluded from reads, but never physically deleted.

To override the defaults:

client := postgres.New(
    postgres.WithUser("myuser"),
    postgres.WithDatabase("mydb"),
    postgres.WithAlertsTimeToLive(7 * 24 * time.Hour),   // 7 days
    postgres.WithIssuesTimeToLive(90 * 24 * time.Hour),  // 90 days
    postgres.WithTTLCleanupInterval(15 * time.Minute),
)

Database Schema

Four tables are created by Init:

  • issues — Slack issues with correlation IDs and open/closed state
  • alerts — Alert records
  • move_mappings — Issue move tracking between channels
  • channel_processing_state — Per-channel processing timestamps

All tables include a version column for schema evolution and an attrs JSONB column for flexible storage. The issues and alerts tables additionally include an expires_at column used for TTL.

Build Commands

make test              # Run unit tests with security scan, formatting, and coverage
make lint              # Run golangci-lint
make lint-fix          # Run golangci-lint with auto-fix
make test-integration  # Run integration tests (requires PostgreSQL — see below)
Integration Tests

Integration tests require a running PostgreSQL instance:

docker-compose up -d
make test-integration

The Docker Compose setup starts PostgreSQL 17 on localhost:5432 with user postgres, password qwerty, and database slack_manager.

License

This project is licensed under the MIT License — see the LICENSE file for details.

Copyright (c) 2026 Peter Aglen

Documentation

Overview

Package postgres provides a PostgreSQL-backed implementation of the types.DB interface from github.com/slackmgr/types.

It uses pgx v5 with connection pooling (pgxpool) and stores all entities as JSONB with indexed columns for efficient querying.

Usage

Create a client using New with functional options, call Client.Connect to establish the connection pool, and then Client.Init to create the database schema: 

client := postgres.New(
    postgres.WithHost("localhost"),
    postgres.WithPort(5432),
    postgres.WithUser("postgres"),
    postgres.WithPassword("secret"),
    postgres.WithDatabase("slack_manager"),
)

if err := client.Connect(ctx); err != nil {
    log.Fatal(err)
}
defer client.Close(ctx)

if err := client.Init(ctx, false); err != nil {
    log.Fatal(err)
}

Database Tables

Four tables are created automatically by Client.Init (table names are configurable via the corresponding With* options):

  • issues — Slack issues with correlation IDs, open/closed state, and TTL
  • alerts — Alert records with TTL
  • move_mappings — Issue move tracking between channels
  • channel_processing_state — Per-channel processing timestamps

Each table includes a version column and an attrs JSONB column that holds the full serialised entity.

Connection Pool

The underlying pgxpool can be tuned with the pool-specific options: WithPoolMaxConnections, WithPoolMinConnections, WithPoolMinIdleConnections, WithPoolMaxConnectionLifetime, WithPoolMaxConnectionIdleTime, WithPoolHealthCheckPeriod, and WithPoolMaxConnectionLifetimeJitter.

TTL and Cleanup

Closed issues and alerts are assigned an expiry timestamp on write. Default TTLs are 180 days for issues and 30 days for alerts; both are configurable via WithIssuesTimeToLive and WithAlertsTimeToLive.

A background goroutine periodically deletes expired rows. Its interval defaults to 1 hour and can be changed with WithTTLCleanupInterval or disabled entirely with WithTTLCleanupDisabled.

Schema Validation

When Client.Init is called with skipSchemaValidation set to false, it queries information_schema.columns and verifies that every expected column exists with the correct data type and nullability. Pass true to skip this check in environments where the schema is managed externally.

SSL

SSL behaviour is controlled by WithSSLMode using the SSLMode constants (SSLModeDisable, SSLModeAllow, SSLModePrefer, SSLModeRequire, SSLModeVerifyCA, SSLModeVerifyFull). The default is SSLModePrefer.

Index

Constants

View Source 
const (
	IssueModelVersion                  = 2
	AlertModelVersion                  = 2
	MoveMappingModelVersion            = 2
	ChannelProcessingStateModelVersion = 2
)

Variables

This section is empty.

Functions

This section is empty.

Types

type Client 

type Client struct {
	// contains filtered or unexported fields
}

func New 

func New(opts ...Option) *Client

func (*Client) Close 

func (c *Client) Close(_ context.Context) error

func (*Client) Connect 

func (c *Client) Connect(ctx context.Context) error

func (*Client) DeleteMoveMapping 

func (c *Client) DeleteMoveMapping(ctx context.Context, channelID, correlationID string) error

func (*Client) DropAllData 

func (c *Client) DropAllData(ctx context.Context) error

func (*Client) FindActiveChannels 

func (c *Client) FindActiveChannels(ctx context.Context) ([]string, error)

func (*Client) FindChannelProcessingState 

func (c *Client) FindChannelProcessingState(ctx context.Context, channelID string) (*types.ChannelProcessingState, error)

func (*Client) FindIssueBySlackPostID 

func (c *Client) FindIssueBySlackPostID(ctx context.Context, channelID, postID string) (string, json.RawMessage, error)

func (*Client) FindMoveMapping 

func (c *Client) FindMoveMapping(ctx context.Context, channelID, correlationID string) (json.RawMessage, error)

func (*Client) FindOpenIssueByCorrelationID 

func (c *Client) FindOpenIssueByCorrelationID(ctx context.Context, channelID, correlationID string) (string, json.RawMessage, error)

func (*Client) Init 

func (c *Client) Init(ctx context.Context, skipSchemaValidation bool) error

func (*Client) LoadOpenIssuesInChannel 

func (c *Client) LoadOpenIssuesInChannel(ctx context.Context, channelID string) (map[string]json.RawMessage, error)

func (*Client) MoveIssue 

func (c *Client) MoveIssue(ctx context.Context, issue types.Issue, _, targetChannelID string) error

func (*Client) SaveAlert 

func (c *Client) SaveAlert(ctx context.Context, alert *types.Alert) error

func (*Client) SaveChannelProcessingState 

func (c *Client) SaveChannelProcessingState(ctx context.Context, state *types.ChannelProcessingState) error

func (*Client) SaveIssue 

func (c *Client) SaveIssue(ctx context.Context, issue types.Issue) error

func (*Client) SaveIssues 

func (c *Client) SaveIssues(ctx context.Context, issues ...types.Issue) error

func (*Client) SaveMoveMapping 

func (c *Client) SaveMoveMapping(ctx context.Context, moveMapping types.MoveMapping) error

type Option 

type Option func(*options)

Option is a functional option for configuring a Client.

func WithAlertsTable 

func WithAlertsTable(name string) Option

func WithAlertsTimeToLive 

func WithAlertsTimeToLive(d time.Duration) Option

WithAlertsTimeToLive sets the TTL applied to alert records. The default is 30 days. The duration must be greater than zero.

func WithChannelProcessingStateTable 

func WithChannelProcessingStateTable(name string) Option

func WithDatabase 

func WithDatabase(database string) Option

func WithHost 

func WithHost(host string) Option

func WithIssuesTable 

func WithIssuesTable(name string) Option

func WithIssuesTimeToLive 

func WithIssuesTimeToLive(d time.Duration) Option

WithIssuesTimeToLive sets the TTL applied to closed issue records. The default is 180 days. The duration must be greater than zero.

func WithMoveMappingsTable 

func WithMoveMappingsTable(name string) Option

func WithPassword 

func WithPassword(password string) Option

func WithPoolHealthCheckPeriod 

func WithPoolHealthCheckPeriod(d time.Duration) Option

func WithPoolMaxConnectionIdleTime 

func WithPoolMaxConnectionIdleTime(d time.Duration) Option

func WithPoolMaxConnectionLifetime 

func WithPoolMaxConnectionLifetime(d time.Duration) Option

func WithPoolMaxConnectionLifetimeJitter 

func WithPoolMaxConnectionLifetimeJitter(d time.Duration) Option

func WithPoolMaxConnections 

func WithPoolMaxConnections(n int32) Option

func WithPoolMinConnections 

func WithPoolMinConnections(n int32) Option

func WithPoolMinIdleConnections 

func WithPoolMinIdleConnections(n int32) Option

func WithPort 

func WithPort(port int) Option

func WithSSLMode 

func WithSSLMode(mode SSLMode) Option

func WithTTLCleanupDisabled 

func WithTTLCleanupDisabled() Option

WithTTLCleanupDisabled disables the background TTL cleanup goroutine. When disabled, expired rows are excluded from reads but never physically deleted. Useful in tests or environments that handle cleanup externally.

func WithTTLCleanupInterval 

func WithTTLCleanupInterval(d time.Duration) Option

WithTTLCleanupInterval sets how often the background goroutine runs to physically delete expired rows. Defaults to 1 hour. The duration must be greater than zero.

func WithUser 

func WithUser(user string) Option

type SSLMode 

type SSLMode string

SSLMode represents PostgreSQL SSL connection modes. 

const (
	SSLModeDisable    SSLMode = "disable"     // No SSL
	SSLModeAllow      SSLMode = "allow"       // Try non-SSL first, then SSL
	SSLModePrefer     SSLMode = "prefer"      // Try SSL first, then non-SSL (default)
	SSLModeRequire    SSLMode = "require"     // Only SSL (no certificate verification)
	SSLModeVerifyCA   SSLMode = "verify-ca"   // SSL with CA verification
	SSLModeVerifyFull SSLMode = "verify-full" // SSL with CA and hostname verification
)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.