payment: note that Stripe has instituted an additional 1% fee for non-US cards

[hcoop/portal.git] / create-stripe-tables.sql

BEGIN;

CREATE TABLE stripe_payment
(
	charge_id text not null primary key,
	webuser_id integer not null references WebUser (id),
	card_name text not null,
	paid_on date not null,
	gross integer not null,
	fee integer not null
);

CREATE TABLE stripe_join_payment
(
	charge_id text not null primary key,
	app_id integer not null references MemberApp (id) unique,
	card_name text not null,
	authorized_on date not null,
	gross integer not null
-- no fee data, because an uncaptured payment does not have have a balance_transaction
);

CREATE TABLE stripe_processed
(
	stripe_charge_id text not null primary key,
	transaction_id integer not null references transaction (id),

	foreign key (stripe_charge_id) references stripe_payment (charge_id)
);

CREATE TABLE stripe_rejected
(
	stripe_charge_id text not null primary key,
	refunded_on date not null,
	reason text not null,

	foreign key (stripe_charge_id) references stripe_payment (charge_id)
);

CREATE VIEW stripe_handled AS
       (select stripe_charge_id from stripe_processed) union (select stripe_charge_id from stripe_rejected);

COMMIT;