Table of Contents

Subscription

A company can set up a monthly subscriptions. A user purchasing a subscription policy can enroll into all the products of the company.
If the price of the subscription changes then the user shall be notified.
A User will have an option to Un-subscribe at any time.

DB Schema

Table Name: subscriptions

Column_name type Description
id integer primary key
company_id integer Company Id
price decimal monthly subscription price
duration integer duration of the subscription in days (defaults to 30 days)
created_at date_time date on which the subscription was created
updated_at date_time date on which the subscription was last modified.

Table Name: user_subscriptions

Column_name type Description
id integer primary key
subscription_id integer primary key of the subscription
user_id integer primary key of the users table
status integer Status of the subscription (active/cancelled)
start_date date_time Date from which the user purchased the subscription
end_date date_time Date when the subscription ends
next_renewal_date date_time Date when the subscription has to be renewed (end_date + 1)
created_at date_time date on which the subscription was created
updated_at date_time date on which this information was last modified.

Table Name: orders (This table already exists. Just add following columns to orders table)

Column_name type Description
sub_total float all item total
total float total value of the order
discount integer %discount availed on the order
shipping float shipping cost
tax integer % of tax applied on order

Table Name: purchases (This table already exists. Just add following columns to purchases table)

Column_name type Description
discount integer %discount availed on product
actual_price float The actual price of the product. This column is required because, if a user adds courses after buying a subscription, the course should be added here with price=0. The actual price of course will be recorded in this column.
duration integer The duration for which the subscription is valid. The value of this column will be applicable only if the purchasable_type=Subscription

Table Name: user_billing_informations

Column_name type Description
id integer primary key
user_id integer primary key of users table
first_name string user's first name
last_name string user's lastname
country string country
address_1 text address
address_2 text address
city string city
state string state
postal_code string postal code / zip code
created_at date_time date on which the record was created
updated_at date_time date on which this information was last modified.
Purchase of Subscription

Only when pricing policy of the company is set to Subscription based.

When a user purchases a product, He shall be buying a subscription. The Cart Item will be referencing 'Subscription' as a product. Coupon codes can be redeemed against a subscription. Once the order is confirmed, then the product gets added to the users enrolment and the user_subscriptions table will updated. If there is no record for the user in the user_subscriptions table then a new one will be created. If record is already present, then end_date will be updated. The value of the end_date will be set to “Today + value of 'duration' field”. The value of next_renewal_date will be set to “end_date + 1 day”.

Automatic Renewal of Subscription

There will be a background job that will run once in a day. The job will look into the user_subscriptions table for the records that have next_renewal_date < Today and status=Active. For each of the matching records, a new order will be placed with the current price of the subscription. Once the order goes through successfully, the end_date and next_renewal_date columns of user_subscriptions table will be updated with new values.

Other details

Pre requesites

  1. Site admin sets the payment plan as Subscription and enters a dollar value for the subscripton.
  2. To accept CC payments following needs to be checked
    • the Receiver's (site) Merchant account setup in Manage Site ⇒ Merchant account needs correct api username, password and signature.
    • Also ensure that receiver's paypal account should have a Business setup. In order to to receive direct CC payments a business paypal setup is required. When setting up a test store I select an account with “PayPal Payments Pro (Use to represent yourself as a merchant using Pro)” option.
  1. To accept payments via paypal adaptive
    • The person who is buying the product needs a paypal account. This is the payer/sender.
    • The primary receiver, who will receive the payment first and will distribute it to secondary receivers. This, in our case is learnexa. So we require a paypal account for learnexa too. The paypal account details of learnexa need to be present in config/paypal_adaptive.yml. The secondary receiver i.e. the learning site will also require a paypal account.

Rules

  1. All courses which are not free, will be available for learners after the subscription is purchased
  2. The validaty of a subscription purchase lasts for 30 days.
  3. Once the subscription is purchased, all the paid new courses that are published within 30 the subscription purchase are available to the learner. These courses remains available to the learner even if the site admin changes the plan to Pay Per course.

Options

  1. Learner has an option to setup automatic renewal of subscription or end the subscription at any point in time.
  2. Site admin has option to change the payment plan at any point in time.
  3. Site admin has option to change the dollar value of subscription at any point in time. If subscription is getting automatically renewed then the new value will be charged.

Payment Integration

  1. A subscription can be purchased via CC or by adaptive payments. Paypal processes the payments in both these cases. We never store CC info in our db.
  2. Use case 1 (If the user has opted for automatic renewal of subscription and has paid via cc)
    1. When paid via CC the payment happens via paypal direct payments
    2. After successful payment, paypal returns a transaction ID which we store in our database (payment_transactions table). This transaction ID is used for making recuring payments. After each recuring payment, the transaction ID is updated with the new value that is returned by Paypal.
  1. Use case 2 (User has paid via Paypal Adaptive)
    1. We use paypal adaptive and paypal Preapprove Api(https://cms.paypal.com/us/cgi-bin/?cmd=_render-content&content_ID=developer/e_howto_api_APPreapproval) to acheive recuring payments via paypal adaptive.
    2. User on submit of payments, we show the paypal adaptive UI.
    3. User logs in to his paypal account and follows the flow to make payment.
    4. On successful payment, paypal redirects back to learnexa with preauth key which we store in our DB and use for future payments.