Database Schema & Models

Comprehensive database structure for the Profile PS3 erosion control management system.

Database Overview

Profile PS3 uses a relational database schema with 40+ tables optimized for erosion control project management, geographical data, and material specifications.

Database Engines:

Production: MySQL 8.0+ or MariaDB 10.3+
Development: SQLite (fast, file-based)
Testing: SQLite (in-memory for speed)

Core Entity Models

User Management

users

Purpose: Application users (administrators, consultants, contractors)

Model: App\Models\User

Key Fields:

id (bigint, primary key)
user_id (uuid, unique) — Unique identifier
fname, lname — User's name
email (unique, indexed) — Login credential
password — Bcrypt hashed password
company — Company affiliation
phone, address, city, state, zip, country — Contact info
state_province_id (foreign key) — Links to states table
company_phone, company_fax, company_email, company_website
username — Legacy username field
approved (enum: pending|approved|denied) — Account status
regional_mgr — Regional manager assignment
referred_by — Referral source
view_comparables (boolean) — Permission for material comparisons
locked_out (boolean) — Account lock status
get_product_updates (boolean) — Email preference
login_count — Login tracking
email_verified_at — Email verification timestamp
app_authentication_secret (encrypted) — MFA secret
app_authentication_recovery_codes (encrypted) — MFA recovery
remember_token — Persistent login
created_at, updated_at

Relationships:

Has many: projects, applicationCalculators, proganicsCalculators
Belongs to: state (via state_province_id)
Belongs to many: roles, permissions, salesRegions

Eloquent Model Features:

class User extends Authenticatable implements FilamentUser
{
    protected $fillable = [
        'fname', 'lname', 'email', 'password', 'company', 
        'phone', 'address', 'city', 'state', 'zip', 'country'
    ];

    protected $hidden = ['password', 'remember_token', 
        'app_authentication_secret', 'app_authentication_recovery_codes'];

    protected $casts = [
        'email_verified_at' => 'datetime',
        'password' => 'hashed',
        'view_comparables' => 'boolean',
        'locked_out' => 'boolean',
        'get_product_updates' => 'boolean',
    ];

    // MFA Implementation
    public function hasEnabledTwoFactorAuthentication(): bool
    {
        return !is_null($this->app_authentication_secret);
    }

    // Filament Access Control
    public function canAccessPanel(Panel $panel): bool
    {
        if ($panel->getId() === 'admin') {
            return $this->hasRole(['admin', 'super-admin']);
        }
        return $this->approved === 'approved';
    }
}

Project Management

projects

Purpose: Erosion control projects

Model: App\Models\Project

Key Fields:

Id (bigint, primary key)
projectNum (varchar, auto-generated) — PRJ-YYYYMMDD-XXX
userId (foreign key to users) — Project owner
projectAddress, projectCity — Location
city (varchar) — City name
state_id (foreign key to states)
country_id (foreign key to countries)
projectType (foreign key to project_types)
projectStage (foreign key to project_stages)
projectTypeEnergy (foreign key to project_type_energies)
latitude, longitude (decimal) — GPS coordinates
active (enum: active|archived|deleted) — Status
createdOn, modifiedOn — Timestamps

Relationships:

Belongs to: user, type, stage, typeEnergy, cityRelation, state, country
Has many: slopes, channels, soil_tests, attachments, applicationCalculators, proganicsCalculators

Global Scope: UserProjectScope — Automatically filters projects by current user (admins see all)

Eloquent Model:

class Project extends Model
{
    protected $table = 'projects';
    protected $primaryKey = 'Id';

    protected $fillable = [
        'projectNum', 'userId', 'projectAddress', 'projectCity',
        'city', 'state_id', 'country_id', 'projectType', 
        'projectStage', 'projectTypeEnergy', 'latitude', 'longitude'
    ];

    protected $casts = [
        'latitude' => 'decimal:8',
        'longitude' => 'decimal:8',
        'createdOn' => 'datetime',
        'modifiedOn' => 'datetime',
    ];

    // Auto-generate project number on creation
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($project) {
            if (empty($project->projectNum)) {
                $project->projectNum = 'PRJ-' . date('Ymd') . '-' . 
                    str_pad(Project::count() + 1, 3, '0', STR_PAD_LEFT);
            }
        });
    }

    // User scope (non-admins only see their own projects)
    protected static function booted(): void
    {
        static::addGlobalScope(new UserProjectScope);
    }
}

slopes

Purpose: Slope protection calculations within projects

Model: App\Models\Slope

Key Fields:

Id (primary key)
projId (foreign key to projects)
slopeDescription — Name/description
slopeLength, slopeWidth (decimal) — Dimensions
slopeGradient (decimal) — Slope angle
soilType — Soil classification
vegetation — Vegetation requirements
recommendedProducts (text) — Calculated recommendations
createdOn, modifiedOn

channels

Purpose: Channel protection calculations within projects

Model: App\Models\Channel

Key Fields:

Id (primary key)
projId (foreign key to projects)
channelDescription — Name/description
channelLength, channelWidth, channelDepth (decimal)
flowRate (decimal) — Water flow rate
soilType, vegetation
recommendedProducts (text)
createdOn, modifiedOn

soil_tests

Purpose: Soil analysis data for projects

Model: App\Models\SoilTest

Key Fields:

Id (primary key)
projId (foreign key to projects)
testDate (date)
soilType — Classification
pH (decimal)
organicMatter (decimal)
nutrients (json) — NPK and other nutrients
recommendations (text)
createdOn, modifiedOn

attachments

Purpose: Project documentation files (PDFs, images, documents)

Model: App\Models\Attachment

Key Fields:

Id (primary key)
projId (foreign key to projects)
fileName — Original filename
filePath — S3 path or local path
fileSize (integer) — Bytes
mimeType — MIME type
uploadedBy (foreign key to users)
description (text)
created_at, updated_at

File Storage: AWS S3 via Laravel Flysystem

Eloquent Model:

class Attachment extends Model
{
    protected $fillable = [
        'projId', 'fileName', 'filePath', 
        'fileSize', 'mimeType', 'uploadedBy', 'description'
    ];

    protected $casts = [
        'fileSize' => 'integer',
        'uploadedBy' => 'integer',
    ];

    // Generate signed URL for S3 files
    public function getDownloadUrlAttribute(): string
    {
        return Storage::disk('s3')->temporaryUrl(
            $this->filePath, 
            now()->addMinutes(30)
        );
    }

    // Human-readable file size
    public function getFileSizeHumanAttribute(): string
    {
        $bytes = $this->fileSize;
        $units = ['B', 'KB', 'MB', 'GB'];
        $i = 0;
        while ($bytes > 1024 && $i < count($units) - 1) {
            $bytes /= 1024;
            $i++;
        }
        return round($bytes, 2) . ' ' . $units[$i];
    }
}

Material Databases

ecbs (Erosion Control Blankets)

Purpose: ECB product specifications and coverage data

Model: App\Models\Ecb

Key Fields:

Id (primary key)
productName — Product name
manufacturer — Manufacturer name
material — Composition (straw, coconut, synthetic, etc.)
thickness (decimal) — Inches
weight (decimal) — oz/yd²
longevity (integer) — Months
coverageRate (decimal) — Acres per roll
rollLength, rollWidth (decimal) — Feet
sqYardsPerRoll (decimal)
pricePerRoll (decimal)
slopeMax (decimal) — Maximum slope angle
flowVelocity (decimal) — Maximum flow velocity (ft/s)

hecps (Hydraulically Applied Erosion Control Products)

Purpose: HECP product specifications and application rates

Model: App\Models\Hecp

Key Fields:

Id (primary key)
productName
manufacturer
material — Fiber type
applicationRate (decimal) — lbs/acre
coverageRate (decimal) — Acres per ton
longevity (integer) — Months
pricePerTon (decimal)
slopeMax (decimal)

trms (Turf Reinforcement Mats)

Purpose: TRM product specifications and load requirements

Model: App\Models\Trm

Key Fields:

Id (primary key)
productName
manufacturer
material
thickness (decimal)
weight (decimal) — oz/yd²
longevity (integer) — Months
tensileStrength (decimal) — lbs/ft
coverageRate (decimal)
rollLength, rollWidth (decimal)
sqYardsPerRoll (decimal)
pricePerRoll (decimal)
flowVelocity (decimal) — ft/s

Geographic Data

countries

Purpose: Country list for location hierarchy

Model: App\Models\Country

Key Fields:

id (primary key)
name (varchar, unique) — Country name
code (varchar, unique) — ISO 3166-1 alpha-2

Relationships:

Has many: states, projects

states

Purpose: State/province list

Model: App\Models\State

Key Fields:

id (primary key)
country_id (foreign key to countries)
name (varchar) — State/province name
code (varchar) — State abbreviation (e.g., "CA", "TX")

Relationships:

Belongs to: country
Has many: cities, projects, users

cities

Purpose: City list for projects

Model: App\Models\City

Key Fields:

id (primary key)
state_id (foreign key to states)
name (varchar) — City name

Relationships:

Belongs to: state
Has many: projects

sales_regions

Purpose: Sales territory management

Model: App\Models\SalesRegion

Key Fields:

id (primary key)
name — Region name (e.g., "Northeast", "Southwest")
states (json) — Array of state IDs in region
manager_id (foreign key to users) — Regional manager
active (boolean)

Relationships:

Belongs to: manager (User)
Belongs to many: users (via user_notifcation_regions)

Calculator Systems

application_calculators

Purpose: Application rate calculator session storage

Model: App\Models\ApplicationCalculator

Key Fields:

id (primary key)
user_id (foreign key to users)
project_id (nullable, foreign key to projects)
sessionData (json) — Calculation inputs and results
calculationType — Calculator type identifier
createdOn, modifiedOn

Session Data Structure:

{
  "area": 5.25,
  "areaUnit": "acres",
  "slopeGradient": 3,
  "soilType": "clay",
  "selectedProduct": "straw-blanket",
  "applicationRate": 1500,
  "totalCoverage": 7.5,
  "estimatedCost": 4250.00
}

proganics_calculators

Purpose: ProGanics BSM calculator session storage

Model: App\Models\ProganicsCalculator

Key Fields:

id (primary key)
user_id (foreign key to users)
project_id (nullable, foreign key to projects)
sessionData (json) — BSM calculation inputs
calculationType — "proganics_bsm"
createdOn, modifiedOn

Export System

bdo_exports

Purpose: BDO export history tracking

Model: App\Models\BdoExport

Key Fields:

id (primary key)
exportType — Entity type (projects|users|ecbs|hecps|trms)
exportDate (datetime)
recordCount (integer) — Records exported
status (enum: pending|success|failed)
errorMessage (text, nullable)
created_at, updated_at

bdo_export_checkpoints

Purpose: Incremental export tracking (exports only changed records)

Model: App\Models\BdoExportCheckpoint

Key Fields:

id (primary key)
entityType — Table name
entityId — Record ID
lastExported (datetime) — Last export timestamp
checksum (varchar) — MD5 hash of record data

Logic: Only export records where updated_at > lastExported or checksum differs

Taxonomy Tables

project_types: Project type classifications

Id, projectType (e.g., "Slope Protection", "Channel Protection", "Restoration")

project_stages: Project lifecycle stages

Id, projectStage (e.g., "Planning", "Bidding", "Construction", "Complete")

project_type_energies: Energy project types

Id, projectTypeEnergy (e.g., "Solar", "Wind", "Pipeline")

Database Migrations

Profile PS3 includes 50+ migrations for schema versioning.

Key Migration Files:

database/migrations/
  2024_01_01_000000_create_users_table.php
  2024_01_02_000000_create_projects_table.php
  2024_01_03_000000_create_slopes_channels_soil_tests_tables.php
  2024_01_04_000000_create_ecbs_hecps_trms_tables.php
  2024_01_05_000000_create_geographic_tables.php
  2024_01_06_000000_create_calculator_tables.php
  2024_01_07_000000_create_bdo_export_tables.php
  ...

Running Migrations:

php artisan migrate              # Run pending migrations
php artisan migrate:fresh        # Drop all tables and re-migrate
php artisan migrate:fresh --seed # With seeders
php artisan migrate:rollback     # Rollback last batch

Database Seeders

Development Seeders:

database/seeders/
  DatabaseSeeder.php          # Master seeder
  UserSeeder.php              # Sample users
  ProjectSeeder.php           # Sample projects
  MaterialSeeder.php          # ECBs, HECPs, TRMs
  GeographicSeeder.php        # Countries, states, cities
  RolePermissionSeeder.php    # Roles and permissions

Running Seeders:

php artisan db:seed                          # Run all seeders
php artisan db:seed --class=UserSeeder       # Run specific seeder
php artisan migrate:fresh --seed             # Fresh database with data

Model Factories

Factory Usage (for testing and seeding):

// Create single user
User::factory()->create();

// Create multiple projects with relationships
User::factory()
    ->has(Project::factory()->count(3))
    ->create();

// Create project with slopes and channels
Project::factory()
    ->has(Slope::factory()->count(2))
    ->has(Channel::factory()->count(1))
    ->create();

Query Optimization

Indexes:

Primary keys on all tables
Foreign key indexes for joins
Unique indexes on email, country.code, state.code
Composite indexes on frequently filtered columns

Eager Loading Example:

// Prevent N+1 queries
$projects = Project::with([
    'user', 
    'state', 
    'country', 
    'slopes', 
    'channels', 
    'attachments'
])->get();

Query Scopes:

// In Project model
public function scopeActive($query)
{
    return $query->where('active', 'active');
}

public function scopeByType($query, $type)
{
    return $query->where('projectType', $type);
}

// Usage
Project::active()->byType(1)->get();

Database Backup & Restore

Backup:

# MySQL dump
mysqldump -u root -p profileps3 > backup.sql

# Laravel backup (if package installed)
php artisan backup:run

Restore:

mysql -u root -p profileps3 < backup.sql

Database Performance Tips

Use indexes on frequently queried columns
Eager load relationships to prevent N+1 queries
Cache geographic data (rarely changes)
Use database transactions for multi-step operations
Monitor slow queries with Laravel Debugbar
Optimize JSON columns with MySQL 8.0 JSON functions
Partition large tables (exports, logs) by date

Database Overview​

Core Entity Models​

User Management​

users​

Project Management​

projects​

slopes​

channels​

soil_tests​

attachments​

Material Databases​

ecbs (Erosion Control Blankets)​

hecps (Hydraulically Applied Erosion Control Products)​

trms (Turf Reinforcement Mats)​

Geographic Data​

countries​

states​

cities​

sales_regions​

Calculator Systems​

application_calculators​

proganics_calculators​

Export System​

bdo_exports​

bdo_export_checkpoints​

Taxonomy Tables​

Database Migrations​

Database Seeders​

Model Factories​

Query Optimization​

Database Backup & Restore​

Database Performance Tips​

Database Overview

Core Entity Models

User Management

users

Project Management

projects

slopes

channels

soil_tests

attachments

Material Databases

ecbs (Erosion Control Blankets)

hecps (Hydraulically Applied Erosion Control Products)

trms (Turf Reinforcement Mats)

Geographic Data

countries

states

cities

sales_regions

Calculator Systems

application_calculators

proganics_calculators

Export System

bdo_exports

bdo_export_checkpoints

Taxonomy Tables

Database Migrations

Database Seeders

Model Factories

Query Optimization

Database Backup & Restore

Database Performance Tips