schema.sql

-- Table to store raw Plaid transaction data
CREATE TABLE IF NOT EXISTS plaid_transactions (
    id SERIAL PRIMARY KEY,
    transaction_id TEXT UNIQUE NOT NULL,
    account_id TEXT NOT NULL,
    item_id TEXT NOT NULL,
    date DATE NOT NULL,
    name TEXT,
    amount NUMERIC(10, 2) NOT NULL, -- Negative for debits, positive for credits
    currency_code TEXT,
    category TEXT[], -- Array of categories (Plaid provides hierarchical categories)
    iso_currency_code TEXT,
    unofficial_currency_code TEXT,
    pending BOOLEAN,
    authorized_date DATE,
    payment_channel TEXT,
    -- Add other fields if needed, e.g., location, merchant_name, personal_finance_category
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Table to store LLM insights and recommendations
CREATE TABLE IF NOT EXISTS daily_recommendations (
    id SERIAL PRIMARY KEY,
    report_date DATE UNIQUE NOT NULL,
    summary TEXT,
    recommendation_1 TEXT,
    recommendation_2 TEXT,
    recommendation_3 TEXT,
    llm_raw_response TEXT, -- Store the full LLM response for debugging
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Table to store Plaid access tokens.
-- This is critical as you only generate an access token once per Plaid Item (linked account).
-- Your daily script will fetch this token to get new transactions.
CREATE TABLE IF NOT EXISTS plaid_items (
    id SERIAL PRIMARY KEY,
    item_id TEXT UNIQUE NOT NULL,
    access_token TEXT NOT NULL,
    institution_name TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);