# autoSMART Database Documentation

## Overview

autoSMART uses PostgreSQL as its primary database for storing SMART data, HDD tracking information, predictions, and system configuration. The database is designed for multi-node cluster deployments with comprehensive HDD mobility tracking.

## Database Schema

### Core Tables

#### `hdd_inventory`

The central inventory table that tracks all HDDs across the cluster.

```sql

CREATE TABLE hdd_inventory (

    id                  SERIAL PRIMARY KEY,

    serial_number       VARCHAR(100) NOT NULL,

    model_name          VARCHAR(200) NOT NULL,

    firmware            VARCHAR(50),

    size_gb             INTEGER,

    manufacturer        VARCHAR(100),

    current_device_path VARCHAR(50),

    current_node_id     VARCHAR(50),

    current_slot        VARCHAR(20),

    madagascar_id       VARCHAR(100),

    first_seen          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    last_seen           TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    status              VARCHAR(20) DEFAULT 'active',

    status_changed_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    notes               TEXT,

    created_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT unique_hardware_id UNIQUE (serial_number, model_name)

);

```

**Key Features:**

- **Hardware-based identification**: Uses `serial_number` + `model_name` as unique constraint

- **Current location tracking**: `current_device_path`, `current_node_id` show where HDD is now

- **Lifecycle management**: `first_seen`, `last_seen`, `status` track HDD lifecycle

- **Madagascar integration**: `madagascar_id` field for cluster-specific identification

#### `hdd_presence`

Tracks HDD mobility across cluster nodes - records when HDDs are present on different nodes.

```sql

CREATE TABLE hdd_presence (

    id SERIAL PRIMARY KEY,

    serial_number VARCHAR(64) NOT NULL,

    node VARCHAR(64) NOT NULL,

    data_start TIMESTAMP NOT NULL,

    data_end TIMESTAMP NOT NULL,

    is_current BOOLEAN NOT NULL DEFAULT TRUE

);

```

**Key Features:**

- **Mobility tracking**: Records when HDDs move between nodes

- **Time-based records**: `data_start`/`data_end` define presence periods

- **Current vs Historic**: `is_current` flag marks active presence

- **Independent of inventory**: Works independently of `hdd_inventory` for pure mobility data

**Example Data:**

```sql

 id | serial_number  |   node    |         data_start         |          data_end          | is_current

----+----------------+-----------+----------------------------+----------------------------+------------

  4 | ZW60K01R       | ebony     | 2025-08-16 22:05:15.863971 | 2025-08-16 22:05:15.863971 | t

  3 | S2HSNXRH402205 | ebony     | 2025-08-16 22:05:15.109956 | 2025-08-16 22:05:15.109956 | t

  2 | ZW60K01R       | baobab    | 2025-08-16 21:47:13.873642 | 2025-08-16 22:03:31.052316 | f

  1 | S2HSNXRH402205 | tapia     | 2025-08-16 21:47:13.078524 | 2025-08-16 22:03:30.268985 | f

```

#### `smart_readings`

Stores SMART data readings with differential storage optimization.

```sql

CREATE TABLE smart_readings (

    id                   BIGSERIAL PRIMARY KEY,

    hdd_id               INTEGER REFERENCES hdd_inventory(id),

    serial_number        VARCHAR(100) NOT NULL,

    device_path          VARCHAR(50),

    node_id              VARCHAR(50),

    timestamp            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    collection_ok        BOOLEAN DEFAULT true,

    temperature          INTEGER,

    parameters_json      JSONB,

    reading_type         VARCHAR(20) DEFAULT 'full',

    changes_detected     BOOLEAN DEFAULT true,

    changed_parameters   JSONB,

    previous_reading_id  INTEGER REFERENCES smart_readings(id),

    checksum             VARCHAR(64)

);

```

**Reading Types:**

- `baseline`: First reading for an HDD

- `full`: Complete parameter set (forced by time interval)

- `differential`: Only changed parameters (optimization)

- `skipped`: No changes detected

**Key Features:**

- **Differential storage**: Only stores changes to reduce data volume

- **Full context**: Links to `hdd_inventory` and includes node information

- **Change tracking**: `previous_reading_id` creates reading chains

- **JSONB parameters**: Flexible storage for SMART attributes

#### `predictions`

AI-generated failure predictions and analysis.

```sql

CREATE TABLE predictions (

    id                    SERIAL PRIMARY KEY,

    hdd_id                INTEGER REFERENCES hdd_inventory(id),

    serial_number         VARCHAR(100) NOT NULL,

    device_path           VARCHAR(50),

    timestamp             TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    risk_level            VARCHAR(20),

    failure_probability   DECIMAL(5,4),

    predicted_failure_date DATE,

    confidence_score      DECIMAL(5,4),

    analysis_summary      TEXT,

    recommendations       JSONB,

    openai_response       JSONB,

    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

```

#### `alert_history`

Tracks all alerts sent about HDD issues.

```sql

CREATE TABLE alert_history (

    id              SERIAL PRIMARY KEY,

    hdd_id          INTEGER REFERENCES hdd_inventory(id),

    serial_number   VARCHAR(100) NOT NULL,

    alert_type      VARCHAR(50),

    severity        VARCHAR(20),

    message         TEXT,

    sent_at         TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    sent_to         TEXT,

    delivery_status VARCHAR(20) DEFAULT 'pending',

    related_reading_id BIGINT REFERENCES smart_readings(id),

    related_prediction_id INTEGER REFERENCES predictions(id)

);

```

### Configuration Tables

#### `system_config`

Global system configuration parameters.

```sql

CREATE TABLE system_config (

    id          SERIAL PRIMARY KEY,

    config_key  VARCHAR(100) UNIQUE NOT NULL,

    value       TEXT,

    description TEXT,

    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

```

**Default Configuration:**

- `collection_interval_seconds`: SMART data collection frequency

- `differential_storage_enabled`: Enable/disable storage optimization

- `forced_storage_interval_hours`: Force full readings periodically

- `critical_parameter_force_store`: Always store critical changes

- `temperature_change_threshold`: Temperature delta for storage

#### `smart_thresholds`

SMART parameter warning and critical thresholds.

```sql

CREATE TABLE smart_thresholds (

    id                SERIAL PRIMARY KEY,

    parameter_name    VARCHAR(100) NOT NULL,

    warning_threshold NUMERIC,

    critical_threshold NUMERIC,

    weight            NUMERIC DEFAULT 1.0,

    enabled           BOOLEAN DEFAULT true,

    description       TEXT,

    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

```

## Views

### `smart_readings_reconstructed`

Reconstructs complete SMART data from differential storage.

```sql

CREATE VIEW smart_readings_reconstructed AS

WITH RECURSIVE reading_chain AS (

    -- Base case: get baseline readings

    SELECT id, hdd_id, serial_number, timestamp,

           parameters_json, temperature, reading_type,

           previous_reading_id, 1 as chain_level

    FROM smart_readings

    WHERE reading_type IN ('baseline', 'full')

    UNION ALL

    -- Recursive case: follow the chain of differential readings

    SELECT sr.id, sr.hdd_id, sr.serial_number, sr.timestamp,

           COALESCE(rc.parameters_json, '{}'::jsonb) || sr.parameters_json as parameters_json,

           COALESCE(sr.temperature, rc.temperature) as temperature,

           sr.reading_type, sr.previous_reading_id,

           rc.chain_level + 1

    FROM smart_readings sr

    JOIN reading_chain rc ON sr.previous_reading_id = rc.id

    WHERE sr.reading_type = 'differential'

)

SELECT id, hdd_id, serial_number, timestamp,

       parameters_json, temperature, reading_type, chain_level

FROM reading_chain;

```

### `latest_smart_readings`

Current SMART status for all active drives.

```sql

CREATE VIEW latest_smart_readings AS

SELECT DISTINCT ON (sr.hdd_id)

    sr.id, sr.hdd_id, sr.serial_number, sr.timestamp,

    sr.parameters_json, sr.temperature,

    hi.model_name, hi.manufacturer, hi.size_gb,

    hi.current_device_path, hi.current_node_id

FROM smart_readings_reconstructed sr

JOIN hdd_inventory hi ON sr.hdd_id = hi.id

ORDER BY sr.hdd_id, sr.timestamp DESC;

```

### `drive_health_summary`

Comprehensive health overview for all drives.

```sql

CREATE VIEW drive_health_summary AS

SELECT

    hi.id as hdd_id, hi.serial_number, hi.model_name,

    hi.manufacturer, hi.current_device_path, hi.current_node_id,

    hi.status, lsr.timestamp as last_reading, lsr.temperature,

    p.risk_level, p.failure_probability, p.predicted_failure_date,

    EXTRACT(EPOCH FROM (NOW() - lsr.timestamp))/3600 as hours_since_last_reading

FROM hdd_inventory hi

LEFT JOIN latest_smart_readings lsr ON hi.id = lsr.hdd_id

LEFT JOIN LATERAL (

    SELECT risk_level, failure_probability, predicted_failure_date

    FROM predictions

    WHERE hdd_id = hi.id

    ORDER BY timestamp DESC

    LIMIT 1

) p ON true

WHERE hi.status = 'active';

```

## Functions

### `update_hdd_presence()`

Manages HDD presence tracking when a drive is detected on a node.

```sql

CREATE OR REPLACE FUNCTION update_hdd_presence(

    p_serial_number VARCHAR(64),

    p_node VARCHAR(64)

) RETURNS VOID AS $$

BEGIN

    -- Mark all previous presence records for this serial as historic

    UPDATE hdd_presence

    SET is_current = FALSE

    WHERE serial_number = p_serial_number AND is_current = TRUE AND node <> p_node;

    -- Check if there's already a current presence for this serial/node

    IF EXISTS (SELECT 1 FROM hdd_presence WHERE serial_number = p_serial_number AND node = p_node AND is_current = TRUE) THEN

        -- Update data_end for existing current presence

        UPDATE hdd_presence

        SET data_end = NOW()

        WHERE serial_number = p_serial_number AND node = p_node AND is_current = TRUE;

    ELSE

        -- Create new presence record

        INSERT INTO hdd_presence (serial_number, node, data_start, data_end, is_current)

        VALUES (p_serial_number, p_node, NOW(), NOW(), TRUE);

    END IF;

END;

$$ LANGUAGE plpgsql;

```

### `should_store_smart_reading()`

Determines if a SMART reading should be stored based on differential storage logic.

```sql

CREATE OR REPLACE FUNCTION should_store_smart_reading(

    p_hdd_id INTEGER,

    p_parameters_json JSONB,

    p_checksum VARCHAR(64),

    p_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()

) RETURNS TABLE(

    should_store BOOLEAN,

    reading_type VARCHAR(20),

    changes_detected BOOLEAN,

    changed_parameters JSONB,

    previous_reading_id INTEGER

) AS $$

-- Function implementation handles:

-- - Differential storage enabled/disabled

-- - Checksum-based change detection

-- - Force intervals for full readings

-- - Reading type determination

$$;

```

## Indexes

### Performance Indexes

```sql

-- hdd_inventory indexes

CREATE INDEX idx_hdd_inventory_device_path ON hdd_inventory(current_device_path);

CREATE INDEX idx_hdd_inventory_node ON hdd_inventory(current_node_id);

CREATE INDEX idx_hdd_inventory_status ON hdd_inventory(status);

CREATE INDEX idx_hdd_inventory_last_seen ON hdd_inventory(last_seen);

-- hdd_presence indexes

CREATE INDEX idx_hdd_presence_serial_current ON hdd_presence(serial_number, is_current);

CREATE INDEX idx_hdd_presence_node ON hdd_presence(node);

CREATE INDEX idx_hdd_presence_data_end ON hdd_presence(data_end DESC);

-- smart_readings indexes

CREATE INDEX idx_smart_readings_hdd_id ON smart_readings(hdd_id);

CREATE INDEX idx_smart_readings_timestamp ON smart_readings(timestamp DESC);

CREATE INDEX idx_smart_readings_serial ON smart_readings(serial_number);

CREATE INDEX idx_smart_readings_device_path ON smart_readings(device_path);

CREATE INDEX idx_smart_readings_type ON smart_readings(reading_type);

CREATE INDEX idx_smart_readings_checksum ON smart_readings(checksum);

CREATE INDEX idx_smart_readings_previous ON smart_readings(previous_reading_id);

-- JSONB indexes for flexible queries

CREATE INDEX idx_smart_readings_parameters ON smart_readings USING GIN (parameters_json);

CREATE INDEX idx_smart_readings_changed_params ON smart_readings USING GIN (changed_parameters);

```

## Data Flow

### Collection Process

1. **Device Discovery**: Collector scans `/dev/sd*` and `/dev/nvme*` devices

2. **SMART Reading**: Uses `smartctl` to extract device information and parameters

3. **HDD Registration**: `get_or_create_hdd()` adds new devices to `hdd_inventory`

4. **Presence Tracking**: `update_hdd_presence()` records current node location

5. **Data Storage**: Stores SMART readings with differential optimization

6. **Change Detection**: Uses checksums to detect parameter changes

### Mobility Tracking

1. **HDD Detected**: When HDD is found on a new node

2. **Historic Records**: Previous presence records marked `is_current = FALSE`

3. **New Presence**: New record created with `is_current = TRUE`

4. **Timeline**: Complete history maintained with `data_start`/`data_end` timestamps

### Query Examples

#### Find HDD History

```sql

SELECT serial_number, node, data_start, data_end, is_current

FROM hdd_presence

WHERE serial_number = 'ZW60K01R'

ORDER BY data_start DESC;

```

#### Current HDD Locations

```sql

SELECT h.serial_number, h.model_name, p.node, h.current_device_path

FROM hdd_inventory h

JOIN hdd_presence p ON h.serial_number = p.serial_number

WHERE p.is_current = TRUE;

```

#### SMART Parameter Trends

```sql

SELECT timestamp,

       parameters_json->>'Power_On_Hours' as power_hours,

       parameters_json->>'Temperature_Celsius' as temp,

       temperature

FROM smart_readings_reconstructed

WHERE serial_number = 'ZW60K01R'

ORDER BY timestamp DESC

LIMIT 10;

```

#### Health Summary

```sql

SELECT * FROM drive_health_summary

WHERE current_node_id = 'ebony';

```

## Troubleshooting

### Common Issues

#### 1. Node ID Mismatch

**Problem**: HDD presence shows wrong node name

**Cause**: Deploy script used local hostname instead of target node name

**Solution**: Deploy script now correctly determines target node name from `cluster.json`

#### 2. Empty hdd_presence Table

**Problem**: No mobility tracking data

**Causes**:

- SMART parameter parsing regex incompatible with new smartctl format

- Missing database sequence permissions

- Incomplete smart_readings INSERT statements

**Solutions**:

- Updated regex to support both old and new smartctl formats

- Added sequence permissions: `GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO autosmart;`

- Fixed INSERT to include all required fields

#### 3. Differential Storage Issues

**Problem**: Too much or too little data stored

**Configuration**: Adjust in `system_config` table:

```sql

UPDATE system_config SET value = 'false' WHERE config_key = 'differential_storage_enabled';

UPDATE system_config SET value = '12' WHERE config_key = 'forced_storage_interval_hours';

```

## Permissions

### Database User Setup

```sql

-- Create autosmart user

CREATE USER autosmart WITH PASSWORD 'autoSMART2025!';

-- Grant permissions

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO autosmart;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO autosmart;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO autosmart;

```

### Sequence Permissions

```sql

-- Required for INSERT operations with SERIAL columns

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO autosmart;

```

## Maintenance

### Regular Tasks

1. **Monitor disk usage**: SMART readings table grows over time

2. **Archive old data**: Consider archiving readings older than 1 year

3. **Index maintenance**: REINDEX periodically for performance

4. **Backup**: Regular PostgreSQL backups recommended

### Performance Monitoring

```sql

-- Table sizes

SELECT schemaname, tablename,

       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size

FROM pg_tables

WHERE schemaname = 'public'

ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Recent activity

SELECT COUNT(*) as readings_today

FROM smart_readings

WHERE timestamp > CURRENT_DATE;

SELECT COUNT(*) as active_drives

FROM hdd_inventory

WHERE status = 'active';

```