# E03 — Persistence Layer

**Status:** Complete — merged into `main` via PR #3  
**Epic:** E03 — Persistence  
**Depends on:** E01 Foundation, E02 Contracts & Public API  
**Target:** PHP 8.5+, Laravel 13.x  
**Package:** `shamimstack/storix`  
**Namespace:** `ShamimStack\\Storix`

## 1. Objective

Implement the durable relational persistence foundation used by later transfer epics. E03 owns schema, Eloquent records, repository boundaries, and validated transfer state transitions. It does not perform storage I/O, upload orchestration, chunk transmission, queue execution, or driver finalization.

## 2. Scope

### In scope

- Four package-owned tables: directories, files, transfers, and chunks.
- Reversible Laravel migrations.
- UUID primary keys generated by the application/model layer.
- Eloquent models with explicit casts and relationships.
- Repository contracts and Eloquent implementations for the four aggregates/records.
- Transfer state enum and transition service.
- Transaction-safe idempotency-key creation and scoped lookup.
- Owner/tenant scope fields without foreign keys to host-application identity tables.
- Unit and feature tests for persistence, relations, uniqueness, state transitions, and migration rollback.

### Out of scope

- Local or remote storage driver implementation (E04+).
- Actual upload/download/chunk byte I/O (E05+).
- Chunk planning, concurrency, resume, retries, and reconciliation workers (E06–E08).
- REST endpoints, share links, transfer event history, and encryption-at-rest.
- Host application's authentication model or authorization policy implementation.
- Redis as a source of truth.

## 3. Confirmed schema decisions

1. **Primary identifiers:** UUID strings for all four tables. Do not mix UUID and ULID in this epic.
2. **Database support:** use Laravel Schema Builder and portable column/index definitions. E03 CI initially validates SQLite through Testbench. MySQL and PostgreSQL compatibility must not be claimed until their integration matrix is added.
3. **Identity references:** `tenant_id` and `owner_id` are nullable `varchar(64)` identifiers with indexes, not foreign keys. This supports UUID, ULID, numeric IDs represented as strings, and host-specific string keys without coupling Storix to host identity tables.
4. **Driver naming:** persist the selected driver name as `driver`; do not add duplicate `disk` and `driver` columns.
5. **Provider references:** `storage_key` is an opaque provider key, not a public URL or credential.
6. **Metadata:** JSON is supplemental and must not replace indexed/query-critical columns.
7. **Time:** persist timestamps using Laravel's normal UTC application convention.
8. **Deletion:** E03 models soft-deletion timestamps for files/directories; physical provider cleanup is a later epic.
9. **Idempotency:** uniqueness is scoped by tenant, owner, and idempotency key. Null keys are allowed; empty strings are invalid.
10. **Transfer completion:** a transfer may enter `completed` only from `verifying`.

## 4. Database schema

### 4.1 `storix_directories`

| Column | Type | Rules |
|---|---|---|
| id | UUID | Primary key |
| tenant_id | varchar(64) nullable | Indexed; host-owned identity |
| owner_id | varchar(64) nullable | Indexed; host-owned identity |
| parent_id | UUID nullable | Self-reference, nullable, restrictive delete |
| name | varchar(255) | Required, non-empty |
| path_key | varchar(1024) | Required normalized internal path |
| scope_hash | char(64) | SHA-256 of tenant, owner, driver, and parent scope |
| name_hash | char(64) | SHA-256 of trimmed, lowercased name |
| driver | varchar(64) | Required |
| status | varchar(24) | active/deleting/deleted |
| created_at / updated_at | timestamps | Required |
| deleted_at | timestamp nullable | Soft delete |

Indexes: unique `(scope_hash, name_hash)`, lookup `(tenant_id, owner_id, driver, parent_id, name)`, `parent_id`, and `status`. The model computes the hashes before save; the database unique constraint prevents duplicate case-insensitive directory names within the same tenant, owner, driver, and parent. Soft-deleted directories reserve their names until restored or physically purged. Do not create a full unique index on the 1024-character path.

### 4.2 `storix_files`

| Column | Type | Rules |
|---|---|---|
| id | UUID | Primary key |
| tenant_id | varchar(64) nullable | Indexed |
| owner_id | varchar(64) nullable | Indexed |
| directory_id | UUID nullable | FK to directories, restrictive delete |
| driver | varchar(64) | Required |
| name | varchar(255) | Required |
| original_name | varchar(255) nullable | Untrusted display metadata |
| extension | varchar(32) nullable | Normalized extension |
| mime_type | varchar(255) nullable | Declared/detected MIME |
| size_bytes | unsigned big integer | Non-negative |
| sha256 | char(64) nullable | Lowercase hex when present |
| storage_key | text nullable | Opaque provider reference |
| status | varchar(24) | pending/processing/available/deleting/deleted/failed |
| visibility | varchar(24) | private/public/limited |
| metadata | JSON nullable | Scalar-safe application metadata |
| created_at / updated_at | timestamps | Required |
| deleted_at | timestamp nullable | Soft delete |

Indexes: `(tenant_id, owner_id, created_at)`, `directory_id`, `(driver, status)`, and `(tenant_id, owner_id, driver, directory_id, sha256, size_bytes)`. Repository queries must always apply the relevant owner/tenant scope; the checksum index is not permission to deduplicate across tenant, owner, driver, or target-directory scopes.

### 4.3 `storix_transfers`

| Column | Type | Rules |
|---|---|---|
| id | UUID | Primary key |
| file_id | UUID nullable | FK to files, restrictive delete |
| tenant_id | varchar(64) nullable | Indexed |
| owner_id | varchar(64) nullable | Indexed |
| direction | varchar(16) | upload/download |
| driver | varchar(64) | Required |
| state | varchar(24) | TransferState enum values |
| source_name | varchar(255) nullable | Display metadata |
| total_bytes | unsigned big integer | Non-negative |
| transferred_bytes | unsigned big integer | 0..total_bytes |
| total_chunks | unsigned integer | Non-negative |
| completed_chunks | unsigned integer | 0..total_chunks |
| average_bytes_per_second | decimal nullable | Non-negative |
| eta_seconds | unsigned big integer nullable | Non-negative |
| idempotency_key | varchar(128) nullable | Non-empty when present |
| idempotency_scope | char(64) nullable | SHA-256 of normalized tenant/owner scope; set when key is present |
| fingerprint | char(64) nullable | SHA-256 hex when present |
| attempts | unsigned integer | Default 0 |
| max_attempts | unsigned integer | Positive |
| last_error_code | varchar(64) nullable | Stable package/provider code |
| last_error_message | text nullable | Sanitized, no secrets |
| started_at / finished_at / expires_at | timestamps nullable | Lifecycle timestamps |
| created_at / updated_at | timestamps | Required |

Indexes: `(tenant_id, owner_id, state)`, `file_id`, `(state, expires_at)`, unique `(idempotency_scope, idempotency_key)`. The scope hash is computed from the nullable tenant/owner pair so SQL NULL uniqueness semantics cannot permit duplicate unscoped keys. The repository must normalize absent identity scope consistently and rely on the unique scope-hash/key constraint as the final arbiter; a preflight SELECT alone is not sufficient.

### 4.4 `storix_chunks`

| Column | Type | Rules |
|---|---|---|
| id | UUID | Primary key |
| transfer_id | UUID | FK to transfers, cascade delete |
| chunk_index | unsigned integer | Zero-based |
| offset_bytes | unsigned big integer | Non-negative |
| size_bytes | unsigned big integer | Positive |
| sha256 | char(64) nullable | SHA-256 hex after verification |
| state | varchar(24) | pending/uploading/verified/failed |
| driver_reference | text nullable | Opaque provider reference |
| attempts | unsigned integer | Default 0 |
| last_error_code | varchar(64) nullable | Sanitized code |
| created_at / updated_at | timestamps | Required |

Unique: `(transfer_id, chunk_index)`. Index: `(transfer_id, state)`. E03 persists chunk records but does not transmit or verify chunk bytes; those behaviors belong to E06.

## 5. Eloquent model requirements

Models:

- `Models/Directory`
- `Models/StoredFile` (avoid PHP's reserved/common `File` naming collision)
- `Models/Transfer`
- `Models/Chunk`

Requirements:

- Use UUID primary keys with non-incrementing string key configuration.
- Generate UUIDs before insert, consistently for all models.
- Define `belongsTo`, `hasMany`, and self-referential directory relationships.
- Cast JSON metadata to arrays, timestamps to immutable/date objects where consistent with Laravel, numeric values to integers, and enum-backed state/direction/visibility to backed enums where implemented.
- Apply soft deletes only to Directory and StoredFile; transfers/chunks remain auditable records and use explicit retention policy later.
- Do not expose provider credentials or signed URLs through model serialization.
- Keep persistence models out of public driver DTOs.

## 6. Repository boundaries

Define repository contracts under `Contracts/Repositories` and Eloquent implementations under `Repositories/Eloquent`.

Minimum operations:

- Directories: create, find by ID within scope, find child by name within scope, list children, save, soft delete.
- Files: create, find by ID within scope, find by scoped fingerprint when available, including driver and target directory, save, soft delete.
- Transfers: create (normalizing idempotency scope for any supplied key), find by ID within scope, find by scoped idempotency key, save non-state attributes only, atomically create-or-return idempotent transfer, list stale/eligible records with bounded pagination.
- Chunks: create, find by transfer/index, list by transfer ordered by index, save, delete by transfer when retention explicitly requests it.

Every public lookup that accepts an owner/tenant scope must apply it in SQL, not fetch globally and filter in PHP. Repository methods return typed models or explicit nullable results. Avoid generic `array<string,mixed>` repositories.

## 7. Transfer state machine

States:

- Non-terminal: `pending`, `preparing`, `transferring`, `paused`, `retry_wait`, `verifying`.
- Terminal: `completed`, `failed`, `cancelled`, `expired`.

Allowed transitions:

| From | To |
|---|---|
| pending | preparing, cancelled, expired, failed |
| preparing | transferring, retry_wait, paused, failed, cancelled, expired |
| transferring | verifying, retry_wait, paused, failed, cancelled |
| retry_wait | preparing, transferring, failed, cancelled, expired |
| paused | preparing, transferring, cancelled, expired |
| verifying | completed, retry_wait, failed |
| completed | none |
| failed | none |
| cancelled | none |
| expired | none |

Rules:

- All state transitions go through one `TransferStateMachine` service; repository `save()` rejects state changes on persisted transfer models.
- Invalid transitions throw a dedicated domain exception; never silently coerce state.
- `completed` requires source state `verifying`.
- Terminal states cannot be reopened; a new transfer is created instead.
- A transition updates state and relevant lifecycle timestamps in one database transaction.
- Concurrent transitions use row locking where supported and re-check the persisted source state after acquiring the lock.
- State machine validation is independent from upload/download execution.

## 8. Transaction and concurrency rules

- Never hold a database transaction or row lock during provider/network I/O.
- Use transactions for multi-row metadata invariants.
- Use database unique constraints as the final arbiter for idempotency and chunk uniqueness.
- Catch and translate only known unique-constraint violations; do not swallow arbitrary database exceptions.
- Use bounded pagination for cleanup/stale queries.
- No repository may bypass scope constraints for convenience.
- Database portability is verified in CI before expanding the support claim.

## 9. Configuration and migration publishing

- Add the Eloquent database dependency required by the models/migrations, constrained to Laravel 13-compatible versions.
- Register migrations from the package service provider using Laravel's package migration loader.
- Publishable migrations are not required for normal installation; package migrations should run through `php artisan migrate`.
- Keep schema names prefixed with `storix_`.
- No destructive migration is included in E03.
- Do not run migrations automatically during package boot.

## 10. Test plan

### Unit

- Every allowed and forbidden state transition.
- Terminal-state immutability.
- UUID generation and enum casts.
- Repository scope query construction where testable.

### Feature / database

- All four migrations load successfully; migration rollback is tested in reverse dependency order.
- Model relationships, UUID/casts, and soft-delete behavior.
- Required columns, defaults, foreign keys, and indexes.
- Scoped directory/file/transfer lookups.
- Duplicate idempotency key reuse in both repository create paths, scope isolation, blank-key rejection, and database uniqueness behavior.
- Unique directory scope/name, transfer idempotency, and transfer/chunk index enforcement, including duplicate-key rejection.
- Chunk ordering, bounded expired-transfer query, and transaction rollback.
- Transaction rollback leaves no partial metadata.
- SQLite CI must pass; no unsupported database is claimed.

## 11. Acceptance criteria

- [x] Four reversible migrations exist and load through the package service provider.
- [x] UUID-backed Eloquent models and relationships are implemented.
- [x] Repository contracts and Eloquent implementations cover the required operations.
- [x] Owner/tenant scoping is enforced in database queries.
- [x] Idempotency and chunk uniqueness are protected by database constraints.
- [x] Transfer state machine enforces the documented transition graph.
- [x] No transaction is held during storage/network I/O.
- [x] Unit and database feature tests cover success and failure paths.
- [x] Composer validation, Pint, and PHPUnit pass in CI.
- [x] Root README, database design, roadmap, and changelog reflect only implemented behavior.

## 12. Explicit non-claims

E03 does not make Storix a usable file storage engine. It only makes metadata and transfer state durable. A usable local storage workflow still requires E04 and E05; chunked/resumable workflows require subsequent epics.
