financial_entities.sql

=============== CNUCASH ===================
CREATE TABLE TRANSACTION (
  ID VARCHAR(256) NOT NULL,
  NUM VARCHAR(256),
  DESCRIPTION VARCHAR(512),
  DATE CHAR(10)
)

CREATE TABLE SPLIT (
  ID VARCHAR(256) NOT NULL,
  TRANSACTION_ID VARCHAR(256),
  VALUE DOUBLE,
  QUANTITY DOUBLE,
  ACCOUNT VARCHAR(256)
)

CREATE TABLE ACCOUNT (
  NAME VARCHAR(256),
  ID VARCHAR(256),
  TYPE VARCHAR(256),
  DESCRIPTION VARCHAR(256),
  PARENT VARCHAR(256)
)

=================== MINE =======================

-- Accounts table to store account information
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50) CHECK (account_type IN ('shared', 'single')) NOT NULL,
    owner_id INT NOT NULL,  -- Assuming there is a users table with user_id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Balances table to store balance information for each account
CREATE TABLE balances (
    balance_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    balance DECIMAL(15, 2) NOT NULL,
    balance_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transactions table to store transaction details
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    amount DECIMAL(15, 2) NOT NULL,
    transaction_date TIMESTAMP NOT NULL,
    description VARCHAR(255),
    tag_id INT REFERENCES tags(tag_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    goal_id INT REFERENCES goals(goal_id)
);

-- Loans table to store loan information
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    loan_amount DECIMAL(15, 2) NOT NULL,
    interest_rate DECIMAL(5, 2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    status VARCHAR(50) CHECK (status IN ('active', 'closed')) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Goals table to store financial goals
CREATE TABLE goals (
    goal_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    goal_name VARCHAR(255) NOT NULL,
    target_amount DECIMAL(15, 2) NOT NULL,
    current_amount DECIMAL(15, 2) DEFAULT 0,
    due_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tags table to store tags with icons and colors
CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL,
    icon VARCHAR(255),
    color VARCHAR(7),  -- Assuming color is stored as a hex code
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for accounts table
CREATE INDEX idx_accounts_owner_id ON accounts(owner_id);

-- Indexes for balances table
CREATE INDEX idx_balances_account_id ON balances(account_id);
CREATE INDEX idx_balances_balance_date ON balances(balance_date);

-- Indexes for transactions table
CREATE INDEX idx_transactions_account_id ON transactions(account_id);
CREATE INDEX idx_transactions_transaction_date ON transactions(transaction_date);
CREATE INDEX idx_transactions_tag_id ON transactions(tag_id);
CREATE INDEX idx_transactions_goal_id ON transactions(goal_id);

-- Indexes for loans table
CREATE INDEX idx_loans_account_id ON loans(account_id);
CREATE INDEX idx_loans_status ON loans(status);
CREATE INDEX idx_loans_start_date ON loans(start_date);
CREATE INDEX idx_loans_end_date ON loans(end_date);

-- Indexes for goals table
CREATE INDEX idx_goals_account_id ON goals(account_id);