 Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory : /home/gositeme/domains/soundstudiopro.com/private_html/ |
# Credit Expiration System Setup Guide
## Overview
This guide explains how to set up automatic credit expiration and commercial rights management for your 30-day subscription model.
## Current Status
- ✅ **Payment Processing** - Credits added on purchase
- ✅ **Expiration Tracking** - Expiration dates logged
- ✅ **Terms Updated** - Users informed of 30-day expiration
- ❌ **Automatic Removal** - Credits not automatically removed
- ❌ **Database Integration** - Credits not stored in database
- ❌ **Email Notifications** - No expiration warnings
## Required Database Changes
### 1. Update Users Table
```sql
-- Add credit and subscription tracking columns
ALTER TABLE users ADD COLUMN credits INT DEFAULT 0;
ALTER TABLE users ADD COLUMN subscription_expires DATETIME NULL;
ALTER TABLE users ADD COLUMN commercial_rights_expires DATETIME NULL;
ALTER TABLE users ADD COLUMN expiration_warning_sent TINYINT(1) DEFAULT 0;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
```
### 2. Create Credit Purchases Table
```sql
CREATE TABLE credit_purchases (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
package VARCHAR(50) NOT NULL,
credits INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_intent_id VARCHAR(255) NOT NULL,
expires_at DATETIME NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_expires (user_id, expires_at),
INDEX idx_expires (expires_at)
);
```
### 3. Create Credit Usage Table
```sql
CREATE TABLE credit_usage (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
track_id VARCHAR(255) NOT NULL,
credits_used INT DEFAULT 1,
usage_type ENUM('download', 'preview', 'commercial') DEFAULT 'download',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_usage (user_id, created_at)
);
```
## Automatic Expiration Setup
### 1. Cron Job Configuration
Add this to your server's crontab to run daily at 2 AM:
```bash
# Edit crontab
crontab -e
# Add this line
0 2 * * * /usr/bin/php /home/gositeme/domains/soundstudiopro.com/public_html/cron/expire_credits.php >> /var/log/credit_expiration.log 2>&1
```
### 2. Update Webhook Handler
Modify `webhooks/stripe.php` to actually update the database:
```php
function addCreditsToUser($user_id, $credits, $package, $subscription_period, $payment_intent_id) {
global $pdo; // Your database connection
// Calculate expiration date (30 days from now)
$expiration_date = date('Y-m-d H:i:s', strtotime('+30 days'));
try {
// Start transaction
$pdo->beginTransaction();
// Update user credits
$stmt = $pdo->prepare("
UPDATE users
SET credits = credits + ?,
subscription_expires = ?,
commercial_rights_expires = ?,
expiration_warning_sent = 0,
updated_at = NOW()
WHERE id = ?
");
$stmt->execute([$credits, $expiration_date, $expiration_date, $user_id]);
// Log the purchase
$stmt = $pdo->prepare("
INSERT INTO credit_purchases (user_id, package, credits, amount, payment_intent_id, expires_at)
VALUES (?, ?, ?, ?, ?, ?)
");
$stmt->execute([$user_id, $package, $credits, $amount, $payment_intent_id, $expiration_date]);
// Commit transaction
$pdo->commit();
// Log success
logPaymentEvent('credits_added_to_database', [
'user_id' => $user_id,
'credits_added' => $credits,
'expiration_date' => $expiration_date
]);
} catch (Exception $e) {
// Rollback on error
$pdo->rollBack();
throw $e;
}
}
```
### 3. Update Payment Processing
Modify `process_credit_payment.php` to use database:
```php
function updateUserCredits($user_id, $credits) {
global $pdo;
try {
$stmt = $pdo->prepare("UPDATE users SET credits = ? WHERE id = ?");
$stmt->execute([$credits, $user_id]);
logPaymentEvent('credits_updated_in_database', [
'user_id' => $user_id,
'new_credits' => $credits
]);
} catch (Exception $e) {
logPaymentEvent('database_update_error', [
'user_id' => $user_id,
'error' => $e->getMessage()
]);
throw $e;
}
}
```
## Email Notification Setup
### 1. Configure Email System
Update the email functions in `cron/expire_credits.php`:
```php
function sendExpirationNotification($user) {
// Use your existing email system (PHPMailer, etc.)
$mailer = new PHPMailer();
$mailer->setFrom('noreply@soundstudiopro.com', 'SoundStudioPro');
$mailer->addAddress($user['email'], $user['username']);
$mailer->Subject = "Your SoundStudioPro Credits Have Expired";
// HTML email template
$mailer->isHTML(true);
$mailer->Body = getExpirationEmailTemplate($user);
$mailer->send();
}
```
### 2. Email Templates
Create professional email templates for:
- **Expiration Warning** (7 days before)
- **Expiration Notice** (day of expiration)
- **Renewal Reminder** (3 days after expiration)
## Testing the System
### 1. Test Credit Addition
```php
// Test script to add credits
$user_id = 1;
$credits = 30;
$expiration = date('Y-m-d H:i:s', strtotime('+30 days'));
$stmt = $pdo->prepare("UPDATE users SET credits = ?, subscription_expires = ? WHERE id = ?");
$stmt->execute([$credits, $expiration, $user_id]);
```
### 2. Test Expiration
```php
// Manually run expiration script
php /path/to/cron/expire_credits.php
```
### 3. Monitor Logs
Check these log files:
- `/logs/credit_expirations.log` - Expiration events
- `/logs/user_credits.log` - Credit additions
- `/logs/stripe_actions.log` - Payment processing
## Commercial Rights Enforcement
### 1. Track Usage Rights
```php
function checkCommercialRights($user_id, $track_id) {
global $pdo;
$stmt = $pdo->prepare("
SELECT commercial_rights_expires
FROM users
WHERE id = ? AND commercial_rights_expires > NOW()
");
$stmt->execute([$user_id]);
return $stmt->fetch() !== false;
}
```
### 2. Enforce Download Restrictions
```php
function downloadTrack($user_id, $track_id) {
if (!checkCommercialRights($user_id, $track_id)) {
throw new Exception('Commercial rights expired. Please renew your subscription.');
}
// Process download
// Log usage
// Deduct credits
}
```
## Monitoring and Maintenance
### 1. Daily Monitoring
- Check expiration logs
- Monitor failed payments
- Review credit usage patterns
### 2. Weekly Reports
- Expired users count
- Revenue from renewals
- Credit usage statistics
### 3. Monthly Cleanup
- Archive old purchase records
- Clean up expired sessions
- Update user statistics
## Security Considerations
### 1. Database Security
- Use prepared statements
- Implement proper indexing
- Regular backups
### 2. Cron Job Security
- Restrict file permissions
- Use dedicated user account
- Monitor execution logs
### 3. Email Security
- Verify email addresses
- Rate limit notifications
- Handle bounces properly
## Implementation Checklist
- [ ] Update database schema
- [ ] Configure cron job
- [ ] Update webhook handler
- [ ] Set up email notifications
- [ ] Test credit addition
- [ ] Test expiration process
- [ ] Monitor logs
- [ ] Update user interface
- [ ] Train support team
## Support and Troubleshooting
### Common Issues:
1. **Credits not expiring** - Check cron job execution
2. **Emails not sending** - Verify email configuration
3. **Database errors** - Check connection and permissions
4. **Payment issues** - Review Stripe webhook logs
### Log Locations:
- `/logs/credit_expirations.log`
- `/logs/user_credits.log`
- `/logs/stripe_actions.log`
- `/logs/credit_payments.log`
This system ensures your 30-day subscription model works correctly and automatically manages credit expiration and commercial rights.