# Database Implementation Guide

## Tables

### admins
Administrator accounts. Passwords are stored with `password_hash()` and never as plaintext.

### clients
The approval registry.

Important status lifecycle:

`pending → approved → paid → active → completed`

Administrative exceptions:

`revoked`, `expired`

### invitations
Stores only a SHA-256 hash of the random invitation token. The raw token is never stored.

### services
The authoritative service catalogue and prices.

### orders
One purchase attempt per service selection. `reference` is unique.

### payments
Payment-provider records. The raw provider response is retained for audit/debugging.

### appointments
Client scheduling records.

### documents
Metadata for files stored outside the public web root.

### messages
Private client/admin communications.

### audit_logs
Security and business audit trail.

## Import

Run:

`mysql -u USER -p < database/schema.sql`

Then configure `.env`.

## Create admin

Run:

`php scripts/create_admin.php admin@example.com 'A-strong-password-of-12-or-more-chars' 'Daphne Admin'`

## Production database rules

- Create a dedicated DB user.
- Do not use MySQL root from the application.
- Grant only required privileges.
- Enable automated backups.
- Encrypt backups at rest.
- Test restore procedures.
- Keep production credentials outside Git.
- Use TLS for remote database connections where the hosting architecture requires it.

## Invitation algorithm

1. Create client.
2. Generate 32 random bytes.
3. Base64url encode.
4. Hash token with SHA-256.
5. Store hash + expiry.
6. Send raw token only to the approved email.
7. On use, hash supplied token and find the record.
8. Reject expired/used tokens.
9. Mark `used_at`.
10. Activate the client.
11. Never put email/name in the token URL.

## Document storage

Store private files outside `public/`, e.g.:

`/home/account/private-storage/documents/`

A download controller should:

1. require authenticated client/admin
2. load document by ID
3. confirm ownership/authorization
4. send safe content headers
5. stream the file
6. never expose the filesystem path to the client