> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coreflux.org/llms.txt
> Use this file to discover all available pages before exploring further.

# File Storage Route

> Store MQTT data to local CSV and JSON files for simple persistence and backups

## File Storage Overview

The `FILE_STORAGE` route writes MQTT messages to local CSV or JSON files. It provides simple persistence for logging, backups, and data export without requiring a database server.

<Note>
  File storage is ideal for simple logging, local backups, and exporting data for offline analysis. Use it when you don't need a full database but want persistent records.
</Note>

## Basic Syntax

```lot theme={null}
DEFINE ROUTE DataLogger WITH TYPE FILE_STORAGE
    ADD FILE_STORAGE_CONFIG
        WITH STORAGE_DIR "/data/logs"
        WITH FORMAT "CSV"
        WITH FILE_PATTERN "{0}_{date}.csv"
        WITH APPEND "true"
    ADD EVENT LogSensorData
        WITH SOURCE_TOPIC "sensors/#"
        WITH QUERY "CLEAN:{table: sensor_data, data: {value.json}}"
```

***

## Connection Configuration

### FILE\_STORAGE\_CONFIG Parameters

<ParamField path="STORAGE_DIR" type="string" required>
  Directory path for storing files.
</ParamField>

<AccordionGroup>
  <Accordion title="Storage Location Options">
    <ParamField path="FILE_PATTERN" type="string">
      File name pattern. Use `{0}` for table name, `{date}` for current date. Default: `{0}.csv`
    </ParamField>
  </Accordion>

  <Accordion title="Format">
    <ParamField path="FORMAT" type="string">
      File format: `CSV` or `JSON`. Default: CSV.
    </ParamField>

    <ParamField path="CSV_DELIMITER" type="string">
      CSV delimiter character. Default: comma (`,`).
    </ParamField>
  </Accordion>

  <Accordion title="Write Options">
    <ParamField path="APPEND" type="boolean">
      Append to existing files. Default: false (overwrite).
    </ParamField>

    <ParamField path="INCLUDE_TIMESTAMP" type="boolean">
      Auto-add timestamp field. Default: false.
    </ParamField>

    <ParamField path="MAX_RECORDS" type="integer">
      Maximum records per file before rotation. Default: 10000.
    </ParamField>
  </Accordion>

  <Accordion title="Timestamp">
    <ParamField path="DATE_FORMAT" type="string">
      Timestamp format string. Default: `yyyy-MM-dd HH:mm:ss`.
    </ParamField>
  </Accordion>
</AccordionGroup>

<Warning>
  **Use environment variables for paths in production.** Keep deployment-specific values out of your route definitions:

  ```lot theme={null}
  WITH STORAGE_DIR GET ENV "STORAGE_PATH"
  ```

  See [Environment Variables & Secrets](/mqtt-broker/secrets-and-env) for setup and usage.
</Warning>

***

## CLEAN Query Format

File storage routes use the CLEAN format:

```lot theme={null}
WITH QUERY "CLEAN:{table: <name>, data: {value.json}}"
```

The `table` name is used in the file pattern with `{0}`.

***

## Writing Data

<Tabs>
  <Tab title="CSV Logging">
    Log sensor data to daily CSV files:

    ```lot theme={null}
    DEFINE ROUTE CSVLogger WITH TYPE FILE_STORAGE
        ADD FILE_STORAGE_CONFIG
            WITH STORAGE_DIR "/data/logs"
            WITH FORMAT "CSV"
            WITH FILE_PATTERN "{0}_{date}.csv"
            WITH APPEND "true"
            WITH CSV_DELIMITER ","
            WITH INCLUDE_TIMESTAMP "true"
            WITH MAX_RECORDS "10000"
        ADD EVENT LogSensorData
            WITH SOURCE_TOPIC "sensors/#"
            WITH DESTINATION_TOPIC "file/status"
            WITH QUERY "CLEAN:{table: sensor_data, data: {value.json}}"
    ```

    Creates files like: `sensor_data_2025-01-15.csv`
  </Tab>

  <Tab title="JSON Storage">
    Store events as JSON lines:

    ```lot theme={null}
    DEFINE ROUTE JSONLogger WITH TYPE FILE_STORAGE
        ADD FILE_STORAGE_CONFIG
            WITH STORAGE_DIR "/data/json"
            WITH FORMAT "JSON"
            WITH FILE_PATTERN "{0}.json"
            WITH APPEND "true"
            WITH INCLUDE_TIMESTAMP "true"
            WITH MAX_RECORDS "5000"
        ADD EVENT LogEvents
            WITH SOURCE_TOPIC "events/#"
            WITH DESTINATION_TOPIC "file/json/status"
            WITH QUERY "CLEAN:{table: events, data: {value.json}}"
    ```
  </Tab>

  <Tab title="Multiple Files">
    Route to different files by data type:

    ```lot theme={null}
    DEFINE ROUTE MultiFileLogger WITH TYPE FILE_STORAGE
        ADD FILE_STORAGE_CONFIG
            WITH STORAGE_DIR "/data/logs"
            WITH FORMAT "CSV"
            WITH FILE_PATTERN "{0}_{date}.csv"
            WITH APPEND "true"
            WITH INCLUDE_TIMESTAMP "true"
        
        ADD EVENT LogTemperature
            WITH SOURCE_TOPIC "sensors/+/temperature"
            WITH QUERY "CLEAN:{table: temperature, data: {value.json}}"
        
        ADD EVENT LogPressure
            WITH SOURCE_TOPIC "sensors/+/pressure"
            WITH QUERY "CLEAN:{table: pressure, data: {value.json}}"
        
        ADD EVENT LogAlerts
            WITH SOURCE_TOPIC "alerts/#"
            WITH QUERY "CLEAN:{table: alerts, data: {value.json}}"
    ```

    Creates: `temperature_2025-01-15.csv`, `pressure_2025-01-15.csv`, `alerts_2025-01-15.csv`
  </Tab>

  <Tab title="Backup Storage">
    Create backup files with rotation:

    ```lot theme={null}
    DEFINE ROUTE BackupStorage WITH TYPE FILE_STORAGE
        ADD FILE_STORAGE_CONFIG
            WITH STORAGE_DIR "/backup/mqtt"
            WITH FORMAT "JSON"
            WITH FILE_PATTERN "{0}_{date}.jsonl"
            WITH APPEND "true"
            WITH MAX_RECORDS "100000"
            WITH INCLUDE_TIMESTAMP "true"
        ADD EVENT BackupAll
            WITH SOURCE_TOPIC "#"
            WITH QUERY "CLEAN:{table: mqtt_backup, data: {value.json}}"
    ```
  </Tab>
</Tabs>

<Info>
  **Alternative: STORE IN with Models** — Instead of writing EVENT queries, you can bind a [model](/lot-language/models/overview) directly to this route. Every `PUBLISH MODEL` call automatically inserts a row — no query needed:

  ```lot theme={null}
  DEFINE MODEL SensorReading
      ADD STRING "sensor_id"
      ADD DOUBLE "value"
      STORE IN "DataLogger"
          WITH TABLE "sensor_data"
  ```

  See [Data Storage Overview](./overview#with-models-store-in) for the full STORE IN workflow.
</Info>

***

## Reading Data

EVENTs also support read queries to retrieve data from stored files. Publish a message to the event's `SOURCE_TOPIC`, and the query result is published to `DESTINATION_TOPIC`. File Storage read queries use the CLEAN format with `filter`, `sort`, and `limit` instead of SQL:

```lot theme={null}
DEFINE ROUTE DataLogger WITH TYPE FILE_STORAGE
    ADD FILE_STORAGE_CONFIG
        WITH STORAGE_DIR "/data/logs"
        WITH FORMAT "CSV"
        WITH FILE_PATTERN "{0}_{date}.csv"
        WITH APPEND "true"
    ADD EVENT GetLatestEntry
        WITH SOURCE_TOPIC "file/query/latest"
        WITH DESTINATION_TOPIC "file/result/latest"
        WITH QUERY "CLEAN:{table: sensor_data, filter: {\"sensor_id\": \"{payload}\"}, sort: {\"timestamp\": -1}, limit: 1}"
```

To trigger this query, publish the sensor ID to the source topic:

```
Topic:   file/query/latest
Payload: temp001
```

The matching record is published to `file/result/latest`, where your actions or external clients can consume it.

### CLEAN Read Operators

| SQL Equivalent    | File Storage CLEAN Syntax      |
| ----------------- | ------------------------------ |
| `SELECT *`        | `{"table": "x"}`               |
| `WHERE a = 1`     | `filter: {"a": 1}`             |
| `WHERE a > 10`    | `filter: {"a": {"$gt": 10}}`   |
| `ORDER BY a DESC` | `sort: {"a": -1}`              |
| `LIMIT 10`        | `limit: 10`                    |
| `OFFSET 5`        | `skip: 5`                      |
| `SELECT a, b`     | `projection: {"a": 1, "b": 1}` |

***

## File Pattern Placeholders

| Placeholder | Description                 | Example       |
| ----------- | --------------------------- | ------------- |
| `{0}`       | Table name from CLEAN query | `sensor_data` |
| `{date}`    | Current date (yyyy-MM-dd)   | `2025-01-15`  |

***

## Output Examples

### CSV Output

```csv theme={null}
timestamp,sensor_id,temperature,humidity
2025-01-15 10:30:00,temp001,23.5,65
2025-01-15 10:30:05,temp002,24.1,62
```

### JSON Output (JSON Lines format)

```json theme={null}
{"timestamp":"2025-01-15T10:30:00Z","sensor_id":"temp001","temperature":23.5,"humidity":65}
{"timestamp":"2025-01-15T10:30:05Z","sensor_id":"temp002","temperature":24.1,"humidity":62}
```

***

## Troubleshooting

<AccordionGroup>
  <Accordion title="Permission Denied">
    * Verify STORAGE\_DIR exists
    * Check write permissions on directory
    * Ensure Coreflux process has access
  </Accordion>

  <Accordion title="File Not Created">
    * Verify STORAGE\_DIR path is correct
    * Check disk space is available
    * Ensure directory is writable
  </Accordion>

  <Accordion title="Data Not Appearing">
    * Verify SOURCE\_TOPIC matches incoming messages
    * Check QUERY syntax is correct
    * Verify APPEND is set correctly
  </Accordion>

  <Accordion title="File Rotation Issues">
    * Check MAX\_RECORDS is set appropriately
    * Verify FILE\_PATTERN includes `{date}` for daily rotation
  </Accordion>
</AccordionGroup>

***

## Next Steps

<CardGroup cols={2}>
  <Card title="Data Storage Routes Overview" icon="database" href="./overview">
    Compare all storage options.
  </Card>

  <Card title="PostgreSQL Route" icon="elephant" href="./postgresql">
    Configure PostgreSQL database storage.
  </Card>
</CardGroup>