From 615d44041d35ee1a08ddc66423f67a9d48f53ae9 Mon Sep 17 00:00:00 2001 From: Kevin Hoerr Date: Thu, 16 Apr 2026 11:37:21 -0400 Subject: Switch to distrobox as base for development --- src/db/20251114-0-init-schema.sql | 53 +++++++++++++++++++++++++++++++ src/db/20251117-0-summary-view.sql | 65 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 118 insertions(+) create mode 100644 src/db/20251114-0-init-schema.sql create mode 100644 src/db/20251117-0-summary-view.sql (limited to 'src/db') diff --git a/src/db/20251114-0-init-schema.sql b/src/db/20251114-0-init-schema.sql new file mode 100644 index 0000000..886a961 --- /dev/null +++ b/src/db/20251114-0-init-schema.sql @@ -0,0 +1,53 @@ +-- Equity tracker schema +-- SQLite does not control types so directly; but it makes it more portable to a different SQL server with specificity + +-- enum as table - there may be other equity types, does not affect views directly +create table equity_type +( + equity_type_name VARCHAR(20) PRIMARY KEY NOT NULL +); + +INSERT INTO equity_type (equity_type_name) VALUES + ('STOCK'), + ('ETF'), + ('CRYPTO'); + +-- deletion of equity_type does not cascade - equity_type is required for equity_symbol, which is required for equity_change_event, which is required for fiduciary_account. +-- equity_symbol, equity_type would never be deleted, but they could be cleaned up if they are orphaned records. +create table equity_symbol +( + equity_symbol_id SMALLINT PRIMARY KEY NOT NULL, + equity_symbol_name VARCHAR(16) NOT NULL, + equity_symbol_type VARCHAR(20) NOT NULL + REFERENCES equity_type(equity_type_name), + equity_symbol_managing_company VARCHAR(96) NOT NULL, + equity_symbol_created_timestamp TIMESTAMP NOT NULL +); + +create table fiduciary_account +( + fiduciary_account_id TINYINT PRIMARY KEY NOT NULL, + fiduciary_account_number UNSIGNED BIG INT NOT NULL, + fiduciary_account_name NVARCHAR(96) NOT NULL, + fiduciary_account_description NVARCHAR(512), + fiduciary_account_created_timestamp TIMESTAMP NOT NULL +); + +-- deletion of fiduciary_account row cascades - an account precludes change events. +-- deletion of equity_symbol row does not cascade - all events should be recorded for an account, and all events must have symbols +create table equity_change_event +( + equity_change_event_id UNSIGNED BIG INT PRIMARY KEY NOT NULL, + fiduciary_account_id TINYINT NOT NULL + REFERENCES fiduciary_account(fiduciary_account_id) + ON DELETE CASCADE, + equity_symbol_id SMALLINT NOT NULL + REFERENCES equity_symbol(equity_symbol_id), + equity_change_quantity DECIMAL(20, 8) NOT NULL, + equity_change_cost_basis_usd DECIMAL(14, 2) NOT NULL, + equity_change_type VARCHAR(20) NOT NULL + CHECK (equity_change_type IN ('BUY', 'SELL')), -- This is essentially '+' or '-' + equity_change_timestamp TIMESTAMP NOT NULL, + equity_change_type_score NUMERIC AS (CASE WHEN equity_change_type = 'BUY' THEN 1.0 ELSE -1.0 END) STORED, + equity_change_est_price DECIMAL(14, 2) AS (equity_change_cost_basis_usd / equity_change_quantity) STORED +); diff --git a/src/db/20251117-0-summary-view.sql b/src/db/20251117-0-summary-view.sql new file mode 100644 index 0000000..a4e8004 --- /dev/null +++ b/src/db/20251117-0-summary-view.sql @@ -0,0 +1,65 @@ +-- Yucky slow view, since it's sub-querying for each of the summation fields +-- AND has a subquery on the inner join to find the right symbols. Anyways it +-- should preview relative strength of treating equity transaction data as +-- immutable. Any application on top of this should get the full list of +-- records so it can construct helpful views of summations based on the history +-- of changes, and/or show price data over time +CREATE VIEW account_symbol_summary_view +AS +SELECT + acc.fiduciary_account_id, + acc.fiduciary_account_name, + acc.fiduciary_account_description, + acc.fiduciary_account_number, + acc.fiduciary_account_created_timestamp, + sym.equity_symbol_id, + sym.equity_symbol_type, + sym.equity_symbol_name, + sym.equity_symbol_managing_company, + (SELECT + SUM(ev.equity_change_quantity * ev.equity_change_type_score) + FROM equity_change_event ev + WHERE ev.fiduciary_account_id = acc.fiduciary_account_id + AND ev.equity_symbol_id = sym.equity_symbol_id + ) AS account_symbol_quantity, + (SELECT + printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score) / SUM(ev.equity_change_quantity * ev.equity_change_type_score)) + FROM equity_change_event ev + WHERE ev.fiduciary_account_id = acc.fiduciary_account_id + AND ev.equity_symbol_id = sym.equity_symbol_id + ) AS account_symbol_cost_basis_price, + (SELECT + printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score)) + FROM equity_change_event ev + WHERE ev.fiduciary_account_id = acc.fiduciary_account_id + AND ev.equity_symbol_id = sym.equity_symbol_id + ) AS account_symbol_cost_basis_total_usd, + (SELECT + printf( '%.2f', SUM(ev.equity_change_quantity) * ( + SELECT + iev.equity_change_est_price + FROM equity_change_event iev + WHERE iev.fiduciary_account_id = acc.fiduciary_account_id + AND iev.equity_symbol_id = sym.equity_symbol_id + ORDER BY iev.equity_change_timestamp DESC + LIMIT 1 + )) + FROM equity_change_event ev + WHERE ev.fiduciary_account_id = acc.fiduciary_account_id + AND ev.equity_symbol_id = sym.equity_symbol_id + ) AS account_symbol_last_effective_price, + (SELECT + ev.equity_change_timestamp + FROM equity_change_event ev + WHERE ev.fiduciary_account_id = acc.fiduciary_account_id + AND ev.equity_symbol_id = sym.equity_symbol_id + ORDER BY ev.equity_change_timestamp DESC + LIMIT 1 + ) AS account_symbol_last_update_timestamp +FROM fiduciary_account acc +INNER JOIN equity_symbol sym ON EXISTS( + SELECT * + FROM equity_change_event e + WHERE e.fiduciary_account_id = acc.fiduciary_account_id + AND e.equity_symbol_id = sym.equity_symbol_id +); \ No newline at end of file -- cgit v1.3