# ALZAR FZE — Database Security & phpMyAdmin Guide (for IT)

Reconciles the IT requirements (MySQL, phpMyAdmin, one central secured connection
file) with the business goals (best-in-class SEO + content management, CRM as the
single place staff manage the site). All self-hosted on your own host.

---

## Architecture at a glance

```
   Employees ──login──►  CRM  ──writes content──►┐
                                                 │
                                        ┌────────▼────────┐
   You (owner) ──approve──► CRM ───────►│  ONE MySQL DB   │◄─── phpMyAdmin (you view/manage)
                                        │  alzar_website  │
                                        └────────▲────────┘
                                                 │
   Public visitors ──────────────► Website ──reads ONLY published content
```

- **One shared MySQL database** (`alzar_website`), on your host.
- The **CRM writes** content and holds the approval workflow.
- The **website reads** only rows with `status='published'`.
- **phpMyAdmin** manages the database directly — independent of the site's language.

---

## The central connection file (as required)

**`server/config/database.ts`** is the single point of DB access. Every other file
connects through it — no file reads credentials on its own.

**Security model** (why credentials are in `.env`, not hard-coded in the file):
- The connection file reads DB user/password from environment variables loaded from
  a **`.env`** file.
- `.env` must sit **outside the public web root** and be **blocked from web access**.
  This means even if the website is attacked, the DB password can't be downloaded.
- Only host-level admins (you) can read `.env` — exactly the "only we can access it"
  requirement, done the safe way.

**Protect `.env` at the web-server level:**

Apache (`.htaccess` in web root):
```apache
<Files ".env">
  Require all denied
</Files>
```

Nginx (server block):
```nginx
location ~ /\.env { deny all; return 404; }
```

Also confirm `.env` is git-ignored (it is) so it never lands in a repo or backup.

---

## Setup on your host (order of operations)

1. **Create the database + tables** — import `database/alzar_mysql_schema.sql` via
   phpMyAdmin (Import → choose file → Go), or CLI:
   `mysql -u root -p < database/alzar_mysql_schema.sql`
   This creates `alzar_website` with utf8mb4 (Arabic-safe) and all four tables.

2. **Create a dedicated MySQL user** for the app (don't use root):
   ```sql
   CREATE USER 'alzar_app'@'localhost' IDENTIFIED BY 'a-strong-password';
   GRANT SELECT, INSERT, UPDATE, DELETE ON alzar_website.* TO 'alzar_app'@'localhost';
   FLUSH PRIVILEGES;
   ```
   (Grant only what the app needs — no DROP/ALTER in normal operation.)

3. **Fill `.env`** (copy from `.env.example`): `DB_HOST/DB_PORT/DB_USER/DB_PASSWORD/
   DB_NAME`, a strong `JWT_SECRET` (`openssl rand -hex 32`), and optional SMTP.

4. **Seed the owner admin login** (hashes the password properly — don't insert admin
   passwords by hand in SQL):
   `pnpm tsx scripts/create-admin.ts you@alzarfze.com "STRONG_PASSWORD" "Your Name"`

5. **Build & run**: `pnpm install && pnpm build && pnpm start` (see DEPLOY.md for
   Docker, Nginx reverse proxy, systemd, and backups).

---

## Viewing data in phpMyAdmin

- All tables appear under the `alzar_website` database.
- `content_items` — every article/product/principal/job. The `status` column is the
  approval gate; the website shows only `published`.
- `content_audit` — full history of who changed what.
- `contact_submissions` — website enquiries.
- `users` — staff logins (passwords are argon2 **hashes**, never plaintext).

You can read, filter, export, and back up everything here. Avoid hand-editing password
hashes; use the seed script for account changes.

---

## The CRM side (separate project — coordination note)

The website (this repo) is the **read + render + approval-gate-enforcement** half.
The **content-creation panel and approval queue** live in the **CRM project** (its own
repo/chat, per the workstream-separation rule). Both connect to this same MySQL DB via
their own copy of the central connection file.

`CMS_BUILD_BRIEF.md` specifies exactly the tables/columns and the workflow the CRM side
must implement so the two halves interlock:
- CRM writes to `content_items` (status starts `draft` → `pending_review`).
- Owner approves in the CRM → status `approved` → `published`; website then shows it.
- Every transition writes a `content_audit` row.

---

## Verified in prep
- `pnpm build` passes; `tsc --noEmit` clean (0 errors); no PostgreSQL remnants.
- `database/alzar_mysql_schema.sql` validated as real MySQL (sqlglot, mysql dialect).
- utf8mb4 throughout for correct Arabic storage.