# Database Schema

STA Connector uses SQLite for development/single-instance or PostgreSQL for production/multi-instance deployments.

## Schema Overview

```
┌─────────────────────────────────────────────────────────────────┐
│                         Database Schema                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌─────────────────────┐      ┌─────────────────────┐           │
│  │  outbound_files     │      │  processed_protocols │           │
│  ├─────────────────────┤      ├─────────────────────┤           │
│  │ id (PK)             │      │ protocol (PK)       │           │
│  │ protocol            │      │ system              │           │
│  │ file_name           │      │ file_type           │           │
│  │ file_type           │      │ processed_at        │           │
│  │ file_size           │      └─────────────────────┘           │
│  │ checksum            │                                        │
│  │ source_system       │      ┌─────────────────────┐           │
│  │ status              │      │  retry_queue        │           │
│  │ sta_state (JSON)    │      ├─────────────────────┤           │
│  │ retry_count         │      │ id (PK)             │           │
│  │ last_error          │      │ item_type           │           │
│  │ created_at          │      │ reference_id        │           │
│  │ updated_at          │      │ payload             │           │
│  │ completed_at        │      │ retry_count         │           │
│  └─────────────────────┘      │ max_retries         │           │
│                               │ next_retry_at       │           │
│  ┌─────────────────────┐      │ last_error          │           │
│  │  config_overrides   │      │ created_at          │           │
│  ├─────────────────────┤      │ updated_at          │           │
│  │ key (PK)            │      └─────────────────────┘           │
│  │ value               │                                        │
│  │ updated_at          │                                        │
│  └─────────────────────┘                                        │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘
```

## Tables

### outbound_files

Tracks files submitted for upload to BCB STA.

```sql
CREATE TABLE outbound_files (
    id TEXT PRIMARY KEY,
    protocol TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    checksum TEXT NOT NULL,
    source_system TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    sta_state TEXT,  -- JSON
    retry_count INTEGER DEFAULT 0,
    last_error TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    completed_at DATETIME
);

CREATE INDEX idx_outbound_files_protocol ON outbound_files(protocol);
CREATE INDEX idx_outbound_files_status ON outbound_files(status);
CREATE INDEX idx_outbound_files_source_system ON outbound_files(source_system);
CREATE INDEX idx_outbound_files_created_at ON outbound_files(created_at);
```

#### Columns

| Column | Type | Description |
|--------|------|-------------|
| `id` | TEXT | UUID primary key |
| `protocol` | TEXT | BCB protocol number |
| `file_name` | TEXT | Uploaded filename |
| `file_type` | TEXT | BCB file type code |
| `file_size` | INTEGER | File size in bytes |
| `checksum` | TEXT | SHA-256 checksum |
| `source_system` | TEXT | BCB system code |
| `status` | TEXT | Processing status |
| `sta_state` | TEXT | JSON-encoded STA state |
| `retry_count` | INTEGER | Number of retry attempts |
| `last_error` | TEXT | Last error message |
| `created_at` | DATETIME | Record creation time |
| `updated_at` | DATETIME | Last update time |
| `completed_at` | DATETIME | Completion time |

#### Status Values

| Status | Description |
|--------|-------------|
| `pending` | Queued for processing |
| `uploading` | Upload in progress |
| `uploaded` | Sent to BCB |
| `processing` | BCB is processing |
| `completed` | Successfully processed |
| `failed` | Processing failed |
| `retrying` | Scheduled for retry |

### processed_protocols

Tracks BCB protocol numbers that have been processed (prevents duplicates).

```sql
CREATE TABLE processed_protocols (
    protocol TEXT PRIMARY KEY,
    system TEXT NOT NULL,
    file_type TEXT,
    processed_at DATETIME NOT NULL
);

CREATE INDEX idx_processed_protocols_system ON processed_protocols(system);
CREATE INDEX idx_processed_protocols_processed_at ON processed_protocols(processed_at);
```

#### Columns

| Column | Type | Description |
|--------|------|-------------|
| `protocol` | TEXT | BCB protocol number (PK) |
| `system` | TEXT | BCB system code |
| `file_type` | TEXT | BCB file type |
| `processed_at` | DATETIME | Processing timestamp |

### retry_queue

Manages retry attempts for failed operations.

```sql
CREATE TABLE retry_queue (
    id TEXT PRIMARY KEY,
    item_type TEXT NOT NULL,
    reference_id TEXT NOT NULL,
    payload TEXT NOT NULL,  -- JSON
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    next_retry_at DATETIME NOT NULL,
    last_error TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_retry_queue_next_retry_at ON retry_queue(next_retry_at);
CREATE INDEX idx_retry_queue_item_type ON retry_queue(item_type);
CREATE INDEX idx_retry_queue_reference_id ON retry_queue(reference_id);
```

#### Columns

| Column | Type | Description |
|--------|------|-------------|
| `id` | TEXT | UUID primary key |
| `item_type` | TEXT | Type of retry (upload, callback, webhook) |
| `reference_id` | TEXT | Reference to original item |
| `payload` | TEXT | JSON-encoded retry data |
| `retry_count` | INTEGER | Current retry attempt |
| `max_retries` | INTEGER | Maximum retry attempts |
| `next_retry_at` | DATETIME | Scheduled retry time |
| `last_error` | TEXT | Error from last attempt |
| `created_at` | DATETIME | Record creation time |
| `updated_at` | DATETIME | Last update time |

#### Item Types

| Type | Description |
|------|-------------|
| `upload` | File upload to STA |
| `callback` | Callback delivery |
| `webhook` | Webhook notification |

### config_overrides

Stores runtime configuration overrides.

```sql
CREATE TABLE config_overrides (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at DATETIME NOT NULL
);
```

#### Columns

| Column | Type | Description |
|--------|------|-------------|
| `key` | TEXT | Configuration key |
| `value` | TEXT | Configuration value |
| `updated_at` | DATETIME | Last update time |

## Go Data Models

### OutboundFile

```go
// internal/store/models.go
type OutboundFile struct {
    ID            string     `json:"id"`
    Protocol      string     `json:"protocol"`
    FileName      string     `json:"file_name"`
    FileType      string     `json:"file_type"`
    FileSize      int64      `json:"file_size"`
    Checksum      string     `json:"checksum"`
    SourceSystem  string     `json:"source_system"`
    Status        string     `json:"status"`
    STAState      *STAState  `json:"sta_state,omitempty"`
    RetryCount    int        `json:"retry_count"`
    LastError     string     `json:"last_error,omitempty"`
    CreatedAt     time.Time  `json:"created_at"`
    UpdatedAt     time.Time  `json:"updated_at"`
    CompletedAt   *time.Time `json:"completed_at,omitempty"`
}

type STAState struct {
    TransferID     string     `json:"transfer_id,omitempty"`
    ExternalID     string     `json:"external_id,omitempty"`
    STAStatus      string     `json:"sta_status,omitempty"`
    STAMessage     string     `json:"sta_message,omitempty"`
    STATimestamp   *time.Time `json:"sta_timestamp,omitempty"`
    AcknowledgedAt *time.Time `json:"acknowledged_at,omitempty"`
    DeliveredAt    *time.Time `json:"delivered_at,omitempty"`
}

// Status constants
const (
    StatusPending    = "pending"
    StatusUploading  = "uploading"
    StatusUploaded   = "uploaded"
    StatusProcessing = "processing"
    StatusCompleted  = "completed"
    StatusFailed     = "failed"
    StatusRetrying   = "retrying"
)
```

### ProcessedProtocol

```go
type ProcessedProtocol struct {
    Protocol    string    `json:"protocol"`
    System      string    `json:"system"`
    FileType    string    `json:"file_type"`
    ProcessedAt time.Time `json:"processed_at"`
}
```

### RetryItem

```go
type RetryItem struct {
    ID           string    `json:"id"`
    ItemType     string    `json:"item_type"`
    ReferenceID  string    `json:"reference_id"`
    Payload      string    `json:"payload"`
    RetryCount   int       `json:"retry_count"`
    MaxRetries   int       `json:"max_retries"`
    NextRetryAt  time.Time `json:"next_retry_at"`
    LastError    string    `json:"last_error,omitempty"`
    CreatedAt    time.Time `json:"created_at"`
    UpdatedAt    time.Time `json:"updated_at"`
}

const (
    RetryTypeUpload   = "upload"
    RetryTypeCallback = "callback"
    RetryTypeWebhook  = "webhook"
)
```

## Store Interface

```go
// internal/store/store.go
type Store interface {
    // Outbound files
    SaveOutboundFile(file *OutboundFile) error
    GetOutboundFile(id string) (*OutboundFile, error)
    UpdateOutboundFileStatus(id, status string, staState *STAState) error
    ListOutboundFiles(status string, limit, offset int) ([]*OutboundFile, error)

    // Processed protocols (inbound)
    IsProcessed(protocol string) bool
    MarkProcessed(protocol, system string) error
    GetProcessedCount(system string) (int, error)

    // Retry queue
    AddToRetryQueue(item *RetryItem) error
    GetNextRetryItems(limit int) ([]*RetryItem, error)
    UpdateRetryItem(id string, retryCount int, nextRetryAt time.Time, lastError string) error
    RemoveFromRetryQueue(id string) error

    // Config overrides
    GetConfigOverride(key string) (string, error)
    SetConfigOverride(key, value string) error
    DeleteConfigOverride(key string) error

    // Lifecycle
    Close() error
}

// Errors
var (
    ErrNotFound = errors.New("record not found")
)
```

## SQLite Implementation

```go
// internal/store/sqlite.go
type SQLiteStore struct {
    db     *sql.DB
    logger *slog.Logger
}

func NewSQLiteStore(path string) (*SQLiteStore, error) {
    db, err := sql.Open("sqlite3", path+"?_journal_mode=WAL&_busy_timeout=5000")
    if err != nil {
        return nil, fmt.Errorf("open database: %w", err)
    }

    store := &SQLiteStore{
        db:     db,
        logger: slog.Default(),
    }

    if err := store.migrate(); err != nil {
        return nil, fmt.Errorf("run migrations: %w", err)
    }

    return store, nil
}

func (s *SQLiteStore) SaveOutboundFile(file *OutboundFile) error {
    staStateJSON, _ := json.Marshal(file.STAState)

    _, err := s.db.Exec(`
        INSERT INTO outbound_files (
            id, protocol, file_name, file_type, file_size, checksum,
            source_system, status, sta_state, retry_count, last_error,
            created_at, updated_at, completed_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            status = excluded.status,
            sta_state = excluded.sta_state,
            retry_count = excluded.retry_count,
            last_error = excluded.last_error,
            updated_at = excluded.updated_at,
            completed_at = excluded.completed_at
    `,
        file.ID, file.Protocol, file.FileName, file.FileType,
        file.FileSize, file.Checksum, file.SourceSystem, file.Status,
        string(staStateJSON), file.RetryCount, file.LastError,
        file.CreatedAt, file.UpdatedAt, file.CompletedAt,
    )

    return err
}

func (s *SQLiteStore) GetOutboundFile(id string) (*OutboundFile, error) {
    row := s.db.QueryRow(`
        SELECT id, protocol, file_name, file_type, file_size, checksum,
               source_system, status, sta_state, retry_count, last_error,
               created_at, updated_at, completed_at
        FROM outbound_files
        WHERE id = ?
    `, id)

    file := &OutboundFile{}
    var staStateJSON sql.NullString
    var completedAt sql.NullTime

    err := row.Scan(
        &file.ID, &file.Protocol, &file.FileName, &file.FileType,
        &file.FileSize, &file.Checksum, &file.SourceSystem, &file.Status,
        &staStateJSON, &file.RetryCount, &file.LastError,
        &file.CreatedAt, &file.UpdatedAt, &completedAt,
    )

    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, err
    }

    if staStateJSON.Valid {
        json.Unmarshal([]byte(staStateJSON.String), &file.STAState)
    }
    if completedAt.Valid {
        file.CompletedAt = &completedAt.Time
    }

    return file, nil
}

func (s *SQLiteStore) IsProcessed(protocol string) bool {
    var count int
    err := s.db.QueryRow(
        "SELECT COUNT(*) FROM processed_protocols WHERE protocol = ?",
        protocol,
    ).Scan(&count)

    return err == nil && count > 0
}

func (s *SQLiteStore) MarkProcessed(protocol, system string) error {
    _, err := s.db.Exec(`
        INSERT OR IGNORE INTO processed_protocols (protocol, system, processed_at)
        VALUES (?, ?, ?)
    `, protocol, system, time.Now())

    return err
}
```

## PostgreSQL Configuration

For production with multiple instances, use PostgreSQL:

```yaml
# config.yaml
state_store:
  type: "postgres"
  postgres:
    host: "postgres"
    port: 5432
    database: "sta_connector"
    username: "connector"
    password: "${DB_PASSWORD}"
    ssl_mode: "require"
    max_connections: 25
    max_idle_connections: 5
```

PostgreSQL provides:

- **Connection pooling** for multiple instances
- **Row-level locking** for concurrent access
- **Better performance** under high load
- **Replication support** for high availability

## Query Patterns

### Common Queries

```go
// List pending files
func (s *SQLiteStore) ListPendingFiles(limit int) ([]*OutboundFile, error) {
    return s.ListOutboundFiles(StatusPending, limit, 0)
}

// Count files by status
func (s *SQLiteStore) CountByStatus() (map[string]int, error) {
    rows, err := s.db.Query(`
        SELECT status, COUNT(*) FROM outbound_files GROUP BY status
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    counts := make(map[string]int)
    for rows.Next() {
        var status string
        var count int
        if err := rows.Scan(&status, &count); err != nil {
            return nil, err
        }
        counts[status] = count
    }

    return counts, nil
}

// Get files for retry
func (s *SQLiteStore) GetFilesForRetry(limit int) ([]*OutboundFile, error) {
    rows, err := s.db.Query(`
        SELECT id, protocol, file_name, file_type, file_size, checksum,
               source_system, status, sta_state, retry_count, last_error,
               created_at, updated_at, completed_at
        FROM outbound_files
        WHERE status = ? AND retry_count < 3
        ORDER BY updated_at ASC
        LIMIT ?
    `, StatusFailed, limit)

    // ... scan rows
}
```

### Metrics Queries

```go
// Get processing statistics
func (s *SQLiteStore) GetStats(since time.Time) (*Stats, error) {
    row := s.db.QueryRow(`
        SELECT
            COUNT(*) FILTER (WHERE status = 'completed') as completed,
            COUNT(*) FILTER (WHERE status = 'failed') as failed,
            COUNT(*) FILTER (WHERE status = 'pending') as pending,
            COUNT(*) FILTER (WHERE status IN ('uploading', 'uploaded', 'processing')) as processing
        FROM outbound_files
        WHERE created_at >= ?
    `, since)

    stats := &Stats{}
    err := row.Scan(&stats.Completed, &stats.Failed, &stats.Pending, &stats.Processing)
    return stats, err
}
```

## Best Practices

### 1. Use Prepared Statements

```go
stmt, err := db.Prepare("SELECT * FROM outbound_files WHERE id = ?")
defer stmt.Close()
```

### 2. Transaction Management

```go
tx, err := db.Begin()
if err != nil {
    return err
}
defer tx.Rollback()

// Perform operations...

return tx.Commit()
```

### 3. Connection Pool Settings

```go
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
```

### 4. Index Important Columns

```sql
CREATE INDEX idx_outbound_files_status ON outbound_files(status);
CREATE INDEX idx_outbound_files_created_at ON outbound_files(created_at);
```

### 5. Use WAL Mode for SQLite

```go
db, err := sql.Open("sqlite3", "state.db?_journal_mode=WAL")
```

## Next Steps

- [Concurrency Design](/architecture/concurrency) - Go concurrency patterns
- [Architecture Overview](/architecture/) - System components
- [API Reference](/api/) - REST API documentation