sql.billing-postgres.sql Maven / Gradle / Ivy

Go to download
CREATE TABLE profound_billing_credit_card (
  credit_card_key SERIAL,
  created_on TIMESTAMP,
  user_key INTEGER,
  name TEXT,
  mask TEXT,
  expiration_month SMALLINT,
  expiration_year SMALLINT,
  brand TEXT,
  cardholder TEXT,
  gateway_key INTEGER,
  token TEXT,
  PRIMARY KEY ("credit_card_key")
);

CREATE INDEX p_b_credit_card_user ON profound_billing_credit_card USING BTREE (user_key);

CREATE TABLE profound_billing_gateway (
  gateway_key SERIAL,
  created_on TIMESTAMP,
  created_by INTEGER,
  changed_on TIMESTAMP,
  changed_by INTEGER,
  provider TEXT,
  configuration TEXT,
  PRIMARY KEY ("gateway_key")
);

CREATE TABLE profound_billing_transaction (
  transaction_key SERIAL,
  user_key INTEGER,
  success BOOLEAN,
  created_on TIMESTAMP,
  created_by INTEGER,
  amount_in_cents INTEGER,
  currency TEXT,
  primary key ("transaction_key")
);

CREATE INDEX p_b_transaction_user ON profound_billing_transaction USING BTREE (user_key, created_on);

CREATE TABLE profound_billing_transaction_credit_card (
  transaction_key INTEGER,
  credit_card_key INTEGER,
  request TEXT,
  response TEXT,
  PRIMARY KEY ("transaction_key", "credit_card_key")
);

CREATE INDEX p_b_transaction_credit_card_card ON profound_billing_transaction_credit_card USING BTREE (credit_card_key);

CREATE TABLE profound_billing_invoice (
  invoice_key SERIAL,
  user_key INTEGER,
  created_on TIMESTAMP,
  amount_in_cents INTEGER,
  currency TEXT,
  due_on DATE,
  PRIMARY KEY (invoice_key)
);

CREATE INDEX p_b_invoice_user ON profound_billing_invoice USING BTREE (user_key);

CREATE TABLE profound_billing_charge (
  charge_key SERIAL,
  user_key INTEGER,
  created_on TIMESTAMP,
  product TEXT,
  quantity DECIMAL,
  amount_in_cents DECIMAL,
  currency TEXT,
  charge_date DATE,
  invoice_key INTEGER,
  PRIMARY KEY (charge_key)
);

CREATE INDEX p_b_charge_user ON profound_billing_charge USING BTREE (user_key);
CREATE INDEX p_b_charge_invoice ON profound_billing_charge USING BTREE (invoice_key);
CREATE INDEX p_b_charge_date ON profound_billing_charge USING BTREE (created_on);

CREATE TABLE profound_billing_invoice_transaction (
  transaction_key INTEGER,
  invoice_key INTEGER,
  amount_in_cents INTEGER,
  PRIMARY KEY (transaction_key, invoice_key)
);

CREATE INDEX p_b_invoice_transaction_invoice ON profound_billing_invoice_transaction USING BTREE (invoice_key) ;

CREATE TABLE profound_billing_user_control (
  user_key INTEGER,
  charges_invoiced_to_user_key INTEGER,
  invoice_create_day INTEGER,
  invoice_payment_day INTEGER,
  credit_card_key INTEGER,
  PRIMARY KEY (user_key)
);

CREATE INDEX p_b_user_control_create ON profound_billing_user_control USING BTREE (invoice_create_day);
CREATE INDEX p_b_user_control_payment ON profound_billing_user_control USING BTREE (invoice_payment_day);