Database Schema Design

Version: 1.0 Last Updated: October 3, 2025 Database: SQLite (development), PostgreSQL (production)

Overview

This document defines the complete database schema for Feedframer v2, including tables, columns, indexes, and relationships.

Tables Summary

Table: users

Purpose: Store user authentication and profile data.

Note: This table is created by Laravel's default migration. We'll add a tier column for subscription management.

Additional Columns (Migration)

Schema::table('users', function (Blueprint $table) {
    $table->string('tier')->default('free')->after('email_verified_at');
    // Values: 'free', 'premium'
});

Complete Schema

Indexes

$table->unique('email');
$table->index('tier');

Tier-Based Limits

// Free tier limits
const POST_LIMIT_FREE = 12;           // Posts per account
const ACCOUNT_LIMIT_FREE = 1;         // Connected accounts
const FETCH_INTERVAL_FREE = 3600;     // 1 hour in seconds
const API_RATE_LIMIT_FREE = 100;      // Requests per hour

// Premium tier limits
const POST_LIMIT_PREMIUM = null;      // Unlimited
const ACCOUNT_LIMIT_PREMIUM = null;   // Unlimited
const FETCH_INTERVAL_PREMIUM = 300;   // 5 minutes in seconds
const API_RATE_LIMIT_PREMIUM = 1000;  // Requests per hour

Table: instagram_accounts

Purpose: Store connected Instagram business accounts with authentication tokens.

Schema Definition

Schema::create('instagram_accounts', function (Blueprint $table) {
    $table->id();

    // Relationships
    $table->foreignId('user_id')->constrained()->onDelete('cascade');

    // Instagram Data
    $table->string('instagram_user_id')->unique();
    $table->string('username');

    // Authentication
    $table->text('access_token');  // Encrypted via model cast
    $table->timestamp('token_expires_at')->nullable();

    // Account Status
    $table->enum('status', [
        'active',           // Token valid, actively syncing
        'expired',          // Token expired, needs refresh
        'disconnected',     // User disconnected
        'token_invalid'     // Token refresh failed
    ])->default('active');

    // Profile Information (optional)
    $table->string('name')->nullable();
    $table->text('biography')->nullable();
    $table->text('profile_picture_url')->nullable();
    $table->unsignedInteger('followers_count')->nullable();
    $table->unsignedInteger('follows_count')->nullable();
    $table->unsignedInteger('media_count')->nullable();

    // Sync Tracking
    $table->timestamp('last_fetch_at')->nullable();
    $table->timestamp('last_token_refresh_at')->nullable();

    // Timestamps
    $table->timestamps();
    $table->softDeletes();
});

Column Details

Indexes

// Primary lookup
$table->unique('instagram_user_id');

// Query optimization
$table->index('user_id');
$table->index('status');
$table->index('last_fetch_at');
$table->index(['user_id', 'status']);

// Token management
$table->index('token_expires_at');

// Foreign key
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

Model Casts

protected $casts = [
    'access_token' => 'encrypted',
    'token_expires_at' => 'datetime',
    'last_fetch_at' => 'datetime',
    'last_token_refresh_at' => 'datetime',
    'deleted_at' => 'datetime',
    'followers_count' => 'integer',
    'follows_count' => 'integer',
    'media_count' => 'integer',
];

Business Logic

Account Limits:

• Free tier: 1 account per user
• Premium tier: Unlimited accounts
• Enforce in controller before creating new account

Status Transitions:

• active → expired (token expires)
• expired → active (successful token refresh)
• expired → token_invalid (refresh failed)
• active → disconnected (user action)
• Any → active (reconnection)

Token Refresh Strategy:

• Refresh tokens expiring within 7 days
• Mark as expired if token_expires_at < now
• Mark as token_invalid if refresh API call fails
• Email user on status change from active

Table: posts

Purpose: Store cached Instagram media (posts) for API serving.

Schema Definition

Schema::create('posts', function (Blueprint $table) {
    $table->id();

    // Relationships
    $table->foreignId('instagram_account_id')->constrained()->onDelete('cascade');

    // Instagram Data
    $table->string('instagram_post_id')->unique();

    // Media Information
    $table->enum('type', ['IMAGE', 'VIDEO', 'CAROUSEL_ALBUM', 'REELS']);
    $table->text('caption')->nullable();
    $table->text('media_url');
    $table->text('thumbnail_url')->nullable();
    $table->string('permalink');

    // Engagement Metrics
    $table->unsignedInteger('comments_count')->default(0);
    $table->unsignedInteger('like_count')->default(0);

    // Timestamps
    $table->timestamp('published_at');      // Instagram publish time
    $table->timestamp('last_updated_at');   // Last time we updated this post
    $table->timestamp('fetched_at');        // Last time we fetched this post

    $table->timestamps();
    $table->softDeletes();
});

Column Details

Indexes

// Primary lookup
$table->unique('instagram_post_id');

// Query optimization for API
$table->index('instagram_account_id');
$table->index('type');
$table->index('published_at');
$table->index(['instagram_account_id', 'published_at']);
$table->index(['instagram_account_id', 'type']);

// Foreign key
$table->foreign('instagram_account_id')
      ->references('id')
      ->on('instagram_accounts')
      ->onDelete('cascade');

Model Casts

protected $casts = [
    'published_at' => 'datetime',
    'last_updated_at' => 'datetime',
    'fetched_at' => 'datetime',
    'deleted_at' => 'datetime',
    'comments_count' => 'integer',
    'like_count' => 'integer',
];

Business Logic

Tier-Based Post Limits:

• Free tier: Store only latest 12 posts per account
• Premium tier: Store all posts (up to 10,000 per Instagram limit)
• Implement in FetchInstagramPosts job

Soft Delete Strategy:

• Posts soft-deleted 30 days after account marked as expired or disconnected
• Allows grace period for users to reconnect
• Scheduled job runs daily to clean up old soft-deleted posts

Update Strategy:

• Use updateOrCreate() with instagram_post_id
• Update last_updated_at only if data changed
• Always update fetched_at to track fetch attempts

Table: personal_access_tokens

Purpose: API key management via Laravel Sanctum.

Note: This table is created by Laravel Sanctum migration. No modifications needed.

Existing Schema

Indexes

$table->unique('token');
$table->index(['tokenable_type', 'tokenable_id']);

Usage

• Users generate API keys for accessing the REST/GraphQL API
• Tokens are hashed and shown only once at creation
• Each token has a descriptive name for user reference
• Revoked = deleted from table

Relationships Diagram

┌─────────────────┐
│     users       │
│  - id           │
│  - email        │
│  - tier         │
└────────┬────────┘
         │ 1:N
         ▼
┌─────────────────────────┐
│  instagram_accounts     │
│  - id                   │
│  - user_id (FK)         │
│  - instagram_user_id    │
│  - access_token (enc)   │
│  - status               │
│  - last_fetch_at        │
└────────┬────────────────┘
         │ 1:N
         ▼
┌─────────────────────────┐
│       posts             │
│  - id                   │
│  - instagram_account_id │
│  - instagram_post_id    │
│  - type                 │
│  - media_url            │
│  - published_at         │
└─────────────────────────┘

┌─────────────────────────┐
│ personal_access_tokens  │
│  - tokenable_id → user  │
│  - token (hashed)       │
│  - name                 │
└─────────────────────────┘

Migration Order

Migrations must be created in this order to satisfy foreign key constraints:

1. Modify users table - Add tier column
2. Create instagram_accounts table - Depends on users
3. Create posts table - Depends on instagram_accounts
4. personal_access_tokens - Already exists via Sanctum

Data Retention Policy

Instagram Accounts

• Active accounts: Retained indefinitely
• Disconnected accounts: Retained for 30 days, then hard deleted
• Posts from deleted accounts: Cascade deleted via foreign key

Posts

• Active accounts: Posts retained per tier limits
• Free tier: Keep only 12 most recent posts per account
• Premium tier: Keep all posts (up to Instagram's 10,000 limit)
• Orphaned posts: Cascade deleted when account deleted

Scheduled Cleanup Jobs

// Daily cleanup job
- Hard delete instagram_accounts soft-deleted > 30 days ago
- Hard delete posts soft-deleted > 30 days ago
- Prune free tier accounts to 12 posts (delete older posts)

Performance Considerations

Query Optimization

Most Common Queries:

1. Get posts for API (high frequency)

SELECT * FROM posts
WHERE instagram_account_id IN (user's accounts)
  AND deleted_at IS NULL
ORDER BY published_at DESC
LIMIT 12;

Indexes used: instagram_account_id, published_at

2. Filter posts by type

SELECT * FROM posts
WHERE instagram_account_id = ?
  AND type = 'IMAGE'
  AND deleted_at IS NULL
ORDER BY published_at DESC;

Indexes used: instagram_account_id, type, published_at

3. Find accounts needing refresh

SELECT * FROM instagram_accounts
WHERE status = 'active'
  AND last_fetch_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
  AND deleted_at IS NULL;

Indexes used: status, last_fetch_at

4. Find tokens expiring soon

SELECT * FROM instagram_accounts
WHERE status = 'active'
  AND token_expires_at <= DATE_ADD(NOW(), INTERVAL 7 DAY)
  AND token_expires_at > NOW()
  AND deleted_at IS NULL;

Indexes used: token_expires_at, status

Index Strategy Summary

instagram_accounts:

• Single column: instagram_user_id (unique), user_id, status, last_fetch_at, token_expires_at
• Composite: user_id + status

posts:

• Single column: instagram_post_id (unique), instagram_account_id, type, published_at
• Composite: instagram_account_id + published_at, instagram_account_id + type

Security Considerations

Encrypted Data

• instagram_accounts.access_token - Encrypted at rest using Laravel's encrypted cast
• Never log or expose access tokens in responses

Data Isolation

• All queries must filter by authenticated user's accounts
• API middleware ensures users only access their own data
• Foreign key cascades prevent orphaned data

Token Security

• Instagram tokens: encrypted in database
• API tokens (Sanctum): hashed, never stored plain-text
• API tokens shown only once at generation

Database Seeder Structure

For development and testing:

// DatabaseSeeder.php
User::factory()
    ->count(10)
    ->has(
        InstagramAccount::factory()
            ->count(2)
            ->has(Post::factory()->count(20))
    )
    ->create();

Testing Considerations

Factory Requirements

UserFactory:

• Generate users with random tiers (80% free, 20% premium)

InstagramAccountFactory:

• Generate realistic Instagram usernames
• Create valid access tokens (encrypted)
• Set token_expires_at 60 days from now
• Vary status (90% active, 5% expired, 5% disconnected)

PostFactory:

• Generate all media types (IMAGE, VIDEO, CAROUSEL_ALBUM)
• Create realistic captions
• Set published_at in past (random last 90 days)
• Vary engagement metrics

Summary

Key Decisions

1. Tier Management: Simple tier column on users table (MVP approach)

   • Easier to implement than full Cashier integration
   • Sufficient for free/premium distinction
   • Can migrate to Cashier later without schema changes

2. Token Storage: Encrypted text column for access tokens

   • Laravel's encrypted cast handles encryption/decryption
   • Protects sensitive Instagram credentials

3. Soft Deletes: On both accounts and posts

   • 30-day grace period for reconnection
   • Prevents accidental data loss
   • Scheduled cleanup maintains database size

4. Indexes: Optimized for API read performance

   • Composite indexes on common filter combinations
   • Separate indexes for admin/maintenance queries

5. Data Types:

   • Use proper unsigned integers for counts
   • Use timestamps for all date/time fields
   • Use enums for constrained values (status, type)

Implementation Checklist

• Define users table extension (tier column)
• Define instagram_accounts table structure
• Define posts table structure
• Plan all indexes for performance
• Document foreign key relationships
• Document tier-based limits
• Plan data retention policy
• Document security considerations

Next Steps: Begin Phase 2 - Create actual migrations and models

Document Version: 1.0 Last Reviewed: October 3, 2025 Next Review: After Phase 2 implementation