Database schema

ElCoco uses a single relational database (MySQL in production, SQLite for local development). All tables follow Laravel conventions: auto-incrementing id primary keys and created_at / updated_at timestamps unless noted otherwise.

Entity relationship overview

quote_block_categories
        │
        │ hasMany
        ▼
   quote_blocks
        │
        │ (referenced by quote_items)
        │
   ─────┴──────────────────────────────────
                                           │
              quotes ◄───────────── quote_items
                │           belongsTo quote_block (nullable)
                │
                │ hasMany
                ▼
          quote_replies

Relationships at a glance:

Model	Relationship	Target
`Quote`	`hasMany`	`QuoteItem`
`Quote`	`hasMany`	`QuoteReply`
`QuoteItem`	`belongsTo`	`Quote`
`QuoteItem`	`belongsTo`	`QuoteBlock` (nullable FK)
`QuoteBlock`	`belongsTo`	`QuoteBlockCategory`
`QuoteBlockCategory`	`hasMany`	`QuoteBlock`
`QuoteReply`	`belongsTo`	`Quote`

Application tables

`users`

Standard Laravel authentication table created by Breeze.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`name`	`varchar(255)`	Display name
`email`	`varchar(255)`	Unique
`email_verified_at`	`timestamp`	Nullable
`password`	`varchar(255)`	Bcrypt hash
`remember_token`	`varchar(100)`	Nullable
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

`quote_block_categories`

Top-level groupings for service blocks displayed in the builder sidebar.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`name`	`varchar(255)`	Display name
`description`	`text`	Nullable
`icon`	`varchar(255)`	Nullable. Icon identifier for the category
`color`	`varchar(255)`	Nullable. Colour used in the UI for this category
`is_active`	`tinyint(1)`	Default `1`. Hidden from builder when `0`
`order`	`int`	Default `0`. Ascending sort position
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

The icon and color columns are defined in the QuoteBlockCategory model’s $fillable array and are returned by QuoteApiController::getBlocks(), but they are not present in the initial migration. Add them with a follow-up migration if needed.

`quote_blocks`

Individual service units selectable in the quote builder. Each block belongs to one category.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`name`	`varchar(255)`	Block display name
`description`	`text`	Nullable
`category_id`	`bigint unsigned`	FK → `quote_block_categories.id` (cascade delete)
`base_price`	`decimal(10,2)`	Default `0.00`
`default_hours`	`int`	Default `0`. Estimated work hours
`config`	`json`	Nullable. Array of `[{key: value}]` extras
`formula`	`text`	Nullable. Reserved for future pricing formulas
`validation_rules`	`json`	Nullable. Reserved for future input validation
`is_active`	`tinyint(1)`	Default `1`. Only active blocks appear in builder
`order`	`int`	Default `0`. Ascending sort within category
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

Indexes: category_id, is_active, order

`quotes`

Top-level record for a client quote submission. Created when a client submits the builder form or saves a draft.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`reference`	`varchar(255)`	Unique. Auto-generated on create: `COT-` + `strtoupper(uniqid())`
`client_name`	`varchar(255)`	—
`client_email`	`varchar(255)`	Used as the reply-to address
`client_company`	`varchar(255)`	Nullable
`client_phone`	`varchar(255)`	Nullable
`project_description`	`text`	Nullable. Free-text from the builder form
`additional_requirements`	`text`	Nullable
`data`	`json`	Full raw payload from the builder (cast to `array`)
`subtotal`	`decimal(12,2)`	Pre-tax total
`tax`	`decimal(12,2)`	Tax amount
`total`	`decimal(12,2)`	Final total (subtotal + tax)
`total_hours`	`int`	Sum of all block hours
`status`	`enum`	`draft`, `sent`, `accepted`, `rejected`, `expired`. Default `draft`
`sent_at`	`timestamp`	Nullable. Set when status becomes `sent`
`pdf_path`	`varchar(255)`	Nullable. Relative path in the `public` disk
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

Indexes: reference, client_email, status, created_at

The reference field is not generated by a migration default — it is set in the Quote model’s boot() method using a creating hook:

static::creating(function ($quote) {
    $quote->reference = 'COT-' . strtoupper(uniqid());
});

Quote status lifecycle

draft ──► sent ──► accepted
               └──► rejected
               └──► expired

Status	Set by	Meaning
`draft`	Client (`save-draft`)	Saved but not yet submitted; no PDF generated
`sent`	Client (`submit`)	Submitted; `sent_at` recorded; PDF stored
`accepted`	Admin (`PATCH status`)	DMI accepted the quote
`rejected`	Admin (`PATCH status`)	DMI declined the quote
`expired`	Admin (`PATCH status`)	Quote validity window has passed

`quote_items`

One row per service block added to a quote. A quote has one or more items.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`quote_id`	`bigint unsigned`	FK → `quotes.id` (cascade delete)
`quote_block_id`	`bigint unsigned`	FK → `quote_blocks.id` (cascade delete). Not nullable per migration
`name`	`varchar(255)`	Snapshot of the block name at submission time
`description`	`text`	Nullable. Snapshot of block description
`type`	`varchar(255)`	Block type string (e.g. `generic`)
`quantity`	`int`	Default `1`
`hours`	`int`	Default `0`. Hours for this line
`unit_price`	`decimal(10,2)`	Price per unit
`total_price`	`decimal(10,2)`	`unit_price * quantity`
`data`	`json`	Nullable. Additional block config snapshot (cast to `array`)
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

Indexes: quote_id, quote_block_id

Block name and description are snapshotted into quote_items at submission time. This ensures historical quote data remains accurate even if the original block is later edited or deleted.

`quote_replies`

Records each admin reply (meeting confirmation) sent for a quote.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`quote_id`	`bigint unsigned`	FK → `quotes.id` (cascade delete)
`message`	`text`	Nullable. Formatted meeting confirmation text (e.g. `"Cita Virtual en GoogleMeet: 20/03/2026 14:30"`)
`sent_to_email`	`varchar(255)`	Nullable. Email address the reply was sent to
`sent_at`	`timestamp`	The scheduled meeting datetime
`created_at`	`timestamp`	—
`updated_at`	`timestamp`	—

Only quote_id and sent_at are in the QuoteReply model’s $fillable. The message and sent_to_email columns exist in the database but must be set via fill() or direct assignment.

Laravel infrastructure tables

These tables are created by Laravel’s built-in migrations and are not specific to ElCoco.

sessions

Stores database-backed sessions when SESSION_DRIVER=database.

Column	Type
`id`	`varchar` (primary key)
`user_id`	`bigint` (nullable, indexed)
`ip_address`	`varchar(45)` nullable
`user_agent`	`text` nullable
`payload`	`longtext`
`last_activity`	`int` (indexed)

cache / cache_locks

Stores cached values when CACHE_STORE=database.

Column	Type
`key`	`varchar` (primary key)
`value`	`mediumtext`
`expiration`	`int`

jobs / job_batches / failed_jobs

Queue tables used when QUEUE_CONNECTION=database.

jobs — pending jobs waiting to be processed.
job_batches — batch metadata for grouped jobs.
failed_jobs — records of jobs that threw an unhandled exception.

password_reset_tokens

Temporary tokens for the password-reset flow. Keyed by email address.

personal_access_tokens

Sanctum API tokens. Uses a polymorphic tokenable relation so any model can issue tokens.

Column	Type
`id`	`bigint` (primary key)
`tokenable_type`	`varchar`
`tokenable_id`	`bigint`
`name`	`text`
`token`	`varchar(64)` unique
`abilities`	`text` nullable
`last_used_at`	`timestamp` nullable
`expires_at`	`timestamp` nullable (indexed)

Overview

Core Features

Configuration

Database

Entity relationship overview

Application tables

`users`

`quote_block_categories`

`quote_blocks`

`quotes`

Quote status lifecycle

`quote_items`

`quote_replies`

Laravel infrastructure tables

Build docs developers (and LLMs) love

Overview

Core Features

Configuration

Database

​Entity relationship overview

​Application tables

​users

​quote_block_categories

​quote_blocks

​quotes

​Quote status lifecycle

​quote_items

​quote_replies

​Laravel infrastructure tables

Build docs developers (and LLMs) love

Entity relationship overview

Application tables

`users`

`quote_block_categories`

`quote_blocks`

`quotes`

Quote status lifecycle

`quote_items`

`quote_replies`

Laravel infrastructure tables