20251104-0-init-schema.sql: Add initial schema creation SQL script

1 files changed, 53 insertions, 0 deletions

diff --git a/src/main/resources/db/20251114-0-init-schema.sql b/src/main/resources/db/20251114-0-init-schema.sql
new file mode 100644
index 0000000..b9b921f
--- /dev/null
+++ b/src/main/resources/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
+);
+
+--TODO construct views on event collation? heavy calculation requirements....
\ No newline at end of file