Database Storage Backend

By default, Stylus SFTP Server stores user files on the local filesystem. The database storage backend stores files in an external database (MySQL, PostgreSQL, or SQL Server) instead. This enables stateless server instances, simplifies high-availability deployments, and leverages your existing database infrastructure for backup, disaster recovery, and replication.

Files are stored as ref-counted chunked BLOBs — each file is split into 4 MB chunks in a dedicated chunk table. Chunks can be shared between files (copy-on-write), so copying a 500 MB file is instant and uses zero additional storage.

Full folder support — users can create subdirectories, navigate folder trees, and organize files exactly as they would on a local filesystem. Folders are stored as lightweight metadata rows using an adjacency-list model.

How It Works

Supported Databases

Quick-Start Setup

1. SQL Server: Driver is pre-installed. Configure filesystem-config.xml and start.
2. MySQL / PostgreSQL: Copy the JDBC driver JAR into jdbc/, configure, and start.

Step 1 — Install the JDBC Driver

SQL Server: No action needed — the Microsoft JDBC Driver is pre-installed in jdbc/.

MySQL / PostgreSQL: Copy the driver JAR into jdbc/. The server scans this directory at startup.

<install>/
├── libs/          ← server JARs (do NOT put JDBC drivers here)
├── jdbc/          ← JDBC drivers (scanned at startup)
│   ├── mssql-jdbc-12.8.1.jre11.jar   ← pre-installed
│   └── mysql-connector-j-8.3.0.jar   ← customer-provided
└── ...

Step 2 — Configure filesystem-config.xml

SQL Server Example

<storage-backend>database</storage-backend>

<storage-database>
    <url>jdbc:sqlserver://dbhost:1433;databaseName=sftpfiles;encrypt=false</url>
    <username>sftpserver</username>
    <password>secret</password>
    <max-pool-size>10</max-pool-size>
</storage-database>

MySQL Example

<storage-backend>database</storage-backend>

<storage-database>
    <url>jdbc:mysql://dbhost:3306/sftpfiles</url>
    <username>sftpserver</username>
    <password>secret</password>
    <max-pool-size>10</max-pool-size>
</storage-database>

PostgreSQL Example

<storage-backend>database</storage-backend>

<storage-database>
    <url>jdbc:postgresql://dbhost:5432/sftpfiles</url>
    <username>sftpserver</username>
    <password>secret</password>
    <max-pool-size>10</max-pool-size>
</storage-database>

Configuration Elements

<storage-database>

Password Security

Store the database password in credentials.p12 rather than in XML. Use key storage.password. The credential store takes precedence over the XML value.

Step 3 — Start the Server

On startup, the server:

1. Scans jdbc/ and loads all JDBC driver JARs
2. Reads <storage-backend> from filesystem-config.xml
3. Opens a HikariCP connection pool to the database
4. Auto-creates all required tables if they do not exist
5. Cleans any stale file locks from a previous server instance
6. Logs: Storage backend: DATABASE (jdbc:sqlserver://...)

No manual table creation is required.

Database Schema

The database storage backend uses four tables, all created automatically on first startup:

sftp_file_storage — File and Directory Metadata

CREATE TABLE sftp_file_storage (
    file_id       BIGINT        PRIMARY KEY,   -- auto-generated
    username      VARCHAR(100)  NOT NULL,
    filename      VARCHAR(660)  NOT NULL,
    type          CHAR(1)       NOT NULL DEFAULT 'F',  -- 'F' = file, 'D' = directory
    parent_id     BIGINT        NULL,          -- NULL = root level
    file_size     BIGINT        NOT NULL DEFAULT 0,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (username, parent_id, filename)     -- no duplicates in same folder
);

Each row is either a file (type='F') or a directory (type='D'). The parent_id column points to the containing directory's file_id. Root-level items have parent_id = NULL. This adjacency-list model supports arbitrarily deep folder hierarchies.

sftp_chunk_data — Ref-Counted BLOB Chunks

CREATE TABLE sftp_chunk_data (
    chunk_id      BIGINT        PRIMARY KEY,   -- auto-generated
    chunk_data    LONGBLOB      NOT NULL,      -- VARBINARY(MAX) on SQL Server
    ref_count     INT           NOT NULL DEFAULT 1
);

Each chunk holds up to 4 MB of file data. The ref_count column tracks how many files reference this chunk. When a file is copied, the chunks are shared (ref_count incremented) rather than duplicated. When a file is deleted, ref_count is decremented. Chunks with ref_count ≤ 0 are deleted automatically.

sftp_file_chunks — File-to-Chunk Mapping

CREATE TABLE sftp_file_chunks (
    file_id       BIGINT        NOT NULL,
    chunk_index   INT           NOT NULL,
    chunk_id      BIGINT        NOT NULL,
    PRIMARY KEY (file_id, chunk_index),
    FOREIGN KEY (file_id)  REFERENCES sftp_file_storage (file_id) ON DELETE CASCADE,
    FOREIGN KEY (chunk_id) REFERENCES sftp_chunk_data (chunk_id)
);

Maps each file to its ordered sequence of chunks. A 12 MB file has three rows (chunk_index 0, 1, 2). When a file is copied, new mapping rows are created pointing to the same chunk_ids — the BLOB data is never duplicated.

sftp_file_locks — Active Transfer Locks

CREATE TABLE sftp_file_locks (
    lock_id       BIGINT        PRIMARY KEY,   -- auto-generated
    file_id       BIGINT        NOT NULL,
    pid           BIGINT        NOT NULL,      -- server process ID
    lock_type     VARCHAR(5)    NOT NULL,      -- READ, WRITE, IS, IX
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (file_id) REFERENCES sftp_file_storage (file_id) ON DELETE CASCADE
);

Folders and Navigation

The database backend supports full hierarchical folder structures. Users can create directories, navigate into them, upload files at any depth, and manage folder trees — through any protocol (SFTP, FTP, or the Web Portal).

Path Resolution

When a client accesses /reports/2026/march/data.csv, the server walks the path one segment at a time:

1. Look up reports where parent_id IS NULL
2. Look up 2026 where parent_id = reports.file_id
3. Look up march where parent_id = 2026.file_id
4. Look up data.csv where parent_id = march.file_id

Each lookup uses the unique index on (username, parent_id, filename) — a single index seek regardless of how many files are in the database.

Automatic Directory Creation

When a client uploads to a path like /invoices/2026/march/invoice.pdf, the server automatically creates the invoices, 2026, and march directories if they do not exist. No manual mkdir required.

Copy-on-Write

File copy uses a zero-copy technique: only metadata is duplicated. The actual BLOB data is shared between the original and the copy via reference counting.

File Locking Policy

The server uses a hierarchical locking policy to protect files and folders from conflicting concurrent operations. Locks are tracked in the sftp_file_locks table using four lock types:

Lock Types

How It Works

When a user downloads /reports/2026/data.csv, the server acquires:

1. A READ lock on data.csv
2. An IS lock on 2026/
3. An IS lock on reports/

When the download completes and the client closes the file, all three locks are released in a single operation.

What Locks Prevent

What Locks Allow

Lock Lifecycle

1. Acquired — when a file is opened for reading or writing. Intent locks are placed on all ancestor directories in the same database transaction.
2. Released — when the file stream is closed (download completes, upload commits, or client disconnects). All locks for the operation are released together.
3. Cleaned on startup — all lock rows are deleted when the server starts. If the server is starting, there are no active transfers — any existing lock is stale from a crash.

Upload Pipeline

Uploads use a pipelined producer-consumer architecture for maximum throughput:

1. The client begins writing data over SFTP/FTP.
2. The server buffers incoming bytes in memory (4 MB buffer).
3. When the buffer fills, the chunk is inserted into sftp_chunk_data (ref_count=1) and a mapping row is added to sftp_file_chunks. A fresh buffer is allocated immediately — the client continues without waiting.
4. A background drain thread handles the database INSERTs independently of the network I/O.
5. On close, the final chunk is flushed, the file size is updated, and the transaction is committed.

If the connection drops mid-upload, the transaction is rolled back. No orphan chunks remain.

Download — Random-Access Chunk Reads

1. The client requests a file for reading.
2. The server resolves the path to a file_id.
3. A READ lock + ancestor IS locks are acquired.
4. Chunks are fetched on demand via: SELECT cd.chunk_data FROM sftp_file_chunks fc JOIN sftp_chunk_data cd ON cd.chunk_id = fc.chunk_id WHERE fc.file_id=? AND fc.chunk_index=?
5. The current chunk is cached in memory. Sequential reads serve many requests from the same cached chunk.
6. On close, all locks are released.

Connection Pooling

Connections are managed by a dedicated HikariCP pool (StoragePool), separate from user-management and audit.

Comparison with Local Storage

DataDirect JDBC Support

Progress DataDirect drivers require a license unlock per connection. The server handles this automatically via reflection.

<storage-database>
    <url>jdbc:datadirect:sqlserver://dbhost:1433;DatabaseName=sftpfiles</url>
    <username>sftpserver</username>
    <password>secret</password>
    <license-key>XXXX-XXXX-XXXX-XXXX</license-key>
</storage-database>

Troubleshooting