Plotwise Writer Design


01 Vision

Product Vision

Problem Statement

Goals & Non‑Goals

Success Metrics

Stakeholders & Personas

02 Requirements

Requirements

Functional Requirements

Non‑Functional

Constraints & Assumptions

03 Solution Overview

Solution Overview

System‑context Diagram

Key User Journeys

High‑level Risks

04 Architecture

Architecture

Container Diagram

Tech Stack & Rationale

Cross‑cutting Concerns

Open Questions

05 Components

06 Data Model

Data Model

Plotwise Writer Projects are self-contained SQLite3 databases.

ER Diagram / Schemas

Here's the schema definition which is stored as a resource in the Plotwise Writer executable.

CREATE TABLE metadata
(
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    "key" TEXT,
    value TEXT
);

CREATE TABLE folders
(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id  INTEGER REFERENCES folders (id) ON DELETE CASCADE,
    name       TEXT                NOT NULL,
    sort_index INTEGER DEFAULT 0,
    flags      INTEGER DEFAULT (0) NOT NULL
);

CREATE TABLE files
(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    folder_id  INTEGER,
    name       TEXT NOT NULL,
    sort_index INTEGER  DEFAULT (0),
    updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT FK_files_folders FOREIGN KEY (folder_id) REFERENCES folders (id) ON DELETE CASCADE
);

CREATE TABLE content
(
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id      INTEGER NOT NULL,
    content_md   TEXT    NOT NULL,
    updated_at   DATETIME DEFAULT (CURRENT_TIMESTAMP),
    is_autosave  INTEGER NOT NULL,
    content_hash TEXT    NOT NULL,
    CONSTRAINT FK_autosave_files FOREIGN KEY (file_id) REFERENCES files (id) ON DELETE CASCADE
);

CREATE TABLE tags
(
    id    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    label TEXT    NOT NULL,
    color TEXT    NOT NULL
);

CREATE TABLE _version
(
    id      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    version TEXT    NOT NULL,
    date    DATETIME DEFAULT (CURRENT_TIMESTAMP)
)

Migrations

Migrations are run every time a project is opened or created. This should bring the project schema up to date automatically. Each migration is logged in the _version table so that each one is only run once.

Version Mismatches

It's possible that someone could open a project file using an older version of the software. If the database version is higher than the current software version, a warning should be displayed and the file opened in read only mode. An effort should be made to display the project data, but there should be no guarantees.

07 User Interface

Main Window

Group.png