# Database Communication

**NUMBER:** ADR-DB-001
**STATUS:** CANONICAL AUTHORITY
**SCOPE:** Every line of code that interacts with the database in this project.
**RULE:** This is the ONLY definitive source for database interaction patterns, read/write paths, and schema conventions. Any other document mentioning this topic is a REFERENCE, not a definition.
**VIOLATION:** Implementing database communication patterns based on any other source constitutes an architectural breach.

> **DTO Taxonomy and Sparse Update patterns:** `09_DTOS.md`
> **How to handle exceptions thrown during database operations:** `02_ERROR_BOUNDARIES.md`
> **What data actually goes into the database:** `SPECIFICATIONS/04_DATABASE/01_TABLES.md`
> **When to wrap DB operations in a transaction:** `10_ACTION_SERVICE_ORCHESTRATION.md` → Section 4.5

---

## 1. Philosophy

**Respect Eloquent, Enforce Data Boundaries.**

We do not fight the framework by building useless wrapper layers for operations Eloquent already handles natively. However, we strictly prevent the physical shape of the database (column names, raw data types) from leaking into business logic layers.

---

## 2. Absolute Prohibitions

```text
❌ PROHIBITED: Creating Interfaces for database communication (YAGNI).
❌ PROHIBITED: Creating Repository classes that wrap simple CRUD operations.
❌ PROHIBITED: Passing raw Arrays across layer boundaries (from Controller to Service).
❌ PROHIBITED: Writing Eloquent queries directly inside Controllers or Actions.
❌ PROHIBITED: Any write operations (Insert/Update/Delete) inside Query Classes.
```

---

## 3. Schema Conventions (CANONICAL)

These conventions apply to all database tables. Any migration MUST adhere to these rules.

| Element | Convention | Rationale |
|:---|:---|:---|
| **IDs** | `BIGSERIAL PRIMARY KEY` | PostgreSQL standard |
| **Timestamps** | `TIMESTAMPTZ` via `timestamps()` | Timezone-aware |
| **Monetary Values** | `DECIMAL(10,2)` | NOT `FLOAT`, NOT `DOUBLE` |
| **Status Columns** | `VARCHAR` | NO PostgreSQL-level ENUMs (requires `ALTER TABLE` to add values) |
| **Soft Deletes** | PROHIBITED | No `deleted_at` columns. Deletion is either hard delete or blocked at the application level. |

---

## 4. The Read Path

Reading data is categorized by complexity, not by a single rigid rule. The goal is to keep the Model clean and prevent the Service from becoming a farm of `where` clauses.

### 4.1 Simple Reads (Atomic Conditions)

**Definition:** A query targeting a single entity with simple conditions (columns within the same table).

**Rule:** Write as a **Scope** inside the Model. Call it directly in the Service.

```php
// Inside Model
public function scopeWhereIsAgent(Builder $query): Builder
{
    return $query->whereRaw("client_type_flags @> ?::jsonb", ['["agent"]']);
}

// Inside Service
$agents = Client::whereIsAgent()->get();
```

**DTO Rule:** Do NOT use a DTO for simple reads. Return the Eloquent `Collection` directly to the API Resource.

#### Scope Organization: Model vs Trait (CANONICAL)

If a Model file becomes too crowded, extract scopes into a Trait. The threshold for extraction depends on conceptual grouping, not just line count.

| Keep in Model | Extract to Trait (in `Domain/Models/Scopes/`) |
|:---|:---|
| Scopes knowing specific column names of THIS model only (e.g., `scopeSearch` knowing `name` and `phone`). | Scopes related to a reusable concept or feature (e.g., `HasClientTypes` containing 5 JSONB scopes). |

```text
❌ PROHIBITED: Creating a Trait for a single scope that is only used by one Model.
```

### 4.2 Complex Reads (Query Classes)

**Definition:** Any query involving one or more of the following:
- Joining multiple tables (`JOIN` / complex Eager Loading).
- Aggregate operations (`SUM`, `COUNT`, `GROUP BY`).
- Complex sorting logic depending on calculations.
- Reading data that does not map cleanly to a single Eloquent Model (e.g., financial reports).

**Rules:**
1. Class name describes the **question** asked (e.g., `GetNextUnpaidInstallmentQuery`), NOT the entity.
2. Contains a single public method that executes the query and returns the result.
3. Uses Scopes defined in Section 4.1.
4. **Strictly Read-Only:** MUST NOT call `create`, `update`, `delete`, or `save`.

**When to return a DTO?**
- **NO:** If the Query returns an Eloquent Model or Collection that can be passed directly to an API Resource.
- **YES:** If the data is aggregated from multiple tables and its final shape does not represent a specific Model. The DTO acts as a "Read Model."

```php
class CustomerFinancialSummaryQuery
{
    public function get(int $customerId): CustomerFinancialSummaryDTO
    {
        $data = Client::where('id', $customerId)
            ->withSum(['contracts as total_debt' => fn($q) => $q->active()], 'remaining_amount')
            ->firstOrFail();

        return CustomerFinancialSummaryDTO::fromArray([
            'totalDebt' => $data->total_debt ?? '0.00',
        ]);
    }
}
```

### 4.3 Complex Reads (Response Builders)

**Definition:** A read endpoint's response requires assembling data from multiple distinct sources (Models, Query Classes, external states) and requires conditional assembly logic.

**Rules:**
1. Located in `app/Domains/{Domain}/Services/`.
2. Named `{Entity}DetailBuilder` or `{Purpose}Builder`.
3. Input is an Eloquent Model (usually via Route Model Binding).
4. Output is a raw `array` (passed to Controller for final response).
5. **Database queries ARE allowed** — this is the class's explicit purpose.
6. **Business rules are NOT allowed** — no validation, no state changes, no transactions.

**Permitted Display Formatting:**
Simple formatting operations that transform raw query results into presentation-ready values are permitted inside the Builder. These are display-level transformations, not business logic:
- Calculating a percentage from two numbers (`value / total * 100`)
- Formatting numbers with `number_format()`
- Formatting dates with `->format('Y-m-d')`
- Filtering out zero-value entries from display arrays
- Mapping raw database rows to response-shaped arrays

**The distinction:** A business rule determines *whether a state is valid* (e.g., "can this payment be deleted?"). A display format determines *how a valid number appears in JSON* (e.g., "show 2 decimal places"). The Builder may do the latter but never the former.

---

## 5. The Write Path

### 5.1 Create Operation

The Service receives a DTO and delegates storage to Eloquent. The Service MUST NOT know database column names.

```php
// Inside Service
public function create(CreateContractDTO $dto): Contract
{
    return Contract::create($dto->toModelArray());
}
```

### 5.2 Update Operation

The DTO builds an array containing ONLY the fields intended for modification.

```php
// Inside Service
public function update(Client $client, UpdateCustomerDTO $dto): Client
{
    $client->update($dto->toUpdateArray());
    return $client->fresh();
}
```

> **Note on Null Semantics:** For distinguishing between "field not sent" and "field sent as null," the DTO MUST use the Sparse Update pattern. See `09_DTOS.md` → Section 6.

### 5.3 Delete & State Changes

No DTO is needed for these operations because no new complex data structures are being passed.

```php
// State change (permitted directly in Service)
$contract->update(['status' => ContractStatus::Active->value, 'signed_at' => now()]);

// Deletion
$model->delete();
```

**Exception:** If a state change requires complex mathematical transformations to populate multiple columns, a DTO MUST be used.

---

## 6. Special Cases

### 6.1 Database Transactions

`DB::transaction()` MUST be used **ONLY** in the **Action** (the orchestrator). A single Service call that wraps its own internal logic in a transaction is acceptable, but coordinating multiple Services/Queries MUST happen in the Action.

```php
// CORRECT: Inside Action
class RecordPaymentAction {
    public function execute(PaymentDTO $dto): PaymentResultDTO {
        return DB::transaction(function () use ($dto) {
            // Call Services and Queries here
        });
    }
}
```

### 6.2 Pessimistic Locking (`lockForUpdate`)

Used for sensitive operations (e.g., financial deductions) to prevent race conditions.

**Placement:** Inside a **Query Class** (if the query is the start of the operation) or directly before the update in the Service.

```php
// Inside Query Class
public function getForUpdate(int $contractId): Contract
{
    return Contract::lockForUpdate()->findOrFail($contractId);
}
```

### 6.3 Batch Inserts

Do NOT use `toModelArray()` for batch operations; it is designed for single row structural mapping.

**Rule:** Build the array manually in the Service or a dedicated helper method, then use `createMany()`.

```php
$installmentsData = [];
foreach ($dueDates as $date) {
    $installmentsData[] = [
        'contract_id' => $contract->id,
        'due_date'    => $date,
        'amount'      => $amount,
    ];
}
$contract->installments()->createMany($installmentsData);
```

### 6.4 Model Events (`creating`, `updating`)

**Permitted ONLY:** Purely technical, automatic logic that must happen on every save, completely detached from business flow.
- ✅ Generating UUIDs.
- ✅ Generating Reference Numbers.
- ✅ Generating Slugs.

**Prohibited:**
- ❌ Sending notifications.
- ❌ Recording business Audit Logs.
- ❌ Updating balances or states in other tables.

#### Trait Boot Mechanism (CANONICAL)

When a Trait needs to hook into Model events (like `creating` for reference numbers), it MUST use the official Laravel `boot{TraitName}` naming convention. This ensures automatic discovery and clear intent.

```php
trait HasReferenceNumber
{
    // Laravel automatically calls this when the trait is attached to a model
    protected static function bootHasReferenceNumber(): void
    {
        static::creating(function ($model) {
            // generate ref logic
        });
    }
}
```

---

## 7. Decision Cheat Sheet

When writing any code that interacts with the database:

1. Am I reading data?
   ├─ Simple condition on one table? → Scope in Model (or Trait if grouped).
   ├─ Join, Aggregate, or complex logic? → Query Class (Read-Only).
   └─ Assembling response from multiple sources? → Response Builder.

2. Am I writing data (Create/Update)?
   ├─ Data needs structural transformation to DB columns? → DTO with toModelArray().
   └─ Simple single-field state change? → $model->update() directly in Service.

3. Am I deleting data?
   └─ $model->delete() directly after validation.

4. Do I need a Transaction coordinating multiple steps?
   └─ YES → Write it in the orchestrating Action.

5. Am I creating a Repository?
   └─ YES → ERROR. Use Scopes for reads, Eloquent for writes.

6. Am I passing a raw array from Controller to Service?
   └─ YES → ERROR. Wrap it in a DTO.