-- by policy, cash is only annual CREATE TABLE business ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL, display_name TEXT NOT NULL DEFAULT '', email TEXT NOT NULL DEFAULT '', phone TEXT NOT NULL DEFAULT '', address_city TEXT NOT NULL, address_country TEXT NOT NULL, address_line1 TEXT NOT NULL, address_line2 TEXT NOT NULL, address_postal_code TEXT NOT NULL, address_state TEXT NOT NULL, business_tier_name TEXT NOT NULL, UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS business_upd_trig AFTER UPDATE ON business BEGIN update business SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- SELECT DISTINCT address_country FROM allowed_postals for allowed countries CREATE TABLE allowed_postals ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, address_country TEXT NOT NULL, address_state TEXT NOT NULL, address_postal_code TEXT NOT NULL, UNIQUE(address_country, address_state, address_postal_code) ); CREATE TRIGGER IF NOT EXISTS allowed_postals_upd_trig AFTER UPDATE ON allowed_postals BEGIN update allowed_postals SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- TODO: can cancellations and plan downgrades be represented by this setup? -- cancellation example: -- assumed: -- service is lumpy by day -- not leap year: -- rounding: -- also round down by same amount you round them down -- -- case 1: -- facts: -- paid $35 for start plan on Jan 1 -- cancelled on Feb 15 -- conclusions: -- they are owed 30.589 by pure maths -- by rounding rules, refund would be 30.00 -- -- case 2: -- alternate facts: -- paid $35 for start plan on Jan 1 -- cancelled on Feb 27 -- conclusions: -- they are owed $29.43 by pure maths -- by rounding rules, refund would by $25.00 -- -- case 3: -- alternate facts: -- paid $35 for start plan on Jan 1 -- cancelled on Jan 1 -- conclusions: -- they are owed $34.90 by pure maths -- by rounding rules, refund would by $30.00 -- -- N.B. you still owe sales tax on earned parts of payment -- negative payment example: CREATE TABLE business_payment( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date at which to start counting payment -- e.g. can have a grace period of N days payment_effective TIMESTAMP NOT NULL, -- normally, would be 1 year after payment_effective, -- in the case of an upgrade need to prorate it. payment_effective_until TIMESTAMP NOT NULL, -- TODO: check that payment_effective_until > payment_effective amount INTEGER NOT NULL, business_id INTEGER REFERENCES business_tier(id) NOT NULL, business_tier_name TEXT NOT NULL, -- agreed on tier going forward at time of payment comments TEXT NOT NULL DEFAULT '', ); CREATE TRIGGER IF NOT EXISTS business_payment_upd_trig AFTER UPDATE ON business_payment BEGIN update business_payment SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_tier_pricing( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- e.g. for all renewals after this date, -- and before the next record entry price_effective TIMESTAMP NOT NULL, amount INTEGER NOT NULL, business_tier_name TEXT NOT NULL, comments TEXT NOT NULL DEFAULT '' ); CREATE TRIGGER IF NOT EXISTS business_payment_upd_trig AFTER UPDATE ON business_payment BEGIN update business_payment SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END;