Tables

companies

site_subscription_id ID of the subscription ID corresponding to the company

subscription_plans

id
name name of plan (Eg - best, better, enterprise)
features strings separated by any specific character (may be new line)
price float
price_unit_in_days integer. Default → 30
max_active_user_count Maximum allowed users count in the given billing period.
per_user_cost
user_pack_size
code
custom Boolean. True → customized enterprise plans
created_at
updated_at

site_custom_plans

site_subscription_id Integer
subscription_plan_id Integer

site_subscriptions

id
status (Can be TRIAL, TRIAL_EXPIRED, TRIAL_EXPIRED_DELETE, SUBSCRIBED, SUBSCRIPTION_CANCELED, SUBSCRIPTION_EXPIRED, SUBSCRIPTION_EXPIRED_DELETE)
status_changed_at datetime when the status field was updated
status_change_notified boolean. Indicates whether the site owner is notified about the recent status change or not
start_at When the subscription period starts
end_at When the subscription period ends
next_renewal_at When the subscription will be renewed. Usually will be end_at - 1.hour
preapproval_id Preapproval ID returned by wepay, used when charging the customer via a background job
last_payment_id ID of corresponding payment record
last_payment_status (SUCCESS, FAILURE)
subscription_plan_id The latest subscription plan of this company. Foreign key for subscription_plans
price
price_unit_in_days
max_active_user_count Maximum allowed users count in the given billing period.
per_user_cost
user_pack_size
previous_status
previous_status_end_at
previous_status_start_at
previous_subscription_plan_id
created_at
updated_at

site_orders

id
site_subscription_id
payment_status Set to SUCCESS if payment was successful and FAILURE otherwise. Initially the value can be NULL and later based on how the payment went through this can be set to SUCCESS or FAILURE
discount
sub_total
total
description
created_at
updated_at
subscription_plan_id

site_order_items

id
site_order_id
item_type Can by subscription, additional user etc…Currently the value will be 'subscription'
item_id
price
qty
created_at
updated_at

site_payments

id
site_order_id Order for which this payment was made.
ip_address IP address of the payer
payer_id site_subscrituion_id of the company who is paying
status SUCCESS or FAILURE
amount
payment_method Currently only WEPAY
params data that was passed to the gateway
response response that was returned by the gateway
created_at
updated_at

site_usages

Saves the user details for each and every billing cycle at the end of the billing cycle.

id
site_subscription_id Foreign key to site_subscriptions table
billing_period_start_time When a given billing period starts.
billing_period_end_time When a given billing period ends.
max_active_user_count Maximum allowed users count in the given billing period.
active_user_count The actual active users in the billing period.
created_at
updated_at

users

subscription_active boolean. Whether this user is active in the current billing cycle.

General flow of record creation

When company purchases subscription

  1. Create a record in site_orders and site_order_items. At present the items for an order will always be 1 viz. subscription. Set payment_status = NIL
  2. Generate the parameters that need to be sent to payment gateway and based on those parameters and the site_order_id create a record in site_payments table. Set 'status' column to NIL. After the response from the gateway is received, update the 'response' column of site_payments and based on the success or failure update the 'status' column. Also update the payment_status column in site_orders table to proper value i.e. success or failure.
  3. Also if payment was successful then update the site_subscriptions table with proper status, preapproval_id and last_payment_id and last_payment_status