finnow/finnow-api/schema.sql

-- This schema is included at compile-time into data : SqliteDataSource.

-- Basic/Utility Entities

CREATE TABLE profile_property (
    property TEXT PRIMARY KEY,
    value TEXT DEFAULT NULL
);

CREATE TABLE attachment (
    id INTEGER PRIMARY KEY,
    uploaded_at TEXT NOT NULL,
    filename TEXT NOT NULL,
    content_type TEXT NOT NULL,
    size INTEGER NOT NULL,
    content BLOB NOT NULL
);

-- Account Entities

CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL,
    archived BOOLEAN NOT NULL DEFAULT FALSE,
    type TEXT NOT NULL,
    number_suffix TEXT,
    name TEXT NOT NULL,
    currency TEXT NOT NULL,
    description TEXT
);

CREATE TABLE account_credit_card_properties (
    account_id INTEGER PRIMARY KEY,
    credit_limit INTEGER,
    CONSTRAINT fk_account_credit_card_properties_account
        FOREIGN KEY (account_id) REFERENCES account(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

-- Transaction Entities

CREATE TABLE transaction_vendor (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE transaction_category (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    color TEXT NOT NULL DEFAULT 'FFFFFF',
    CONSTRAINT fk_transaction_category_parent
        FOREIGN KEY (parent_id) REFERENCES transaction_category(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE transaction_tag (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE "transaction" (
    id INTEGER PRIMARY KEY,
    timestamp TEXT NOT NULL,
    added_at TEXT NOT NULL,
    amount INTEGER NOT NULL,
    currency TEXT NOT NULL,
    description TEXT,
    vendor_id INTEGER,
    category_id INTEGER,
    CONSTRAINT fk_transaction_vendor
        FOREIGN KEY (vendor_id) REFERENCES transaction_vendor(id)
            ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_transaction_category
        FOREIGN KEY (category_id) REFERENCES transaction_category(id)
            ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE TABLE transaction_attachment (
    transaction_id INTEGER NOT NULL,
    attachment_id INTEGER NOT NULL,
    PRIMARY KEY (transaction_id, attachment_id),
    CONSTRAINT fk_transaction_attachment_transaction
        FOREIGN KEY (transaction_id) REFERENCES "transaction"(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_transaction_attachment_attachment
        FOREIGN KEY (attachment_id) REFERENCES attachment(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE transaction_tag_join (
    transaction_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (transaction_id, tag_id),
    CONSTRAINT fk_transaction_tag_join_transaction
        FOREIGN KEY (transaction_id) REFERENCES "transaction"(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_transaction_tag_join_tag
        FOREIGN KEY (tag_id) REFERENCES transaction_tag(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE transaction_line_item (
    id INTEGER PRIMARY KEY,
    transaction_id INTEGER NOT NULL,
    value_per_item INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    idx INTEGER NOT NULL DEFAULT 0,
    description TEXT NOT NULL,
    category_id INTEGER,
    CONSTRAINT fk_transaction_line_item_transaction
        FOREIGN KEY (transaction_id) REFERENCES "transaction"(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_transaction_line_item_category
        FOREIGN KEY (category_id) REFERENCES transaction_category(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE account_journal_entry (
    id INTEGER PRIMARY KEY,
    timestamp TEXT NOT NULL,
    account_id INTEGER NOT NULL,
    transaction_id INTEGER NOT NULL,
    amount INTEGER NOT NULL,
    type TEXT NOT NULL,
    currency TEXT NOT NULL,
    CONSTRAINT fk_account_journal_entry_account
        FOREIGN KEY (account_id) REFERENCES account(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_account_journal_entry_transaction
        FOREIGN KEY (transaction_id) REFERENCES "transaction"(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

-- Value records

CREATE TABLE account_value_record (
    id INTEGER PRIMARY KEY,
    timestamp TEXT NOT NULL,
    account_id INTEGER NOT NULL,
    type TEXT NOT NULL DEFAULT 'BALANCE',
    value INTEGER NOT NULL,
    currency TEXT NOT NULL,
    CONSTRAINT fk_account_value_record_account
        FOREIGN KEY (account_id) REFERENCES account(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE account_value_record_attachment (
    value_record_id BIGINT NOT NULL,
    attachment_id BIGINT NOT NULL,
    PRIMARY KEY (value_record_id, attachment_id),
    CONSTRAINT fk_account_value_record_attachment_value_record
        FOREIGN KEY (value_record_id) REFERENCES account_value_record(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_account_value_record_attachment_attachment
        FOREIGN KEY (attachment_id) REFERENCES attachment(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

-- History Entities

CREATE TABLE history (
    id INTEGER PRIMARY KEY
);

CREATE TABLE history_item (
    id INTEGER PRIMARY KEY,
    history_id INTEGER NOT NULL,
    timestamp TEXT NOT NULL,
    type TEXT NOT NULL,
    CONSTRAINT fk_history_item_history
        FOREIGN KEY (history_id) REFERENCES history(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE history_item_text (
    item_id INTEGER PRIMARY KEY,
    content TEXT NOT NULL,
    CONSTRAINT fk_history_item_text_item
        FOREIGN KEY (item_id) REFERENCES history_item(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE account_history (
    account_id INTEGER NOT NULL UNIQUE,
    history_id INTEGER NOT NULL,
    PRIMARY KEY (account_id, history_id),
    CONSTRAINT fk_account_history_account
        FOREIGN KEY (account_id) REFERENCES account(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_account_history_history
        FOREIGN KEY (history_id) REFERENCES history(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE transaction_history (
    transaction_id INTEGER NOT NULL UNIQUE,
    history_id INTEGER NOT NULL,
    PRIMARY KEY (transaction_id, history_id),
    CONSTRAINT fk_history_transaction_transaction
        FOREIGN KEY (transaction_id) REFERENCES "transaction"(id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_history_transaction_history
        FOREIGN KEY (history_id) REFERENCES history(id)
            ON UPDATE CASCADE ON DELETE CASCADE
);