diff options
author | Jesse Luehrs <doy@tozt.net> | 2019-08-23 00:50:15 -0400 |
---|---|---|
committer | Jesse Luehrs <doy@tozt.net> | 2019-08-23 00:50:15 -0400 |
commit | 51ff59228b2bbc3a92d9b3e867abb1144059034d (patch) | |
tree | f4374878df6a04ce83883539467a626700641a5b /data | |
parent | c0a0be3ecf2ddf0842173cb68002738b033895ff (diff) | |
download | ynab-export-51ff59228b2bbc3a92d9b3e867abb1144059034d.tar.gz ynab-export-51ff59228b2bbc3a92d9b3e867abb1144059034d.zip |
add helpers for loading data into the db
Diffstat (limited to 'data')
-rw-r--r-- | data/schema.sql | 56 |
1 files changed, 56 insertions, 0 deletions
diff --git a/data/schema.sql b/data/schema.sql new file mode 100644 index 0000000..fe690be --- /dev/null +++ b/data/schema.sql @@ -0,0 +1,56 @@ +DROP TABLE IF EXISTS transactions; +DROP TABLE IF EXISTS payees; +DROP TABLE IF EXISTS categories; +DROP TABLE IF EXISTS category_groups; +DROP TABLE IF EXISTS accounts; +DROP TYPE IF EXISTS cleared_t; +DROP TYPE IF EXISTS flag_color_t; + +CREATE TABLE accounts ( + id text PRIMARY KEY, + name text NOT NULL, + on_budget boolean NOT NULL, + closed boolean NOT NULL, + balance bigint NOT NULL, + cleared_balance bigint NOT NULL, + uncleared_balance bigint NOT NULL +); + +CREATE TABLE category_groups ( + id text PRIMARY KEY, + name text NOT NULL, + hidden boolean NOT NULL +); + +CREATE TABLE categories ( + id text PRIMARY KEY, + category_group_id text REFERENCES category_groups(id) NOT NULL, + name text NOT NULL, + hidden boolean NOT NULL, + budgeted bigint NOT NULL, + activity bigint NOT NULL, + balance bigint NOT NULL +); + +CREATE TABLE payees ( + id text PRIMARY KEY, + name text NOT NULL, + transfer_account_id text REFERENCES accounts(id) +); + +CREATE TYPE cleared_t AS ENUM ('reconciled', 'cleared', 'uncleared'); +CREATE TYPE flag_color_t AS ENUM('red', 'orange', 'yellow', 'green', 'blue', 'purple'); + +CREATE TABLE transactions ( + id text PRIMARY KEY, + transaction_date date NOT NULL, + amount bigint NOT NULL, + memo text, + cleared cleared_t NOT NULL, + approved boolean NOT NULL, + flag_color flag_color_t, + account_id text REFERENCES accounts(id) NOT NULL, + payee_id text REFERENCES payees(id), + category_id text REFERENCES categories(id), + transfer_account_id text REFERENCES accounts(id) +); |