blob: 6681aa64580385eccc893011eb23a0069d86adf7 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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);
|
