Database

Database

NexTask persists everything in a local SQLite database, accessed through Prisma with the better-sqlite3 adapter. This page covers the schema, the (custom) migration and seed systems, and database configuration.

Where the database lives

The database path depends on the environment (src/main/constants.ts):

Environment	File	Location
Development	dev.db	Project root (process.cwd())
Production (packaged)	app.db	Electron userData directory (OS-specific)

The Prisma client (src/main/server/prismaClient.ts) resolves this by checking for a dev.db in the current working directory first, and otherwise falling back to app.db in the user-data folder.

Schema

Defined in src/main/prisma/schema.prisma. It uses the modern prisma-client generator (output to ./generated/prisma) and the sqlite datasource.

Task — a Kanban card

Field	Type	Notes
id	Int	Primary key, auto-increment
version	String	Version tag (e.g. 1.5.0)
description	String	Free-text description
position	Int	Order within its column
title	String	Card title
isHistorized	Boolean	false by default; true once archived
historizationDate	DateTime?	When it was archived (nullable)
stageId	Int?	FK → Stage.id (nullable)
stage	Stage?	Relation

Stage — a Kanban column

Field	Type	Notes
id	Int	Primary key, auto-increment
name	String	Column name
position	Int	Order left-to-right
tasks	Task[]	Relation

Seed — seed bookkeeping

Mapped to the table _seeds. Tracks which seed scripts have been executed so they only run once.

Field	Type	Notes
id	Int	Primary key
name	String	Unique — the seed file name
executed	Boolean	Default false
createdAt	DateTime	Default now()

Relationship & archiving semantics

• A task belongs to at most one stage. stageId is nullable.
• The foreign key is declared ON DELETE SET NULL (see the migration SQL): deleting a stage detaches its tasks rather than deleting them.
• Archiving a task (the "trash" button / PUT /tasks/:id) sets isHistorized = true, stamps historizationDate, and sets stageId = null — so it disappears from the board but is retained in the database.
• When the app deletes a stage, the store first archives all of that stage's active tasks, then deletes the stage (see Frontend).

Migrations

⚠️ NexTask uses a hand-rolled migration runner, not prisma migrate deploy, so that packaged builds can apply schema changes without shipping the Prisma CLI.

In development

setupDatabase() is a no-op in dev. You manage the schema yourself with the Prisma CLI:

npx prisma migrate dev            # create + apply a new migration
npx prisma migrate dev --create-only  # create without applying
npx prisma studio                 # visual DB browser
npx prisma migrate reset          # ⚠ wipes all data

In production (packaged app)

At startup, setupDatabase() (src/main/setupDatabase.ts):

1. Creates the SQLite file if it doesn't exist.
2. Ensures a _prisma_migrations bookkeeping table exists (migration_name, applied_at).
3. Reads every folder under prisma/migrations/.
4. For each folder not already recorded, executes its migration.sql and records the folder name.

Migrations are tracked by folder name, and applied in the order returned by the filesystem. Each migration is a standard folder prisma/migrations/<timestamp>_<name>/migration.sql.

Current migrations:

Migration	Purpose
20260330201803_init	Creates the Task and Stage tables
20260404131528_add_seed	Adds the _seeds bookkeeping table

Seeds

Seeds run on every startup, in both dev and prod, and are idempotent — applySeeds() (src/main/seedDatabase.ts):

1. Ensures the _seeds table exists.
2. Lists *.sql files in prisma/seeds/, sorted by name.
3. Skips any already recorded as executed; runs the rest and records them.

Note: because seeding is automatic at startup, there is no separate seed command to run.

Default data

prisma/seeds/01_initial_stages.sql inserts the four default columns on a fresh database:

INSERT INTO Stage (name, position) VALUES ('A faire', 1);
INSERT INTO Stage (name, position) VALUES ('En cours', 2);
INSERT INTO Stage (name, position) VALUES ('En attente', 3);
INSERT INTO Stage (name, position) VALUES ('Terminé', 4);

Performance PRAGMAs

Before serving the first request, the server applies SQLite tuning pragmas (src/main/server/prismaClient.ts → applyDatabasePragmas()):

PRAGMA journal_mode = WAL;      -- Write-Ahead Logging for better concurrency
PRAGMA synchronous  = NORMAL;   -- Faster writes, still crash-safe under WAL
PRAGMA busy_timeout = 5000;     -- Wait up to 5s on a locked DB instead of erroring

Prisma configuration

prisma.config.ts tells the Prisma CLI where the schema lives and which datasource to use in development:

export default defineConfig({
  schema: 'src/main/prisma/schema.prisma',
  datasource: { url: `file:${path.join(process.cwd(), 'dev.db')}` },
})

The generated client is emitted to src/main/prisma/generated/ and is git-ignored — regenerate it with npx prisma generate if needed.

Related pages

• Consume the data over HTTP: REST API
• How the UI caches and mutates it: Frontend
• Command reference: Useful Commands