# Database Engineering Standard

## 1. Core Principle

Database design must prioritize:

- Data integrity
- Consistency
- Referential integrity
- Query performance
- Maintainability
- Safe schema evolution

Never design database tables only around the current UI.

---

## 2. Migration First

Database changes must be represented through Laravel migrations.

Do not manually modify production schema.

Every schema change must have:

- Migration
- Appropriate rollback strategy
- Related model updates
- Tests when behavior changes

---

## 3. Naming

Use predictable names.

Tables:

```text
users
employees
projects
project_services
contracts
contract_members
```

Foreign keys:

```text
user_id
project_id
contract_id
```

Pivot tables:

```text
role_user
permission_role
contract_member
```

---

## 4. Primary Keys

Use the project's established primary-key strategy consistently.

Do not introduce a different ID strategy into an existing module without architectural justification.

---

## 5. Foreign Keys

Use foreign keys where relational integrity is required.

Prefer:

```php
$table->foreignId('project_id')
    ->constrained()
    ->cascadeOnDelete();
```

Delete behavior must be chosen intentionally.

Do not blindly use cascade delete.

---

## 6. Relationships

Relationships must be defined clearly in models.

Common relationships:

```text
hasOne
hasMany
belongsTo
belongsToMany
morphOne
morphMany
```

Avoid unnecessary polymorphic relationships.

Use polymorphism only when the domain genuinely requires multiple entity types.

---

## 7. Indexing

Index columns frequently used for:

- WHERE
- JOIN
- ORDER BY
- UNIQUE constraints
- Foreign keys
- Search/filter operations

Do not create indexes blindly.

Consider composite indexes for common query combinations.

---

## 8. Unique Constraints

Business uniqueness should be enforced at database level when appropriate.

Example:

```text
contract_number
employee_code
service_code
```

Do not rely only on application-level validation for critical uniqueness.

---

## 9. Transactions

Multi-step writes must use transactions.

Examples:

- Creating contract + members
- Approval workflows
- Payroll processing
- Permission changes
- Status transitions
- Financial operations

Transactions normally belong in the Service layer.

---

## 10. Soft Deletes

Use soft deletes only when business requirements need historical preservation.

Do not add `deleted_at` to every table automatically.

---

## 11. Schema Evolution

Before changing an existing column:

- Search all usages
- Search API consumers
- Search queries
- Search reports
- Search jobs
- Search imports/exports
- Search tests

Never assume a column is unused.

---

## 12. Data Migration

For data migrations:

- Preserve existing data
- Make migration idempotent where possible
- Avoid destructive transformations
- Log important transformations
- Validate before and after migration

Never perform bulk destructive updates without explicit approval.

---

## 13. Query Responsibility

Controllers must not query the database.

Repositories handle database access.

Services handle business decisions.

---

## 14. Performance

Avoid:

- N+1 queries
- Unnecessary relationship loading
- `SELECT *` when large datasets are involved
- Unbounded list queries
- Repeated identical queries

Use:

- eager loading
- pagination
- proper indexes
- caching where justified
- query optimization

---

## 15. Final Database Checklist

Before completing database work:

- [ ] Migration created
- [ ] Rollback considered
- [ ] Foreign keys reviewed
- [ ] Indexes reviewed
- [ ] Unique constraints reviewed
- [ ] Relationships updated
- [ ] Transaction requirement reviewed
- [ ] Existing usages checked
- [ ] Tests added/updated
- [ ] Data-loss risk reviewed