Initial Commit

4 files changed, 152 insertions, 0 deletions

diff --git a/backend/db/migrations/000001_create_user_tables.down.sql b/backend/db/migrations/000001_create_user_tables.down.sql
new file mode 100644
index 0000000..f1ba1c0
--- /dev/null
+++ b/backend/db/migrations/000001_create_user_tables.down.sql
@@ -0,0 +1,5 @@
+DROP INDEX IF EXISTS idx_ibd_tokens_user_subject;
+
+DROP TABLE IF EXISTS ibd_tokens;
+DROP TABLE IF EXISTS users;
+DROP TABLE IF EXISTS keys;
diff --git a/backend/db/migrations/000001_create_user_tables.up.sql b/backend/db/migrations/000001_create_user_tables.up.sql
new file mode 100644
index 0000000..6681aa6
--- /dev/null
+++ b/backend/db/migrations/000001_create_user_tables.up.sql
@@ -0,0 +1,47 @@
+/**
+  * Keys Table
+  * The keys table stores the encryption keys used to encrypt user data.
+  * It holds encrypted AES keys and the KMS key used to encrypt them.
+ */
+CREATE TABLE IF NOT EXISTS keys
+(
+    id            SERIAL PRIMARY KEY,                             -- Unique ID for the key
+    kms_key_name  VARCHAR(255) NOT NULL,                          -- The name of the KMS key
+    encrypted_key BYTEA        NOT NULL,                          -- The encrypted AES key (encrypted with the KMS key)
+    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP -- The datetime the key was created
+);
+
+/**
+ * Users Table
+ * The users table stores the user data for the app.
+ */
+CREATE TABLE IF NOT EXISTS users
+(
+    subject        TEXT PRIMARY KEY,            -- The unique subject of the user, retrieved from Auth0
+    ibd_username   VARCHAR(255),                -- The IBD username of the user
+    ibd_password   BYTEA,                       -- The encrypted IBD password. Encrypted with `encryption_key`
+    encryption_key INTEGER REFERENCES keys (id) -- The encryption key used to encrypt the IBD password
+);
+
+/**
+ * IBD Tokens Table
+ * The IBD tokens table stores the tokens/cookies used to authenticate with the IBD website.
+ * These are scraped using chromedb and stored in the database for future use via APIs.
+ */
+CREATE TABLE IF NOT EXISTS ibd_tokens
+(
+    id             SERIAL PRIMARY KEY,                            -- Unique ID for the token
+    user_subject   TEXT      NOT NULL REFERENCES users (subject), -- The user in the users table associated with the token
+    token          BYTEA     NOT NULL,                            -- The encrypted token/cookie
+    encryption_key INTEGER   NOT NULL REFERENCES keys (id),       -- The encryption key used to encrypt the token
+    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- The datetime the token was created
+    expires_at     TIMESTAMP NOT NULL,                            -- The datetime the token expires
+    /**
+     * The `degraded` column is used to indicate that the token MAY be invalid.
+     * It is set to TRUE when a worker reports a failure using the token.
+     * This will cause the authentication cronjob to check the token and re-authenticate if necessary.
+     */
+    degraded       BOOLEAN   NOT NULL DEFAULT FALSE
+);
+
+CREATE INDEX IF NOT EXISTS idx_ibd_tokens_user_subject ON ibd_tokens (user_subject);
diff --git a/backend/db/migrations/000002_create_data_tables.down.sql b/backend/db/migrations/000002_create_data_tables.down.sql
new file mode 100644
index 0000000..640fe94
--- /dev/null
+++ b/backend/db/migrations/000002_create_data_tables.down.sql
@@ -0,0 +1,11 @@
+DROP INDEX IF EXISTS ratings_symbol_idx;
+
+DROP TABLE IF EXISTS stock_data;
+DROP TABLE IF EXISTS ratings;
+DROP TABLE IF EXISTS chart_analysis;
+DROP TABLE IF EXISTS stock_rank;
+DROP TABLE IF EXISTS stocks;
+
+DROP TYPE IF EXISTS CHART_ACTION;
+DROP TYPE IF EXISTS BAR_INTERVAL;
+DROP TYPE IF EXISTS RANK_TYPE;
\ No newline at end of file
diff --git a/backend/db/migrations/000002_create_data_tables.up.sql b/backend/db/migrations/000002_create_data_tables.up.sql
new file mode 100644
index 0000000..116c424
--- /dev/null
+++ b/backend/db/migrations/000002_create_data_tables.up.sql
@@ -0,0 +1,89 @@
+/**
+ * Stocks table
+ * The stocks table stores the basic information about each stock.
+ * This data is scraped periodically from the IBD website.
+ */
+CREATE TABLE IF NOT EXISTS stocks
+(
+    symbol  VARCHAR(16) PRIMARY KEY, -- The stock symbol/ticker
+    name    TEXT NOT NULL,           -- The full name of the stock
+    ibd_url TEXT                     -- The URL to the IBD page for the stock
+);
+
+/**
+ * Stock Rank table
+ * The stock rank table stores the rank information for each stock.
+ * This data is scraped periodically from the IBD website.
+ */
+CREATE TYPE RANK_TYPE AS ENUM ('ibd50', 'cap20');
+CREATE TABLE IF NOT EXISTS stock_rank
+(
+    symbol     VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
+    rank_type  RANK_TYPE   NOT NULL,                            -- The type of rank (IBD 50 or CAP 20)
+    rank       SMALLINT,                                        -- The rank of the stock
+    created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP   -- The datetime the rank was scraped/created
+);
+
+/**
+ * Chart Analysis table
+ * The chart analysis table stores the chart analysis for each stock.
+ * This data is scraped periodically from the IBD website then analyzed by OpenAI's GPT-4o.
+ */
+CREATE TYPE CHART_ACTION AS ENUM ('buy', 'sell', 'hold', 'unknown');
+CREATE TABLE IF NOT EXISTS chart_analysis
+(
+    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Randomly generated UUID for the chart analysis
+    raw_analysis TEXT    NOT NULL,                           -- The raw chart analysis text (e.g. "Flat base, 3 weeks tight")
+
+    processed    BOOLEAN NOT NULL DEFAULT FALSE,             -- Whether the chart analysis has been processed by GPT-4o
+    action       CHART_ACTION,                               -- The action to take based on the chart analysis
+    price        MONEY,                                      -- The price to take the action at
+    reason       TEXT,                                       -- The reason for the action
+    confidence   SMALLINT                                    -- The confidence level of the action (0-100)
+);
+
+/**
+ * Ratings table
+ * The ratings table stores the ratings for each stock.
+ * This data is scraped periodically from the IBD website.
+ */
+CREATE TABLE IF NOT EXISTS ratings
+(
+    id             UUID PRIMARY KEY     DEFAULT gen_random_uuid(),  -- Randomly generated UUID for the rating
+    symbol         VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
+
+    composite      SMALLINT,                                        -- The composite rating (0-99)
+    eps            SMALLINT,                                        -- The EPS rating (0-99)
+    rel_str        SMALLINT,                                        -- The relative strength rating (0-99)
+    group_rel_str  SMALLINT,                                        -- The group relative strength rating (E-A+) E=0, A+=13
+    smr            SMALLINT,                                        -- The SMR rating (E-A+)
+    acc_dis        SMALLINT,                                        -- The acc/dis rating (E-A+)
+    chart_analysis UUID REFERENCES chart_analysis (id),             -- The ID of the chart analysis for the stock
+    price          MONEY,                                           -- The price of the stock at the time of the rating
+
+    created_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP   -- The datetime the rating was scraped/created
+);
+
+CREATE INDEX IF NOT EXISTS ratings_symbol_idx ON ratings (symbol);
+
+/**
+ * Stock Data table
+ * The stock data table stores the historical price data for each stock.
+ * This data should be retrieved from a brokerage API or other data source.
+ * These should be in the format of minute and daily bars.
+ */
+CREATE TYPE BAR_INTERVAL AS ENUM ('1m', '1d');
+CREATE TABLE IF NOT EXISTS stock_data
+(
+    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),       -- Randomly generated UUID for the stock data
+    symbol       VARCHAR(16)  NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
+
+    bar_interval BAR_INTERVAL NOT NULL,                            -- The interval of the bar data (1m, 1d)
+    time         TIMESTAMP    NOT NULL,                            -- The timestamp of the bar
+
+    open         MONEY        NOT NULL,                            -- The opening price of the bar
+    high         MONEY        NOT NULL,                            -- The high price of the bar
+    low          MONEY        NOT NULL,                            -- The low price of the bar
+    close        MONEY        NOT NULL,                            -- The closing price of the bar
+    volume       BIGINT       NOT NULL                             -- The volume of the bar
+);