# Table of Contents

1. [Overview](#overview)
2. [Schema Design](#schema-design)
3. [Migrations](#migrations)
4. [Repository Pattern](#repository-pattern)
5. [Caching Layer](#caching-layer)
6. [Health Checks](#health-checks)
7. [Connection Pooling](#connection-pooling)

---

## Overview

PostgreSQL as primary data store with pgx for high-performance connection pooling.

---

## Schema Design

### Users Table

```sql
CREATE TABLE IF NOT EXISTS users (
    nip VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_is_active ON users(is_active);
```

### Cache Schema (Unlogged Tables)

```sql
CREATE SCHEMA IF NOT EXISTS cache;

CREATE UNLOGGED TABLE cache.entries (
    key TEXT PRIMARY KEY,
    value BYTEA NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_cache_expires ON cache.entries(expires_at)
    WHERE expires_at IS NOT NULL;
```

**Why Unlogged Tables?**
- No Write-Ahead Log (WAL) overhead = 5-10x faster writes
- Data survives crashes but not unclean shutdowns
- Acceptable for cache (rebuilds from source on restart)
- Same PostgreSQL instance = simpler operations

---

## Migrations

Uses `golang-migrate` for database schema versioning.

### Migration Files

```
db/migrations/persistence/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_last_login.up.sql
├── 000002_add_last_login.down.sql
└── ...
```

### Example Migration

```sql
-- 000001_create_users.up.sql
CREATE TABLE IF NOT EXISTS users (
    nip VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_is_active ON users(is_active);

-- 000001_create_users.down.sql
DROP TABLE IF EXISTS users;
```

### Running Migrations

```bash
# Via admin CLI
./messaging-be migrate up
./messaging-be migrate down 1

# Via migrate CLI directly
migrate -database "$PERSISTENCE_DSN" -path db/migrations/persistence up
migrate -database "$PERSISTENCE_DSN" -path db/migrations/persistence down 1
```

---

## Repository Pattern

### Pool Initialization

```go
package persistence

import (
    "context"
    "fmt"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

type Pool struct {
    *pgxpool.Pool
}

func NewPool(ctx context.Context, dsn string, maxConns, minConns int) (*Pool, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, fmt.Errorf("failed to parse config: %w", err)
    }

    config.MaxConns = int32(maxConns)
    config.MinConns = int32(minConns)
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute
    config.HealthCheckPeriod = time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, fmt.Errorf("failed to create pool: %w", err)
    }

    if err := pool.Ping(ctx); err != nil {
        return nil, fmt.Errorf("failed to ping pool: %w", err)
    }

    return &Pool{Pool: pool}, nil
}

func (p *Pool) Close() {
    p.Pool.Close()
}
```

### User Repository

```go
package persistence

import (
    "context"

    "myapp/internal/core/domain"
    "myapp/internal/core/port/outbound"

    "github.com/jackc/pgx/v5"
    "github.com/pkg/errors"
)

type userRepository struct {
    pool *Pool
}

func NewUserRepository(pool *Pool) outbound.UserRepository {
    return &userRepository{pool: pool}
}

func (r *userRepository) GetByNIP(ctx context.Context, nip string) (*domain.User, error) {
    var user domain.User
    err := r.pool.QueryRow(ctx,
        `SELECT nip, name, email, is_active, last_login_at, created_at, updated_at
         FROM users WHERE nip = $1`,
        nip,
    ).Scan(&user.NIP, &user.Name, &user.Email, &user.IsActive,
        &user.LastLoginAt, &user.CreatedAt, &user.UpdatedAt)

    if err == pgx.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, errors.Wrap(err, "failed to get user")
    }

    return &user, nil
}

func (r *userRepository) Create(ctx context.Context, user *domain.User) error {
    err := r.pool.QueryRow(ctx,
        `INSERT INTO users (nip, name, email, is_active, last_login_at)
         VALUES ($1, $2, $3, $4, $5)
         RETURNING created_at, updated_at`,
        user.NIP, user.Name, user.Email, user.IsActive, user.LastLoginAt,
    ).Scan(&user.CreatedAt, &user.UpdatedAt)

    if err != nil {
        return errors.Wrap(err, "failed to create user")
    }

    return nil
}

func (r *userRepository) Update(ctx context.Context, user *domain.User) error {
    _, err := r.pool.Exec(ctx,
        `UPDATE users SET name = $1, email = $2, is_active = $3,
         last_login_at = $4, updated_at = NOW()
         WHERE nip = $5`,
        user.Name, user.Email, user.IsActive, user.LastLoginAt, user.NIP,
    )
    if err != nil {
        return errors.Wrap(err, "failed to update user")
    }

    return nil
}
```

---

## Caching Layer

### Cache Repository

```go
package cache

import (
    "context"
    "time"

    "myapp/internal/core/port/outbound"

    "github.com/jackc/pgx/v5"
    "github.com/pkg/errors"
)

type postgresCacheRepository struct {
    pool *persistence.Pool
}

func NewPostgresCacheRepository(pool *persistence.Pool) outbound.CacheRepository {
    return &postgresCacheRepository{pool: pool}
}

func (r *postgresCacheRepository) Get(ctx context.Context, key string) ([]byte, error) {
    var value []byte
    var expiresAt time.Time

    err := r.pool.QueryRow(ctx,
        `SELECT value, expires_at FROM cache.entries
         WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())`,
        key,
    ).Scan(&value, &expiresAt)

    if err == pgx.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, errors.Wrap(err, "failed to get cache")
    }

    return value, nil
}

func (r *postgresCacheRepository) Set(ctx context.Context, key string, value []byte, ttl time.Duration) error {
    expiresAt := time.Now().Add(ttl)

    _, err := r.pool.Exec(ctx,
        `INSERT INTO cache.entries (key, value, expires_at)
         VALUES ($1, $2, $3)
         ON CONFLICT (key) DO UPDATE SET value = $2, expires_at = $3`,
        key, value, expiresAt,
    )
    if err != nil {
        return errors.Wrap(err, "failed to set cache")
    }

    return nil
}

func (r *postgresCacheRepository) Delete(ctx context.Context, key string) error {
    _, err := r.pool.Exec(ctx, `DELETE FROM cache.entries WHERE key = $1`, key)
    if err != nil {
        return errors.Wrap(err, "failed to delete cache")
    }
    return nil
}
```

### HA-Safe Cleanup

```go
func (r *postgresCacheRepository) CleanupExpired(ctx context.Context) (int64, error) {
    // Try to acquire advisory lock (HA-safe)
    var acquired bool
    err := r.pool.QueryRow(ctx,
        `SELECT pg_try_advisory_lock(hashtext('cache_cleanup'))`,
    ).Scan(&acquired)

    if err != nil {
        return 0, errors.Wrap(err, "failed to acquire cleanup lock")
    }

    if !acquired {
        return 0, nil // Another instance is running cleanup
    }

    defer func() {
        _, _ = r.pool.Exec(ctx, `SELECT pg_advisory_unlock(hashtext('cache_cleanup'))`)
    }()

    result, err := r.pool.Exec(ctx, `DELETE FROM cache.entries WHERE expires_at < NOW()`)
    if err != nil {
        return 0, errors.Wrap(err, "failed to cleanup expired entries")
    }

    return result.RowsAffected(), nil
}
```

---

## Health Checks

### Health Endpoint with Version

```go
r.GET("/healthz", func(c *gin.Context) {
    dbHealthy := checkDatabase(provider.PersistencePool)
    cacheHealthy := checkCache(provider.CachePool)

    allHealthy := dbHealthy && cacheHealthy
    status := "ok"
    if !allHealthy {
        status = "degraded"
    }

    response := gin.H{
        "status":  status,
        "version": buildinfo.Version,
        "commit":  buildinfo.Commit,
        "checks": gin.H{
            "database": dbHealthy,
            "cache":    cacheHealthy,
        },
    }

    if allHealthy {
        c.JSON(200, response)
    } else {
        c.JSON(503, response)
    }
})

func checkDatabase(pool *persistence.Pool) bool {
    return pool.Ping(context.Background()) == nil
}
```

---

## Connection Pooling

**Pool Configuration:**

| Setting | Default | Description |
|---------|---------|-------------|
| `MaxConns` | 25 | Maximum connections |
| `MinConns` | 5 | Minimum idle connections |
| `MaxConnLifetime` | 1h | Maximum connection lifetime |
| `MaxConnIdleTime` | 30m | Maximum idle time |
| `HealthCheckPeriod` | 1m | Health check interval |

**Recommended Settings:**

| Environment | MaxConns | MinConns |
|-------------|----------|----------|
| Development | 10 | 2 |
| Staging | 20 | 5 |
| Production | 50 | 10 |