db/migrations.go

   1 package db
   2
   3 type Migration struct {
   4         Version int64
   5         Up      []string
   6         Down    []string
   7 }
   8
   9 var migrations []Migration = []Migration{
  10         {
  11                 Version: 1,
  12                 Up: []string{
  13                         `CREATE TABLE users (
  14                                 id            BIGSERIAL PRIMARY KEY,
  15                                 email         text NOT NULL,
  16                                 password_hash text NOT NULL,
  17                                 otp_secret    text,
  18                                 is_otp_setup  boolean,
  19                                 status        smallint,
  20                                 UNIQUE(email)
  21                         )`,
  22                         `CREATE TABLE market_configs (
  23                                 id          BIGSERIAL PRIMARY KEY,
  24                                 market_name text NOT NULL,
  25                                 user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  26                                 config      jsonb
  27                         )`,
  28                         `CREATE UNIQUE INDEX IF NOT EXISTS market_name_user_id_idx ON public.market_configs (user_id, market_name)`,
  29                         `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`,
  30                         `CREATE TABLE reports (
  31                                 id               BIGSERIAL PRIMARY KEY,
  32                                 date             timestamp with time zone NOT NULL,
  33                                 market_config_id bigint NOT NULL REFERENCES market_configs(id),
  34                                 debug            boolean
  35                         )`,
  36                         "CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)",
  37                         `CREATE TABLE report_lines (
  38                                 id        BIGSERIAL PRIMARY KEY,
  39                                 date      timestamp with time zone NOT NULL,
  40                                 report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
  41                                 type      text,
  42                                 payload   jsonb
  43                         )`,
  44                         "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)",
  45                         "CREATE INDEX IF NOT EXISTS report_lines_type      ON report_lines (type)",
  46                         `CREATE VIEW view_report_lines_by_user AS
  47                             SELECT report_lines.id,
  48                                 reports.id AS report_id,
  49                                 market_configs.market_name,
  50                                 users.id AS user_id,
  51                                 users.email AS user_email,
  52                                 reports.date AS report_date,
  53                                 report_lines.date,
  54                                 report_lines.payload
  55                               FROM public.report_lines
  56                                 JOIN public.reports ON reports.id = report_lines.report_id
  57                                 JOIN public.market_configs ON reports.market_config_id = market_configs.id
  58                                 JOIN public.users ON market_configs.user_id = users.id`,
  59                 },
  60                 Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"},
  61         },
  62         {
  63                 Version: 201805061000,
  64                 Up: []string{
  65                         `CREATE VIEW "view_balances" AS
  66                                             SELECT report_lines.id,
  67                                                 reports.id AS report_id,
  68                                                 market_configs.market_name,
  69                                                 market_configs.id AS market_id,
  70                                                 reports.date AS report_date,
  71                                                 report_lines.date,
  72                                                 report_lines.payload
  73                                               FROM report_lines
  74                                                 JOIN reports ON reports.id = report_lines.report_id
  75                                                 JOIN market_configs ON reports.market_config_id = market_configs.id
  76                                               WHERE report_lines.payload::jsonb->>'checkpoint' IS NOT NULL`,
  77                         `CREATE INDEX checkpoints_idx ON report_lines ((payload->>'checkpoint'))`,
  78                 },
  79                 Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"},
  80         },
  81         {
  82                 Version: 201805101000,
  83                 Up: []string{
  84                         "CREATE TYPE market_config_status AS ENUM ('enabled', 'disabled', 'invalid_credentials')",
  85                         "ALTER TABLE market_configs ADD status market_config_status NOT NULL DEFAULT 'disabled'",
  86                 },
  87                 Down: []string{
  88                         "ALTER TABLE market_configs DROP COLUMN status",
  89                         "DROP TYPE market_config_status",
  90                 },
  91         },
  92         {
  93                 Version: 201805131000,
  94                 Up: []string{
  95                         "CREATE TYPE user_role AS ENUM ('admin', 'user')",
  96                         "ALTER TABLE users ADD role user_role NOT NULL DEFAULT 'user'",
  97                 },
  98                 Down: []string{
  99                         "ALTER TABLE users DROP COLUMN role",
 100                         "DROP TYPE user_role",
 101                 },
 102         },
 103 }