ducky/subscriptions
ducky/subscriptions/subscription_postgres.go
Use stripe's built-in subscriptions. We're going to use Stripe's built-in subscriptions to manage our subscriptions, which required us to change a lot of stuff. We're now tracking stripe_subscription instead of stripe_customer, and we need to track the plan, status, and if the user is canceling after this month. We also don't need to know when to begin charging them (Stripe will do it), but we should track when their trial starts and ends, when the current pay period they're in starts and ends, when they canceled (if they've canceled), the number of failed charge attempts they've had, and the last time we notified them about billing (To avoid spamming users). We get to delete all the stuff about periods, which is nice. We updated our SubscriptionChange type to match. Notably, there are a lot of non-user modifiable things now, but our Stripe webhook will need to use them to update our database records and keep them in sync. We no longer need to deal with sorting stuff, which is also nice. Our SubscriptionStats have been updated to be... useful? Now we can track how many users we have, and how many of them have failing credit cards, how many are canceling at the end of their current payment period, and how many users are on each plan. We also switched around how the TestUpdateSubscription loops were written, to avoid resetting more than we needed to. Before, we had to call store.reset() after every single change iteration. Now we get to call it only when switching stores. This makes a significant difference in the amount of time it takes to run tests. Finally, we added a test case for retrieving subscription stats. It's minimal, but it works.
1.1 --- a/subscription_postgres.go Sun Jun 14 02:48:08 2015 -0400 1.2 +++ b/subscription_postgres.go Tue Jun 16 23:09:59 2015 -0400 1.3 @@ -1,10 +1,10 @@ 1.4 package subscriptions 1.5 1.6 import ( 1.7 - "database/sql" 1.8 - "time" 1.9 + "log" 1.10 1.11 "code.secondbit.org/uuid.hg" 1.12 + 1.13 "github.com/lib/pq" 1.14 "github.com/secondbit/pan" 1.15 ) 1.16 @@ -16,8 +16,8 @@ 1.17 } 1.18 1.19 func (p Postgres) resetSQL() *pan.Query { 1.20 - var sub Subscription 1.21 - query := pan.New(pan.POSTGRES, "TRUNCATE "+pan.GetTableName(sub)) 1.22 + var subscription Subscription 1.23 + query := pan.New(pan.POSTGRES, "TRUNCATE "+pan.GetTableName(subscription)) 1.24 return query.FlushExpressions(" ") 1.25 } 1.26 1.27 @@ -30,9 +30,9 @@ 1.28 return nil 1.29 } 1.30 1.31 -func (p Postgres) createSubscriptionSQL(sub Subscription) *pan.Query { 1.32 - fields, values := pan.GetFields(sub) 1.33 - query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(sub)) 1.34 +func (p Postgres) createSubscriptionSQL(subscription Subscription) *pan.Query { 1.35 + fields, values := pan.GetFields(subscription) 1.36 + query := pan.New(pan.POSTGRES, "INSERT INTO "+pan.GetTableName(subscription)) 1.37 query.Include("(" + pan.QueryList(fields) + ")") 1.38 query.Include("VALUES") 1.39 query.Include("("+pan.VariableList(len(values))+")", values...) 1.40 @@ -44,25 +44,30 @@ 1.41 _, err := p.Exec(query.String(), query.Args...) 1.42 if e, ok := err.(*pq.Error); ok && e.Constraint == "subscriptions_pkey" { 1.43 err = ErrSubscriptionAlreadyExists 1.44 - } else if e, ok := err.(*pq.Error); ok && e.Constraint == "subscriptions_stripe_customer_key" { 1.45 - err = ErrStripeCustomerAlreadyExists 1.46 + } else if e, ok := err.(*pq.Error); ok && e.Constraint == "subscriptions_stripe_subscription_key" { 1.47 + err = ErrStripeSubscriptionAlreadyExists 1.48 } 1.49 return err 1.50 } 1.51 1.52 func (p Postgres) updateSubscriptionSQL(id uuid.ID, change SubscriptionChange) *pan.Query { 1.53 - var sub Subscription 1.54 - query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(sub)+" SET") 1.55 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "StripeCustomer")+" = ?", change.StripeCustomer) 1.56 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "Amount")+" = ?", change.Amount) 1.57 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "Period")+" = ?", change.Period) 1.58 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "BeginCharging")+" = ?", change.BeginCharging) 1.59 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "LastCharged")+" = ?", change.LastCharged) 1.60 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "LastNotified")+" = ?", change.LastNotified) 1.61 - query.IncludeIfNotNil(pan.GetUnquotedColumn(sub, "InLockout")+" = ?", change.InLockout) 1.62 + var subscription Subscription 1.63 + query := pan.New(pan.POSTGRES, "UPDATE "+pan.GetTableName(subscription)+" SET") 1.64 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "StripeSubscription")+" = ?", change.StripeSubscription) 1.65 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "Plan")+" = ?", change.Plan) 1.66 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "Status")+" = ?", change.Status) 1.67 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "Canceling")+" = ?", change.Canceling) 1.68 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "TrialStart")+" = ?", change.TrialStart) 1.69 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "TrialEnd")+" = ?", change.TrialEnd) 1.70 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "PeriodStart")+" = ?", change.PeriodStart) 1.71 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "PeriodEnd")+" = ?", change.PeriodEnd) 1.72 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "CanceledAt")+" = ?", change.CanceledAt) 1.73 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "FailedChargeAttempts")+" = ?", change.FailedChargeAttempts) 1.74 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "LastFailedCharge")+" = ?", change.LastFailedCharge) 1.75 + query.IncludeIfNotNil(pan.GetUnquotedColumn(subscription, "LastNotified")+" = ?", change.LastNotified) 1.76 query.FlushExpressions(", ") 1.77 query.IncludeWhere() 1.78 - query.Include(pan.GetUnquotedColumn(sub, "UserID")+" = ?", id) 1.79 + query.Include(pan.GetUnquotedColumn(subscription, "UserID")+" = ?", id) 1.80 return query.FlushExpressions(" ") 1.81 } 1.82 1.83 @@ -73,8 +78,8 @@ 1.84 1.85 query := p.updateSubscriptionSQL(id, change) 1.86 res, err := p.Exec(query.String(), query.Args...) 1.87 - if e, ok := err.(*pq.Error); ok && e.Constraint == "subscriptions_stripe_customer_key" { 1.88 - return ErrStripeCustomerAlreadyExists 1.89 + if e, ok := err.(*pq.Error); ok && e.Constraint == "subscriptions_stripe_subscription_key" { 1.90 + return ErrStripeSubscriptionAlreadyExists 1.91 } else if err != nil { 1.92 return err 1.93 } 1.94 @@ -89,10 +94,10 @@ 1.95 } 1.96 1.97 func (p Postgres) deleteSubscriptionSQL(id uuid.ID) *pan.Query { 1.98 - var sub Subscription 1.99 - query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(sub)) 1.100 + var subscription Subscription 1.101 + query := pan.New(pan.POSTGRES, "DELETE FROM "+pan.GetTableName(subscription)) 1.102 query.IncludeWhere() 1.103 - query.Include(pan.GetUnquotedColumn(sub, "UserID")+" = ?", id) 1.104 + query.Include(pan.GetUnquotedColumn(subscription, "UserID")+" = ?", id) 1.105 return query.FlushExpressions(" ") 1.106 } 1.107 1.108 @@ -112,47 +117,16 @@ 1.109 return nil 1.110 } 1.111 1.112 -func (p Postgres) listSubscriptionsLastChargedBeforeSQL(cutoff time.Time) *pan.Query { 1.113 - var sub Subscription 1.114 - fields, _ := pan.GetFields(sub) 1.115 - query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(sub)) 1.116 - query.IncludeWhere() 1.117 - query.Include(pan.GetUnquotedColumn(sub, "LastCharged")+" < ?", cutoff) 1.118 - query.IncludeOrder(pan.GetUnquotedColumn(sub, "LastCharged") + " ASC") 1.119 - return query.FlushExpressions(" ") 1.120 -} 1.121 - 1.122 -func (p Postgres) listSubscriptionsLastChargedBefore(cutoff time.Time) ([]Subscription, error) { 1.123 - var results []Subscription 1.124 - query := p.listSubscriptionsLastChargedBeforeSQL(cutoff) 1.125 - rows, err := p.Query(query.String(), query.Args...) 1.126 - if err != nil { 1.127 - return results, err 1.128 - } 1.129 - for rows.Next() { 1.130 - var sub Subscription 1.131 - err := pan.Unmarshal(rows, &sub) 1.132 - if err != nil { 1.133 - return results, err 1.134 - } 1.135 - results = append(results, sub) 1.136 - } 1.137 - if err := rows.Err(); err != nil { 1.138 - return results, err 1.139 - } 1.140 - return results, nil 1.141 -} 1.142 - 1.143 func (p Postgres) getSubscriptionsSQL(ids []uuid.ID) *pan.Query { 1.144 - var sub Subscription 1.145 - fields, _ := pan.GetFields(sub) 1.146 + var subscription Subscription 1.147 + fields, _ := pan.GetFields(subscription) 1.148 intIDs := make([]interface{}, len(ids)) 1.149 for pos, id := range ids { 1.150 intIDs[pos] = id 1.151 } 1.152 - query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(sub)) 1.153 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM "+pan.GetTableName(subscription)) 1.154 query.IncludeWhere() 1.155 - query.Include(pan.GetUnquotedColumn(sub, "UserID") + " IN") 1.156 + query.Include(pan.GetUnquotedColumn(subscription, "UserID") + " IN") 1.157 query.Include("("+pan.VariableList(len(intIDs))+")", intIDs...) 1.158 return query.FlushExpressions(" ") 1.159 } 1.160 @@ -168,12 +142,12 @@ 1.161 return results, err 1.162 } 1.163 for rows.Next() { 1.164 - var sub Subscription 1.165 - err := pan.Unmarshal(rows, &sub) 1.166 + var subscription Subscription 1.167 + err := pan.Unmarshal(rows, &subscription) 1.168 if err != nil { 1.169 return results, err 1.170 } 1.171 - results[sub.UserID.String()] = sub 1.172 + results[subscription.UserID.String()] = subscription 1.173 } 1.174 if err := rows.Err(); err != nil { 1.175 return results, err 1.176 @@ -181,39 +155,81 @@ 1.177 return results, nil 1.178 } 1.179 1.180 -func (p Postgres) getSubscriptionStatsSQL() *pan.Query { 1.181 - var sub Subscription 1.182 - amountColumn := pan.GetUnquotedColumn(sub, "Amount") 1.183 - query := pan.New(pan.POSTGRES, "SELECT") 1.184 - query.Include("COUNT(*), SUM(" + amountColumn + "), AVG(" + amountColumn + ")") 1.185 - query.Include("FROM " + pan.GetTableName(sub)) 1.186 +func (p Postgres) getSubscriptionStatsCountSQL() *pan.Query { 1.187 + var subscription Subscription 1.188 + query := pan.New(pan.POSTGRES, "SELECT COUNT(*) FROM") 1.189 + query.Include(pan.GetTableName(subscription)) 1.190 + return query.FlushExpressions(" ") 1.191 +} 1.192 + 1.193 +func (p Postgres) getSubscriptionStatsCancelingSQL() *pan.Query { 1.194 + var subscription Subscription 1.195 + query := pan.New(pan.POSTGRES, "SELECT COUNT(*) FROM") 1.196 + query.Include(pan.GetTableName(subscription)) 1.197 + query.IncludeWhere() 1.198 + query.Include(pan.GetUnquotedColumn(subscription, "Canceling")+" = ?", true) 1.199 + return query.FlushExpressions(" ") 1.200 +} 1.201 + 1.202 +func (p Postgres) getSubscriptionStatsFailingSQL() *pan.Query { 1.203 + var subscription Subscription 1.204 + query := pan.New(pan.POSTGRES, "SELECT COUNT(*) FROM") 1.205 + query.Include(pan.GetTableName(subscription)) 1.206 + query.IncludeWhere() 1.207 + statuses := []interface{}{"past_due", "unpaid"} 1.208 + query.Include(pan.GetUnquotedColumn(subscription, "Status")+" IN ("+pan.VariableList(len(statuses))+")", statuses...) 1.209 + return query.FlushExpressions(" ") 1.210 +} 1.211 + 1.212 +func (p Postgres) getSubscriptionStatsPlansSQL() *pan.Query { 1.213 + var subscription Subscription 1.214 + fields := []interface{}{pan.GetUnquotedColumn(subscription, "Plan"), "COUNT(*)"} 1.215 + query := pan.New(pan.POSTGRES, "SELECT "+pan.QueryList(fields)+" FROM") 1.216 + query.Include(pan.GetTableName(subscription)) 1.217 + query.Include("GROUP BY " + pan.GetUnquotedColumn(subscription, "Plan")) 1.218 return query.FlushExpressions(" ") 1.219 } 1.220 1.221 func (p Postgres) getSubscriptionStats() (SubscriptionStats, error) { 1.222 - query := p.getSubscriptionStatsSQL() 1.223 + stats := SubscriptionStats{ 1.224 + Plans: map[string]int64{}, 1.225 + } 1.226 + query := p.getSubscriptionStatsCountSQL() 1.227 + err := p.QueryRow(query.String(), query.Args...).Scan(&stats.Number) 1.228 + if err != nil { 1.229 + log.Printf("Error querying for total subscriptions: %+v\n", err) 1.230 + return stats, err 1.231 + } 1.232 + query = p.getSubscriptionStatsCancelingSQL() 1.233 + err = p.QueryRow(query.String(), query.Args...).Scan(&stats.Canceling) 1.234 + if err != nil { 1.235 + log.Printf("Error querying for canceling subscriptions: %+v\n", err) 1.236 + return stats, err 1.237 + } 1.238 + query = p.getSubscriptionStatsFailingSQL() 1.239 + err = p.QueryRow(query.String(), query.Args...).Scan(&stats.Failing) 1.240 + if err != nil { 1.241 + log.Printf("Error querying for failing subscriptions: %+v\n", err) 1.242 + return stats, err 1.243 + } 1.244 + query = p.getSubscriptionStatsPlansSQL() 1.245 rows, err := p.Query(query.String(), query.Args...) 1.246 if err != nil { 1.247 - return SubscriptionStats{}, err 1.248 + log.Printf("Error querying for plans: %+v\n", err) 1.249 + return stats, err 1.250 } 1.251 - var stats SubscriptionStats 1.252 for rows.Next() { 1.253 - var number, total sql.NullInt64 1.254 - var mean sql.NullFloat64 1.255 - if err := rows.Scan(number, total, mean); err != nil { 1.256 - return stats, err 1.257 + var plan string 1.258 + var count int64 1.259 + err := rows.Scan(&plan, &count) 1.260 + if err != nil { 1.261 + log.Printf("Error scanning database row for plans: %+v\n", err) 1.262 + continue 1.263 } 1.264 - if number.Valid { 1.265 - stats.Number = number.Int64 1.266 - } 1.267 - if total.Valid { 1.268 - stats.TotalAmount = total.Int64 1.269 - } 1.270 - if mean.Valid { 1.271 - stats.MeanAmount = mean.Float64 1.272 - } 1.273 + stats.Plans[plan] = count 1.274 } 1.275 if err := rows.Err(); err != nil { 1.276 + log.Printf("Error querying for plans: %+v\n", err) 1.277 return stats, err 1.278 } 1.279 return stats, nil