How the ACE-Step UI Backend Manages Data Storage: SQLite and Local File Architecture

The ACE-Step UI backend uses SQLite for relational data and the local filesystem for binary assets, wrapped behind abstraction layers that make the storage implementation swappable without modifying route handlers.

The ACE-Step UI backend, found in the fspecii/ace-step-ui repository, implements a dual-layer data storage strategy that separates structured relational records from binary media files. This architecture stores users, songs, playlists, and metadata in a transactional SQLite database while persisting audio files and images to the local filesystem under public/audio. A factory pattern and clean interfaces ensure the rest of the application remains agnostic to these underlying implementations.

Relational Data Storage with SQLite

All structured data—including users, songs, playlists, comments, and follows—lives in a file-based SQLite database configured via config.database.path. The system accesses this through a custom pool wrapper defined in [server/src/db/pool.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/db/pool.ts) that provides PostgreSQL-compatible query semantics while targeting SQLite.

Database Initialization and Configuration

The pool initializes a single better-sqlite3 instance with Write-Ahead Logging (WAL) enabled for concurrent read performance, foreign key constraints enforced, and a busy timeout to handle lock contention.

// From server/src/db/pool.ts (lines 19-22)
const dbInstance = new Database(config.database.path);
dbInstance.pragma('journal_mode = WAL');
dbInstance.pragma('foreign_keys = ON');
dbInstance.pragma('busy_timeout = 5000');

Query Parameter Sanitization

Before any SQL execution, parameters pass through sanitizeParams to ensure type compatibility with SQLite’s strict typing. This function converts undefined to null, booleans to integers (0/1), and serializes objects or arrays to JSON strings.

// From server/src/db/pool.ts (lines 26-39)
function sanitizeParams(params: any[]): any[] {
  return params.map(param => {
    if (param === undefined) return null;
    if (typeof param === 'boolean') return param ? 1 : 0;
    if (typeof param === 'object') return JSON.stringify(param);
    return param;
  });
}

SQL Compatibility Layer

To support PostgreSQL-style query syntax, the executeQuery method in pool.ts rewrites placeholders ($1, $2) to SQLite ? syntax, translates ILIKE to LIKE, and handles CURRENT_TIMESTAMP conversions. It also auto-generates UUIDs for tables requiring an id column, bridging the gap between SQLite’s native features and PostgreSQL conventions.

Transaction Support

The connect() method returns a lightweight client that recognizes BEGIN, COMMIT, and ROLLBACK commands, mapping them directly to SQLite transactions. This allows route handlers to execute atomic operations across multiple tables.

// Transaction example usage
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO songs ...');
  await client.query('INSERT INTO playlists ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
}

Binary Asset Storage with Local Files

Binary assets—including audio files, avatars, and banner images—are stored on the local filesystem under public/audio. This implementation is abstracted behind a common StorageProvider interface, making it trivial to swap in S3, Cloudflare R2, or other cloud storage later.

The StorageProvider Interface

The contract defined in [server/src/services/storage/index.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/services/storage/index.ts) isolates storage specifics from business logic.

export interface StorageProvider {
  upload(key: string, data: Buffer, contentType: string): Promise<string>;
  getUrl(key: string, expiresIn?: number): Promise<string>;
  getPublicUrl(key: string): string;
  delete(key: string): Promise<void>;
  exists(key: string): Promise<boolean>;
  copy(sourceKey: string, destKey: string): Promise<void>;
}

LocalStorageProvider Implementation

[server/src/services/storage/local.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/services/storage/local.ts) implements this interface for the local filesystem. The upload method creates directories recursively and writes buffers to disk, while URL generation returns paths like /audio/<key> that Express static middleware serves directly.

// Upload implementation (local.ts lines 17-22)
async upload(key: string, data: Buffer, contentType: string): Promise<string> {
  const fullPath = path.join(this.basePath, key);
  await fs.mkdir(path.dirname(fullPath), { recursive: true });
  await fs.writeFile(fullPath, data);
  return key;
}

// Public URL generation (local.ts lines 24-32)
getPublicUrl(key: string): string {
  return `/audio/${key.replace(/\\/g, '/')}`;
}

Deletion, existence checks, and copy operations are thin wrappers around Node.js fs/promises functions.

Factory Pattern for Storage Abstraction

The getStorageProvider() function in [server/src/services/storage/factory.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/services/storage/factory.ts) lazily instantiates a singleton LocalStorageProvider. While ACE-Step UI currently defaults to local storage exclusively, this factory pattern means changing providers only requires modifying the factory return value, leaving all route handlers untouched.

// From factory.ts (lines 11-14)
export function getStorageProvider(): StorageProvider {
  // Currently hardcoded to local; easily swapped for S3/R2
  return new LocalStorageProvider();
}

How Routes Integrate the Storage Layer

Route modules interact with storage exclusively through the pool.query method for data and getStorageProvider() for files, maintaining clean separation of concerns.

Uploading User Avatars and Banners

In [server/src/routes/users.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/routes/users.ts) (lines 76-82), avatar uploads use the storage provider to persist files and return URLs for database storage.

const storage = getStorageProvider();
const key = `users/${req.user!.id}/avatar${path.extname(req.file.originalname)}`;
await storage.upload(key, req.file.buffer, req.file.mimetype);
const url = storage.getPublicUrl(key); // Returns /audio/users/42/avatar.png
// Store 'url' in the users table via pool.query

Resolving Audio URLs for Playback

The songs route ([server/src/routes/songs.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/routes/songs.ts), lines 10-31) handles both legacy S3-style URLs (s3://) and local paths. For S3 references, it requests signed URLs; for local files, it redirects to the static path.

async function resolveAccessibleAudioUrl(audioUrl: string, isPublic: boolean) {
  if (!audioUrl) return null;
  if (audioUrl.startsWith('s3://')) {
    const key = audioUrl.replace('s3://', '');
    const storage = getStorageProvider();
    return isPublic 
      ? storage.getPublicUrl(key) 
      : storage.getUrl(key, 3600); // Signed URL for private access
  }
  return audioUrl; // Already a local /audio/ path
}

Cleaning Up Files on Deletion

When deleting songs, the route removes associated binary assets to prevent orphaned files. In [server/src/routes/songs.ts](https://github.com/fspecii/ace-step-ui/blob/main/server/src/routes/songs.ts) (lines 14-25), it extracts the storage key from the database URL and invokes the provider’s delete method.

const storage = getStorageProvider();
if (song.audio_url) {
  const storageKey = song.audio_url.startsWith('/audio/')
    ? song.audio_url.replace('/audio/', '')
    : song.audio_url.replace('s3://', '');
  await storage.delete(storageKey);
}

Summary

• SQLite database: Stores all relational entities (users, songs, playlists, comments) with WAL mode enabled for performance. Accessed through the pool wrapper in server/src/db/pool.ts which provides parameter sanitization and transaction support.
• Local filesystem: Houses binary assets under public/audio, implemented via LocalStorageProvider in server/src/services/storage/local.ts.
• Abstraction layers: The StorageProvider interface and getStorageProvider() factory decouple business logic from storage implementation, enabling future cloud migration without code changes.
• Route integration: All database queries use pool.query() while file operations use the storage provider singleton, ensuring consistent error handling and resource management.

Frequently Asked Questions

Can I migrate the ACE-Step UI backend from SQLite to PostgreSQL?

Yes. The pool abstraction in server/src/db/pool.ts already handles PostgreSQL-style placeholder rewriting ($1 to ?) and syntax translation. To migrate, you would replace the better-sqlite3 instantiation with a PostgreSQL client connection and remove the compatibility transformations, keeping the same pool.query interface that routes depend on.

How does the backend handle file upload security?

The storage layer itself does not validate content, but routes like users.ts sanitize file extensions and keys before calling storage.upload(). The sanitizeParams function in pool.ts ensures metadata stored in SQLite is properly escaped and typed, preventing SQL injection while the filesystem operations use Node.js safe path joining to avoid directory traversal attacks.

What happens to audio files when a song is deleted?

The deletion handler in server/src/routes/songs.ts explicitly calls storage.delete() on the audio file and optional cover image after removing the database record. This prevents orphaned files in public/audio, though the implementation currently does not run garbage collection for files uploaded but never associated with records (e.g., failed uploads).

Is the SQLite database suitable for production workloads?

The ACE-Step UI backend configures SQLite with WAL mode, foreign keys, and busy timeouts to handle moderate concurrency. According to the source in pool.ts, this configuration supports read-heavy workloads well, though high-write concurrent scenarios might require migrating to PostgreSQL using the existing abstraction layer.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}