create-stripe-tables.sql

   1 BEGIN;
   2
   3 CREATE TABLE stripe_payment
   4 (
   5         charge_id text not null primary key,
   6         webuser_id integer not null references WebUser (id),
   7         card_name text not null,
   8         paid_on date not null,
   9         gross integer not null,
  10         fee integer not null
  11 );
  12
  13 CREATE TABLE stripe_join_payment
  14 (
  15         charge_id text not null primary key,
  16         app_id integer not null references MemberApp (id) unique,
  17         card_name text not null,
  18         authorized_on date not null,
  19         gross integer not null
  20 -- no fee data, because an uncaptured payment does not have have a balance_transaction
  21 );
  22
  23 CREATE TABLE stripe_processed
  24 (
  25         stripe_charge_id text not null primary key,
  26         transaction_id integer not null references transaction (id),
  27
  28         foreign key (stripe_charge_id) references stripe_payment (charge_id)
  29 );
  30
  31 CREATE TABLE stripe_rejected
  32 (
  33         stripe_charge_id text not null primary key,
  34         refunded_on date not null,
  35         reason text not null,
  36
  37         foreign key (stripe_charge_id) references stripe_payment (charge_id)
  38 );
  39
  40 CREATE VIEW stripe_handled AS
  41        (select stripe_charge_id from stripe_processed) union (select stripe_charge_id from stripe_rejected);
  42
  43 COMMIT;