From 51ff59228b2bbc3a92d9b3e867abb1144059034d Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Fri, 23 Aug 2019 00:50:15 -0400 Subject: add helpers for loading data into the db --- data/schema.sql | 56 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) create mode 100644 data/schema.sql (limited to 'data') 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) +); -- cgit v1.2.3-54-g00ecf