database_schema.txt

=Tables

* access_group_priveleges
 * Links groups to have privledges with nodes, limited possibly by a schedule or membership requirement
 * id, access_group_id, node_id, schedule_id, membership_type_id
 
* access_group_users
 * Links users to being in groups
 * access_group_id, user_id
 
* access_groups    
 * List of actual groups (and maybe a way to disable the whole group?)
 * id, name, enabled
 
* access_manual_unlock  
 * List of times that doors should automatically unlock
 * id, name, node_id, schedule_id, enabled
 
* access_user_priveleges
 * List of privleges for doors for each user possibly limited by schedule and membership type
 * This is also used for "Everyone" by setting the user_id to -1
 * id, user_id, node_id, schedule_id, membership_type_id
 
* cards
 * List of RFID cards
 * id, user_id, name, serial, hash, enabled, fault, balance
 
* ci_sessions
 * Used by Code Ignitor to manage user sessions
 * session_id, ip_address, user_agent, last_activity, user_data
 
* current_memberships
 * A view used to only select memberships which are currently active
 * id, user_id, type_id, start, end, purchased, price, notes
 
* current_schedules
 * A view used to see which schedules apply right now
 * schedule_id
  * select distinct schedule_id from schedule_times where 
    (day_of_week is NULL OR day_of_week=DAYOFWEEK(NOW())) AND 
    (start_date is NULL OR start_date <= DATE(NOW())) AND 
    (end_date is NULL OR end_date>= DATE(NOW())) AND 
    (start_time is null OR start_time <= TIME(NOW())) AND 
    (end_time is null OR end_time >= TIME(NOW()));
    
* groups 
 * A table used to manage login user groups (public/member/admin/developer)
 * id, name, desc, admin
           
* ipn_log
 * A table used to keep a log of all the PayPal IPN messages
 * id, listener_name, transaction_type, transaction_id, status, message, ipn_data_hash, detail, create_at
 
* ipn_order_items       
 * A list of order items from paypal transaction orders (references a single ipn_order
 * id, order_id, item_name, item_number, quantity, mc_gross, mc_handling, mc_shipping, tax, cost_per_item, option_name_1, option_selection_1,...,option_name_7,option_selection_7, create_at, updated_at
 
* ipn_orders            
 * A list of paypal order transactions recieved on IPN
 * id, notify_version, verify_sign, test_ipn, protection_eligibility, charset, btn_id, address_city, address_country, address_country_code, address_name, address_state, address_status, address_street, address_zip
 * first_name, last_name, payer_business_name, payer_email, payer_id, payer_status, contact_phone, residence_country, business,
 * receiver_email, receiver_id, custom, invoice, memo, tax, auth_id, auth_exp, auth_amount, auth_status, num_cart_items, parent_txn_id,
 * payment_date, payment_status, payment_type, pending_reason, reason_code, remaining_settle, shipping_method, shipping, transaction_entity,
 * txn_id, txn_type, exchange_rate, mc_currency, mc_fee, mc_gross, mc_handling, mc_shipping, payment_fee, payment_gross, settle_amount, settle_currency,
 * auction_buyer_id, auction_closing_date, auction_multi_item, for_auction, subscr_date, subscr_effective, period1, period2, period3, amount1, amount2, amount3,
 * mc_amount1, mc_amount2, mc_amount3, recurring, reattempt, retry_at, recur_times, username, password, subscr_id, case_id, case_type, case_creation_date,
 * order_status, discount, shipping_discount, ipn_track_id, transaction_subject, created_at, updated_at
 
* log    
 * A list of node action log items (item_id/amount is future for logging vending machine transactions)
 * id, node_id, card_id, user_id, time, type, item_id, amount, result

* memberships         
 * A depricated list of memberships and their owners, start and end date (NULL if membership is not activated)
 * id, user_id, type_id, start, end, purchased, price, notes

* membership_names        
 * A list of memberships and their owners, start and end date (NULL if membership is not activated)
 * id, owner_id, type_id, name, created_at

* membership_credits
 * A table to maintain credits for memberships
 * id, membership_id, owner_id, type_id, price_paid, auto_activate, start, end, purchased, notes
 
* membership_users
 * A table to link users to memberships
 * id, membership_id, user_id
 
* membership_types
 * A list of membership types, including price and length in days
 * id, name, length, number, price, subscription, paypal_button, paypal_subscription_button, disabled
 
* membership_log
 * A log of actions taken with memberships
 * id, membership_id, user_id, action, for_user_id, result

* nodes
 * A list of nodes/doors 
 * id, name, type, hostname, ip, mac, login_key, enabled
 
* schedules
 * A list of schedules that can be used control times for access control
 * id, name

* schedule_times
 * A list of times that make up each schedule
 * id, schedule_id, day_of_week, start_date, end_date, start_time, end_time

* tool_time
 * A list to track the amount time allowed for each user on tools (future, unimplemented)
 * id, node_type, user_id, balance
 
* user_login_sessions
 * User Login Sessions (Flexi-Auth)
 * user_id, series, token, login_date

* user_privilege_groups
 * Group Privileges (Flexi-Auth) for Login Groups
 * id, group_id, privilege_id
 
* user_privilege_users  
 * Specific Privileges Per User (Flexi-Auth) for Site Access Privileges
 * id, user_id, privilege_id
 
* user_privileges       
 * A list of site privileges (Flexi-Auth)
 * id, name, desc
 
* user_profiles         
 * A collection of user personal data (Flexi-Auth)
 * id, user_id, company, first_name, last_name, phone, address, address2, city, state, zipcode
 
* users
 * Table to keep track of user logins (from flexi-auth)
 * id, group_id, email, username, password, ip_address, salt, activation_token, forgotten_password_token, forgotten_password_expire,
 * update_email_token, update_email, active, suspend, fail_login_attempts, fail_login_ip_address, date_fail_login_ban, date_last_login, date_added