Automation Process for Website PHP PostgreSQL

Currently, I'm working with a website. This website is written using PHP with PostgreSQL as the back-end. For the server I use Apache (XAMPP). My website has a membership feature consist of free membership and premium membership. Premium membership valid for 1 year after the registration, after that the user membership will be revert back to free membership. To be a premium membership again, the user should pay the membership fee and the account will be upgraded again for the next 1 year (imagine Rapidshare member开发者_运维知识库ship!). How to create a automation process to check and demote an expired membership? I think it should be a background process working on the back-end (postgresql), but I'm not really sure. Any idea or solution?

Big thanks :)

An idea would be like this:

• User pays for premium
• Script inserts an entry in a database with premium cancelling dates, with cancelling date and user FK
• A daily cron/at(depending on OS) job searches the db for cancelling dates with the date of today, and cancels the respective users' premium membership

I hope it helps you :)

Use a table schema like this one:

CREATE TABLE all_accounts (
    id INTEGER NOT NULL PRIMARY KEY,
    premium_expires DATE NOT NULL DEFAULT current_date,
    other_attrs,
    ...
);

Then create a view which you use for queries:

CREATE VIEW accounts AS
SELECT
   account.*, current_date < account.premium_expires AS is_premium_membership
FROM
   account
WHERE 
   id = ?

Now a select to accounts will yield an is_premium_membership attribute which is t when the premium membership is valid. No need for background jobs.

Why don't you check every time when user logs in?