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

> Discover how the ACE-Step UI backend manages data storage using SQLite and local files. Learn about its swappable abstraction layers for flexible implementation.

- Repository: [fspecii/ace-step-ui](https://github.com/fspecii/ace-step-ui)
- Tags: architecture
- Published: 2026-04-29

---

**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)](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.

```typescript
// 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.

```typescript
// 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`](https://github.com/fspecii/ace-step-ui/blob/main/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.

```typescript
// 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)](https://github.com/fspecii/ace-step-ui/blob/main/server/src/services/storage/index.ts) isolates storage specifics from business logic.

```typescript
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)](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.

```typescript
// 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)](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.

```typescript
// 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)](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.

```typescript
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)](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.

```typescript
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)](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.

```typescript
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`](https://github.com/fspecii/ace-step-ui/blob/main/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`](https://github.com/fspecii/ace-step-ui/blob/main/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`](https://github.com/fspecii/ace-step-ui/blob/main/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`](https://github.com/fspecii/ace-step-ui/blob/main/users.ts) sanitize file extensions and keys before calling `storage.upload()`. The `sanitizeParams` function in [`pool.ts`](https://github.com/fspecii/ace-step-ui/blob/main/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`](https://github.com/fspecii/ace-step-ui/blob/main/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`](https://github.com/fspecii/ace-step-ui/blob/main/pool.ts), this configuration supports read-heavy workloads well, though high-write concurrent scenarios might require migrating to PostgreSQL using the existing abstraction layer.