Database Schema

The FIM module uses multiple database schemas to store and manage file integrity monitoring data. The architecture includes both local FIMDB databases for state comparison and sync protocol databases for reliable message persistence.

Wazuh Common Schema (WCS)

The schemas according to the Wazuh Common Schema (WCS) are available in src/external/indexer-plugins and are downloaded during the agent build process as part of external dependencies (make deps):

• fim-files.json
• fim-registry-keys.json
• fim-registry-values.json

These schemas define the standardized format for FIM data that is sent to the Wazuh indexer.

FIM Local Databases (FIMDB + DBSync)

FIM uses FIMDB as a wrapper around DBSync to maintain local SQLite databases for storing current file and registry states for comparison during monitoring. The actual database operations are handled by DBSync, while FIMDB provides FIM-specific functionality.

File Entries Table

Stores metadata for monitored files:

CREATE TABLE IF NOT EXISTS file_entry (
    path TEXT NOT NULL,
    checksum TEXT NOT NULL,
    device INTEGER,
    inode INTEGER,
    size INTEGER,
    permissions TEXT,
    attributes TEXT,
    uid TEXT,
    gid TEXT,
    owner TEXT,
    group_ TEXT,
    hash_md5 TEXT,
    hash_sha1 TEXT,
    hash_sha256 TEXT,
    mtime INTEGER,
    version INTEGER NOT NULL DEFAULT 1,
    sync INTEGER DEFAULT 0,
    PRIMARY KEY(path)
) WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS path_index ON file_entry (path);
CREATE INDEX IF NOT EXISTS inode_index ON file_entry (device, inode);

Field Descriptions

Registry Key Table (Windows Only)

Stores metadata for monitored registry keys:

CREATE TABLE IF NOT EXISTS registry_key (
    path TEXT NOT NULL,
    permissions TEXT,
    uid TEXT,
    gid TEXT,
    owner TEXT,
    group_ TEXT,
    mtime INTEGER,
    architecture TEXT CHECK (architecture IN ('[x32]', '[x64]')),
    checksum TEXT NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    sync INTEGER DEFAULT 0,
    PRIMARY KEY (architecture, path)
) WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS path_index ON registry_key (path);

Field Descriptions

Primary Key: (architecture, path) - Allows same path in different architectures

Registry Data Table (Windows Only)

Stores individual registry values:

CREATE TABLE IF NOT EXISTS registry_data (
    path TEXT,
    architecture TEXT CHECK (architecture IN ('[x32]', '[x64]')),
    value TEXT NOT NULL,
    type INTEGER,
    size INTEGER,
    hash_md5 TEXT,
    hash_sha1 TEXT,
    hash_sha256 TEXT,
    checksum TEXT NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    sync INTEGER DEFAULT 0,
    PRIMARY KEY(path, architecture, value),
    FOREIGN KEY (path) REFERENCES registry_key(path),
    FOREIGN KEY (architecture) REFERENCES registry_key(architecture)
) WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS key_name_index ON registry_data (path, value);

Field Descriptions

Primary Key: (path, architecture, value) - Unique per registry value Foreign Keys: References registry_key table

Table Metadata

Tracks synchronization state for FIM tables to support recovery operations:

CREATE TABLE IF NOT EXISTS table_metadata (
    table_name TEXT PRIMARY KEY,
    last_sync_time INTEGER NOT NULL
);

Field Descriptions

Purpose

The table_metadata table supports FIM recovery functionality by tracking each table's last synchronization attempt. This allows the system to trigger recovery operations with the frequency specified by the integtrity_interal option's value.

Tracked Tables:

• file_entry - File monitoring state
• registry_key - Registry key monitoring state (Windows only)
• registry_data - Registry value monitoring state (Windows only)

Sync Protocol Database

The sync protocol maintains its own persistence layer in a separate SQLite database for reliable message delivery.

Persistent Queue Table

CREATE TABLE IF NOT EXISTS persistent_queue (
    id TEXT PRIMARY KEY NOT NULL,
    idx TEXT NOT NULL,
    data TEXT NOT NULL,
    operation INTEGER NOT NULL,
    sync_status INTEGER NOT NULL DEFAULT 0,
    create_status INTEGER NOT NULL DEFAULT 0,
    operation_syncing INTEGER NOT NULL DEFAULT 3
);

Field Descriptions

Sync Status Values

enum class SyncStatus : int {
    PENDING = 0,        // Message waiting to be synchronized
    SYNCING = 1,        // Message currently being synchronized
    SYNCING_UPDATED = 2 // Message being synchronized with updated contents
};

Create Status Values

enum class CreateStatus : int {
    EXISTING = 0,     // Message existed prior to current session
    NEW = 1,          // Message newly created during current session
    NEW_DELETED = 2   // Message created then deleted before sync
};

Database Operations Flow

State Comparison Process

1. File System Scan: FIM detects file/registry changes
2. Database Transaction: Start transaction with FIMDB
3. State Comparison: Compare current state with stored state
4. Change Detection: Determine operation type (create/update/delete)
5. Event Generation: Generate stateless and stateful events
6. Persistence: Store stateful event in sync protocol database

Database Maintenance

Index Optimization

Both databases use indexes to optimize common queries:

FIMDB Indexes:

• path_index: Fast file path lookups
• inode_index: Detect file moves/renames
• key_name_index: Registry value lookups

Sync Protocol: Uses primary key for fast ID-based operations

Database File Locations

Fixed Paths

Database files are stored in fixed locations relative to Wazuh installation:

• FIM Database: queue/fim/db/fim.db
• Sync Protocol Database: queue/fim/db/fim_sync.db

Database Configuration

SQLite Pragmas used for optimization:

-- Sync protocol database optimizations
PRAGMA synchronous = NORMAL;    -- Balanced durability/performance
PRAGMA journal_mode = WAL;      -- Write-Ahead Logging for concurrency