gilles/system_update
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
system_update/tache1.9.md
T
gilles 0fbca06d3d docs: roadmap tâches 1.9-8 (briefs, gates de validation, designs tâche 2) + plans d'implémentation 
Cartographie complète (liste_taches/coherence_taches), briefs tacheN + gates
validation_tacheN, design tâche 2 (docs/design/tache2/), specs/plans jalon 1-2
et tâche 1.9/2 (Phase 1, Phase 2, SJ-0→3). Validations consignées (1.9 ✅, 2-8 🟡).

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
2026-06-05 19:50:25 +02:00

1336 lines
36 KiB
Markdown
Raw Permalink Blame History

# Consigne de dev — Architecture base de données cible
> **Type** : mission d'**analyse + design architecture BDD** (PAS d'implémentation).
> **Langue** : français.
> **Livrable final attendu** : spec de schéma de données prête à passer en plan d'implémentation.
---
## 0. Contexte
Le projet `system_update` dispose actuellement d'un schéma SQLite minimal :
- `machines`
- `snapshots`
- `executions`
Ce schéma suffit au jalon 1, mais les tâches suivantes ajoutent de nouveaux besoins :
- APT update/analyse, diff avant/après, reboot vérifié ;
- Docker Compose, roots, stacks, pull-check, apply, prune ;
- profils post-install et installateurs externes ;
- sauvegarde de tous les JSON machine ↔ webapp ;
- automatisations planifiées ;
- Hermes/MCP/skills/rapports globaux ;
- optimisation tokens ;
- nettoyage DB/logs ;
- découverte de machines SSH ;
- sécurité credentials et host keys ;
- observabilité backend.
Objectif de cette tâche : concevoir la **meilleure structure BDD cible**, compatible avec le MVP SQLite/Drizzle, mais prévue pour évoluer vers PostgreSQL si le projet grandit.
---
## 1. Analyse du schéma actuel
Schéma actuel :
```text
machines
├─ id
├─ name
├─ hostname
├─ port
├─ os_family
├─ username
├─ enc_password
├─ enc_sudo_password
├─ apt_proxy_mode
├─ apt_proxy_url
├─ status
├─ last_checked_at
└─ created_at
snapshots
├─ id
├─ machine_id
├─ checked_at
├─ status
└─ payload_json
executions
├─ id
├─ machine_id
├─ action
├─ mode
├─ started_at
├─ finished_at
├─ status
├─ result_json
├─ report_path
└─ raw_log_path
```
Points forts :
- simple ;
- compatible jalon 1 ;
- `payload_json` et `result_json` permettent une évolution rapide ;
- séparation snapshot/exécution correcte ;
- suppression cascade par machine.
Limites :
- credentials mélangés à la table machine ;
- pas de table événements/timeline ;
- pas de table jobs/schedules persistants ;
- pas de configuration Docker/post-install par machine ;
- pas de modèle de templates/scripts ;
- pas de demandes d'action Hermes en attente de validation ;
- pas de métriques système/token usage ;
- pas de rétention/purge ;
- pas de host keys SSH ;
- pas de tags/groupes machines ;
- pas de notion de snapshot kind ;
- pas de version de schéma JSON.
Conclusion : le modèle actuel doit être conservé comme base, mais élargi en couches spécialisées.
---
## 2. Principe d'architecture BDD
### Recommandation MVP
Continuer en **SQLite + Drizzle** pour l'application locale/mono-instance :
- simple à déployer ;
- cohérent avec le jalon 1 ;
- suffisant pour quelques dizaines/centaines de machines ;
- WAL déjà activé ;
- backups faciles.
### Prévoir PostgreSQL plus tard
Garder une architecture compatible PostgreSQL :
- IDs text/UUID ;
- dates ISO ou timestamp abstrait côté code ;
- JSON stocké en `text` côté SQLite, futur `jsonb` côté PostgreSQL ;
- tables normalisées pour les recherches fréquentes ;
- payload complet conservé en JSON ;
- index explicites.
### Règle structurante
Chaque donnée métier importante existe sous deux formes :
1. **JSON complet archivé** : vérité canonique, relecture, Hermes, audit.
2. **Colonnes indexées dérivées** : recherche, filtres UI, badges, performances.
Exemple :
```text
snapshots.payload_json ← JSON complet
snapshots.kind/status/checked_at ← filtres rapides
machine_state.apt_updates_count ← affichage tuile rapide
```
---
## 3. Architecture cible — groupes de tables
```text
Core
├─ machines
├─ machine_credentials
├─ machine_host_keys
├─ machine_tags
├─ tags
├─ machine_state
├─ machine_hardware
├─ machine_metrics_latest
├─ app_settings
├─ user_preferences
└─ machine_ui_state
JSON / Historique
├─ snapshots
├─ executions
├─ machine_events
├─ important_messages
├─ reports
├─ raw_artifacts
└─ ssh_terminal_sessions
APT
├─ apt_planned_packages
├─ apt_applied_packages
└─ apt_errors
Docker
├─ docker_settings
├─ docker_compose_roots
├─ docker_compose_stacks
├─ docker_stack_services
└─ docker_image_events
Post-install / scripts
├─ install_profiles
├─ install_recipes
├─ install_recipe_versions
├─ machine_profile_state
└─ script_variables_presets
Jobs / automatisations
├─ jobs
├─ schedules
├─ machine_locks
└─ action_requests
Hermes / MCP
├─ hermes_sessions
├─ hermes_runs
├─ hermes_usage
├─ mcp_audit_log
└─ api_clients
Optimisation / maintenance
├─ system_metrics
├─ cleanup_runs
├─ discovery_scans
└─ discovery_candidates
```
---
## 4. Schéma ASCII global
```text
┌──────────────┐
│ machines │
└──────┬───────┘
│ 1
├──────────────┬───────────────┬───────────────┐
│ │ │ │
▼ ▼ ▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ credentials │ │ host_keys │ │ machine_state│ │ docker_config│
└──────────────┘ └──────────────┘ └──────────────┘ └──────┬───────┘
│
▼
┌──────────────┐
│ compose │
│ roots/stacks │
└──────────────┘
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ snapshots │◄────►│ executions │◄────►│ reports │
└──────┬───────┘ └──────┬───────┘ └──────────────┘
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ apt planned │ │ apt applied │
│ docker state │ │ docker events│
└──────────────┘ └──────────────┘
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ schedules │─────►│ jobs │─────►│ machine_locks│
└──────────────┘ └──────────────┘ └──────────────┘
┌──────────────┐ ┌──────────────┐
│ hermes_runs │─────►│ hermes_usage │
└──────────────┘ └──────────────┘
```
---
## 5. Tables core
### `machines`
Table publique machine, sans secret.
```text
id text primary key
name text not null
hostname text not null
port integer not null default 22
os_family text not null
os_version text
os_codename text
arch text
machine_kind text -- physical | vm | proxmox_host | lxc | raspberry_pi | workstation | unknown
virtualization text -- none | qemu | kvm | lxc | docker | vmware | virtualbox | ...
hardware_profile text -- generic_vm | baremetal_server | raspberry_pi | gpu_server | proxmox_host | ...
username text not null
status text not null
created_at text not null
updated_at text not null
last_seen_at text
deleted_at text null
```
Évolutions :
- `deleted_at` permet une suppression douce future ;
- `hostname` peut être IP ou DNS ;
- `last_seen_at` utile pour statut.
- `os_family` et `machine_kind` sont deux dimensions différentes : Debian VM, Debian physique, Proxmox hôte, Raspberry Pi OS, etc.
- ces champs peuvent être choisis manuellement à l'ajout puis corrigés par `machine_probe`.
### `machine_credentials`
Secrets chiffrés séparés de `machines`.
```text
machine_id text primary key references machines(id)
auth_method text not null -- password | ssh_key
enc_password text
enc_sudo_password text
enc_private_key text
enc_key_passphrase text
sudo_mode text not null -- same_as_ssh | separate | none
created_at text not null
updated_at text not null
last_test_at text
status text -- ok | error | unknown
```
Règles :
- jamais exposée via API publique ;
- audit obligatoire quand lue/déchiffrée ;
- migration possible depuis `machines.enc_password`.
### `machine_host_keys`
Validation host key SSH.
```text
id text primary key
machine_id text references machines(id)
hostname text not null
port integer not null
key_type text
fingerprint_sha256 text not null
public_key text
status text not null -- approved | changed | rejected | unknown
first_seen_at text not null
last_seen_at text not null
```
### `machine_state`
État courant dérivé pour tuiles et dashboard.
```text
machine_id text primary key
status text not null
apt_status text
apt_updates_count integer default 0
apt_reboot_required integer default 0
apt_last_analyze_at text
docker_status text
docker_installed integer default 0
docker_stacks_count integer default 0
docker_updates_count integer default 0
docker_prune_available integer default 0
post_install_status text
metrics_last_collected_at text
cpu_load1 real
memory_used_percent real
root_used_percent real
disk_warnings_count integer default 0
hardware_warnings_count integer default 0
running_job_id text
last_error_kind text
last_error_message text
updated_at text not null
```
Objectif :
- éviter de parser les derniers snapshots à chaque affichage ;
- les tuiles lisent cette table.
### `tags` et `machine_tags`
Pour groupes, schedules et filtres.
```text
tags(id, name, color, created_at)
machine_tags(machine_id, tag_id)
```
Exemples :
- `debian`
- `proxmox`
- `docker`
- `domotique`
- `prod`
- `lab`
### `machine_hardware`
Dernier inventaire matériel détecté.
```text
machine_id text primary key references machines(id)
probe_snapshot_id text references snapshots(id)
cpu_model text
cpu_cores integer
memory_bytes integer
gpus_json text -- vendor/model/driver/recommendations
disks_json text -- name/type/size/rotational/smart
network_json text
firmware_json text
driver_json text
warnings_json text
updated_at text not null
```
### `machine_metrics_latest`
Dernières métriques simples par machine.
```text
machine_id text primary key references machines(id)
snapshot_id text references snapshots(id)
collected_at text not null
cpu_load1 real
cpu_load5 real
cpu_cores integer
memory_total_bytes integer
memory_used_bytes integer
memory_available_bytes integer
memory_used_percent real
filesystems_json text not null
root_used_percent real
warnings_json text
```
Ces métriques ne remplacent pas un vrai outil de monitoring ; elles servent à l'affichage rapide, aux alertes simples et aux rapports Hermes.
---
## 6. Snapshots, exécutions, événements
### `snapshots`
Élargir la table existante.
```text
id text primary key
machine_id text not null references machines(id)
kind text not null -- apt_update_analyze | docker_scan | reboot_check | ...
schema_version integer not null default 1
created_at text not null
status text not null -- ok | warning | error
payload_json text not null
important_json text -- version réduite pour Hermes/UI
raw_log_path text
raw_artifact_id text
source_job_id text
```
Index :
```text
(machine_id, kind, created_at desc)
(kind, status, created_at desc)
```
### `executions`
Élargir la table existante.
```text
id text primary key
machine_id text not null references machines(id)
action text not null
mode text not null -- manual | scheduled | hermes_requested
schema_version integer not null default 1
started_at text not null
finished_at text
status text not null -- running | ok | warning | error | cancelled
request_id text
job_id text
result_json text
important_json text
raw_log_path text
raw_artifact_id text
report_id text
exit_code integer
error_kind text
error_message text
```
Index :
```text
(machine_id, started_at desc)
(action, status, started_at desc)
(job_id)
(request_id)
```
### `machine_events`
Timeline/audit unifiée.
```text
id text primary key
machine_id text references machines(id)
event_type text not null
severity text not null -- info | warning | error
created_at text not null
actor_type text -- user | system | schedule | hermes
actor_id text
snapshot_id text
execution_id text
job_id text
message text
payload_json text
```
### `important_messages`
Messages extraits des logs et JSON pour analyse UI/Hermes.
```text
id text primary key
machine_id text references machines(id)
source text not null -- apt | docker | post_install | ssh | system
category text not null -- error | warning | future_major_change | deprecation | security_notice | repository_notice
severity text not null -- info | warning | error
package_name text
component text
message text not null -- résumé nettoyé sans secret
raw_line_ref text -- artifact_id#line
snapshot_id text references snapshots(id)
execution_id text references executions(id)
first_seen_at text not null
last_seen_at text not null
acknowledged integer default 0
acknowledged_at text
acknowledged_by text
payload_json text
```
Objectif :
- conserver les warnings importants même si le log brut est purgé ;
- permettre à Hermes de rechercher les évolutions majeures futures, notices sécurité, dépôts obsolètes ou changements de politique paquet ;
- alimenter les badges et alertes des tuiles machine ;
- permettre un acquittement utilisateur sans supprimer l'historique.
### `reports`
```text
id text primary key
machine_id text references machines(id)
execution_id text
kind text not null -- machine | global | cleanup | hermes
title text not null
path text not null
created_at text not null
pinned integer default 0
summary_json text
```
### `raw_artifacts`
Pour logs bruts, previews, exports.
```text
id text primary key
machine_id text
kind text not null -- raw_log | rendered_template | export | screenshot
path text not null
bytes integer
sha256 text
created_at text not null
expires_at text
pinned integer default 0
redacted integer default 1
retention_policy text -- default | failed | pinned | short
deleted_at text
delete_reason text
metadata_json text
```
### `ssh_terminal_sessions`
Sessions de vrai terminal SSH interactif ouvertes depuis la webapp.
```text
id text primary key
machine_id text not null references machines(id)
username text
opened_by text
opened_at text not null
closed_at text
status text not null -- open | closed | error | killed
interactive integer default 1
recording_enabled integer default 0
raw_artifact_id text
last_error text
metadata_json text
```
---
## 6.1 Préférences frontend et paramètres UI
Les paramètres frontend qui doivent survivre au navigateur sont stockés en BDD. Le `localStorage` ne sert qu'à accélérer l'affichage initial ou garder un fallback local.
### `app_settings`
Paramètres globaux de l'application.
```text
key text primary key
value_json text not null
updated_at text not null
updated_by text
```
Exemples :
- thème par défaut ;
- densité UI ;
- zoom global ;
- largeur par défaut volet Hermes/terminal ;
- terminal SSH interactif activé/désactivé ;
- rétention logs ;
- CIDR autorisés pour découverte réseau.
### `user_preferences`
Préférences par utilisateur/session opérateur, si authentification ajoutée.
```text
id text primary key
user_id text
key text not null
value_json text not null
updated_at text not null
```
Exemples :
- thème choisi ;
- zoom ;
- largeur des volets ;
- taille/densité des tuiles ;
- mode compact/confort ;
- onglet par défaut.
### `machine_ui_state`
État UI par machine, non critique mais utile.
```text
machine_id text primary key references machines(id)
sections_open_json text -- docker/post-install/logs
tile_mode text -- compact | expanded
last_active_tab text
updated_at text not null
```
Règle :
- aucune décision métier ne dépend de cette table ;
- elle sert uniquement à retrouver l'ergonomie choisie.
## 7. APT
Les détails APT peuvent rester dans `payload_json`, mais certaines tables dérivées accélèrent recherche/dédup/Hermes.
### `apt_planned_packages`
Paquets prévus avant upgrade.
```text
id text primary key
snapshot_id text not null references snapshots(id)
machine_id text not null
mode text not null -- upgrade | dist_upgrade
name text not null
arch text
current_version text
target_version text
origin text
operation text not null -- upgrade | install | remove | hold
dedup_key text not null
```
### `apt_applied_packages`
Diff réel après exécution.
```text
id text primary key
execution_id text not null references executions(id)
machine_id text not null
name text not null
arch text
from_version text
to_version text
operation text not null -- upgraded | installed | removed | unchanged
origin text
dedup_key text
```
### `apt_errors`
```text
id text primary key
snapshot_id text
execution_id text
machine_id text not null
kind text not null -- apt_lock_busy | dpkg_interrupted | ...
severity text not null
message text not null
important_lines_json text
remediation text
created_at text not null
```
---
## 8. Docker Compose
### `docker_settings`
Configuration Docker par machine.
```text
machine_id text primary key references machines(id)
enabled integer default 0
scan_depth integer default 4
prune_mode text default 'safe' -- safe | aggressive
last_scan_at text
last_pull_check_at text
updated_at text not null
```
### `docker_compose_roots`
```text
id text primary key
machine_id text not null references machines(id)
path text not null
enabled integer default 1
scan_depth integer
created_at text not null
updated_at text not null
```
### `docker_compose_stacks`
```text
id text primary key
machine_id text not null
name text not null
working_dir text not null
compose_files_json text not null
project_name text
env_file text
status text not null -- candidate | enabled | ignored | error
detected_by text -- root_scan | label | manual
last_scan_at text
last_update_at text
created_at text not null
updated_at text not null
```
### `docker_stack_services`
Dernier état service/image par stack.
```text
id text primary key
stack_id text not null references docker_compose_stacks(id)
service_name text not null
image_ref text
current_image_id text
current_digest text
candidate_image_id text
candidate_digest text
version_label text
status text -- up_to_date | updates_available | error
updated_at text not null
```
### `docker_image_events`
Historique pull/apply/prune.
```text
id text primary key
execution_id text references executions(id)
machine_id text not null
stack_id text
service_name text
image_ref text
from_image_id text
to_image_id text
from_digest text
to_digest text
operation text -- pulled | recreated | pruned
bytes_reclaimed integer
created_at text not null
```
---
## 9. Post-install, scripts, templates
### `install_profiles`
Catalogue de profils.
```text
id text primary key
label text not null
category text not null
risk text
enabled integer default 1
manifest_json text not null
created_at text not null
updated_at text not null
```
### `install_recipes`
Installateurs externes ou scripts custom.
```text
id text primary key
profile_id text references install_profiles(id)
label text not null
source_type text not null -- apt_packages | official_external | custom
risk text
current_version_id text
enabled integer default 1
created_at text not null
updated_at text not null
```
### `install_recipe_versions`
Versionner les scripts/manifests.
```text
id text primary key
recipe_id text not null references install_recipes(id)
version integer not null
template_path text not null
manifest_json text not null
sha256 text
created_at text not null
created_by text
```
### `machine_profile_state`
État des profils par machine.
```text
id text primary key
machine_id text not null references machines(id)
profile_id text not null references install_profiles(id)
status text not null -- not_run | ok | warning | error | pending
last_execution_id text
last_run_at text
variables_json text -- non sensible seulement
```
### `script_variables_presets`
Préréglages réutilisables.
```text
id text primary key
scope text not null -- global | machine | profile
machine_id text
profile_id text
name text not null
variables_json text not null
created_at text not null
updated_at text not null
```
---
## 10. Jobs, schedules, demandes d'action
### `jobs`
```text
id text primary key
kind text not null
machine_id text
schedule_id text
request_id text
status text not null -- queued | running | ok | warning | error | cancelled
priority integer default 0
created_at text not null
started_at text
finished_at text
attempt integer default 0
max_attempts integer default 1
payload_json text
result_json text
error_kind text
error_message text
```
### `schedules`
```text
id text primary key
name text not null
enabled integer default 1
cron text not null
timezone text not null
scope_json text not null
actions_json text not null
concurrency integer default 1
notify_on_json text
last_run_at text
next_run_at text
created_at text not null
updated_at text not null
```
### `machine_locks`
```text
machine_id text primary key references machines(id)
job_id text not null
lock_kind text not null -- apt | docker | post_install | reboot | exclusive
created_at text not null
expires_at text
```
### `action_requests`
Demandes venant UI ou Hermes, avec validation.
```text
id text primary key
machine_id text references machines(id)
requested_by_type text not null -- user | hermes | schedule
requested_by_id text
action text not null
risk text
status text not null -- pending | approved | rejected | executed | expired
summary text
payload_json text
created_at text not null
approved_at text
approved_by text
execution_id text
expires_at text
```
---
## 11. Hermes, MCP, token usage
### `hermes_sessions`
```text
id text primary key
source text not null -- webapp | tui | telegram | discord | ...
session_key text
created_at text not null
last_message_at text
status text
```
### `hermes_runs`
```text
id text primary key
session_id text references hermes_sessions(id)
prompt_kind text
status text
started_at text not null
finished_at text
request_json text -- réduit, sans secret
response_json text
report_id text
```
### `hermes_usage`
```text
id text primary key
run_id text references hermes_runs(id)
provider text
model text
prompt_tokens integer
cached_tokens integer
completion_tokens integer
total_tokens integer
raw_bytes integer
reduced_bytes integer
reduction_ratio real
created_at text not null
```
### `mcp_audit_log`
```text
id text primary key
server_name text not null
tool_name text not null
actor text
request_json text
response_summary_json text
status text
created_at text not null
```
### `api_clients`
Clients API externes : app locale Rust/GNOME, scripts internes, clients admin.
```text
id text primary key
label text not null
client_kind text not null -- webapp | rust_gnome_app | script | mcp | other
token_hash text not null
scopes_json text not null -- read | operate | admin | debug_logs
status text not null -- active | revoked | expired
created_at text not null
last_seen_at text
expires_at text
revoked_at text
metadata_json text
```
Règles :
- ne jamais stocker le token en clair ;
- audit des actions via `machine_events` ou table dédiée si nécessaire ;
- scopes minimaux ;
- révocation depuis les paramètres sécurité.
---
## 12. Optimisation, maintenance, découverte
### `system_metrics`
```text
id text primary key
created_at text not null
cpu_percent real
memory_rss_bytes integer
memory_heap_bytes integer
db_bytes integer
wal_bytes integer
jobs_running integer
machines_count integer
payload_json text
```
### `cleanup_runs`
```text
id text primary key
mode text not null -- dry_run | apply
status text not null
started_at text not null
finished_at text
deleted_counts_json text
bytes_reclaimed integer
result_json text
```
### `discovery_scans`
```text
id text primary key
cidr text not null
ports_json text not null
method text not null -- nmap | tcp
status text not null
started_at text not null
finished_at text
result_json text
```
### `discovery_candidates`
```text
id text primary key
scan_id text not null references discovery_scans(id)
host text not null
port integer not null
service text
host_key_fingerprint text
reverse_dns text
already_known integer default 0
created_at text not null
```
---
## 13. Index recommandés
```text
machines(hostname, port)
machines(status)
machines(os_family, machine_kind)
machines(hardware_profile)
snapshots(machine_id, kind, created_at desc)
snapshots(kind, status, created_at desc)
executions(machine_id, started_at desc)
executions(action, status, started_at desc)
executions(job_id)
machine_events(machine_id, created_at desc)
machine_events(event_type, created_at desc)
machine_hardware(updated_at desc)
machine_metrics_latest(collected_at desc)
machine_metrics_latest(root_used_percent)
apt_planned_packages(dedup_key)
apt_planned_packages(machine_id, name)
apt_applied_packages(dedup_key)
apt_applied_packages(execution_id)
docker_compose_stacks(machine_id, status)
docker_stack_services(stack_id, status)
jobs(status, created_at)
jobs(machine_id, status)
schedules(enabled, next_run_at)
action_requests(status, created_at)
reports(machine_id, created_at desc)
reports(kind, created_at desc)
important_messages(machine_id, severity, last_seen_at desc)
important_messages(category, acknowledged, last_seen_at desc)
important_messages(package_name)
raw_artifacts(machine_id, created_at desc)
raw_artifacts(expires_at)
ssh_terminal_sessions(machine_id, opened_at desc)
ssh_terminal_sessions(status)
hermes_runs(session_id, started_at desc)
hermes_usage(run_id)
api_clients(status, client_kind)
```
---
## 14. Migration progressive recommandée
Ne pas tout implémenter d'un coup.
### Phase 1 — Séparer et fiabiliser le socle
- ajouter `machine_state` ;
- ajouter `machine_kind/virtualization/hardware_profile` dans `machines` ;
- ajouter `machine_hardware` ;
- ajouter `machine_metrics_latest` ;
- ajouter `machine_events` ;
- ajouter `important_messages` ;
- ajouter `reports` ;
- ajouter `raw_artifacts` ;
- ajouter `kind/schema_version/important_json` à `snapshots` ;
- ajouter `schema_version/important_json/error_kind/error_message` à `executions`.
### Phase 1.5 — Préférences frontend
- créer `app_settings` ;
- créer `user_preferences` ;
- créer `machine_ui_state` ;
- migrer le thème actuel depuis une logique `localStorage` vers une préférence persistée côté backend, avec fallback local.
### Phase 2 — Sécurité credentials
- créer `machine_credentials` ;
- migrer `enc_password`, `enc_sudo_password` ;
- créer `machine_host_keys` ;
- ajouter audit événements secrets.
### Phase 3 — APT complet
- créer `apt_planned_packages` ;
- créer `apt_applied_packages` ;
- créer `apt_errors`.
### Phase 4 — Docker
- créer `docker_settings` ;
- créer `docker_compose_roots` ;
- créer `docker_compose_stacks` ;
- créer `docker_stack_services` ;
- créer `docker_image_events`.
### Phase 5 — Post-install/scripts
- créer `install_profiles` ;
- créer `install_recipes` ;
- créer `install_recipe_versions` ;
- créer `machine_profile_state` ;
- créer `script_variables_presets`.
### Phase 6 — Automatisations
- créer `jobs` ;
- créer `schedules` ;
- créer `machine_locks` ;
- créer `action_requests`.
### Phase 7 — Hermes/optimisation
- créer `hermes_sessions` ;
- créer `hermes_runs` ;
- créer `hermes_usage` ;
- créer `mcp_audit_log` ;
- créer `api_clients`.
### Phase 8 — Maintenance/découverte
- créer `system_metrics` ;
- créer `cleanup_runs` ;
- créer `discovery_scans` ;
- créer `discovery_candidates`.
### Phase 9 — Terminal SSH interactif
- créer `ssh_terminal_sessions` ;
- définir activation globale dans `app_settings` ;
- journaliser ouverture/fermeture ;
- décider si l'enregistrement de session est désactivé, optionnel ou obligatoire selon profil de sécurité.
---
## 15. Recommandations importantes
### Garder les JSON complets
Même avec tables dérivées, conserver les payloads JSON complets :
- audit ;
- relecture ;
- compatibilité Hermes ;
- évolution future ;
- debug.
### Versionner les JSON
Ajouter `schema_version` sur snapshots/executions.
### Ne jamais stocker de secrets dans JSON métier
Secrets seulement dans `machine_credentials`, chiffrés.
### Dériver `machine_state`
Le dashboard ne doit pas recalculer les compteurs depuis l'historique à chaque requête.
### Prévoir rétention
Les tables historiques peuvent grossir :
- snapshots ;
- executions ;
- events ;
- raw_artifacts ;
- hermes_usage.
Tâche 7 définit la purge.
### Préparer PostgreSQL
Si migration PostgreSQL future :
- `payload_json` → `jsonb` ;
- `text ISO` → `timestamptz` ;
- index GIN sur JSON si besoin ;
- jobs persistants avec `pg-boss`.
---
## 16. Livrables attendus
À produire sous `docs/` :
1. Schéma BDD détaillé.
2. Diagramme relationnel.
3. Plan de migration depuis le schéma actuel.
4. Priorisation par phases.
5. Stratégie SQLite maintenant / PostgreSQL futur.
6. Liste des index.
7. Règles de rétention.
8. Règles sécurité secrets.
9. Contrats JSON versionnés.
---
## 17. Définition de terminé
- Le schéma couvre les tâches 2 à 8.
- Le schéma reste compatible MVP SQLite.
- Les évolutions futures sont prévues.
- Les secrets sont isolés.
- Les JSON complets restent archivés.
- Les tuiles machine peuvent lire un état courant rapide.
- Les automatisations et Hermes ont leurs tables dédiées.
- Aucun code de production n'est livré pendant cette mission de design.
---
## 18. Technos à utiliser — checklist
- [ ] SQLite au MVP, avec WAL activé.
- [ ] Drizzle ORM pour schéma TypeScript et migrations.
- [ ] JSON stocké en `text` côté SQLite, compatible futur `jsonb` PostgreSQL.
- [ ] Index explicites sur colonnes de recherche/fréquence.
- [ ] Volumes persistants Docker Compose pour DB, WAL, logs, reports, artifacts.
- [ ] Chiffrement applicatif des secrets avant stockage.
- [ ] Table `api_clients` pour tokens externes hashés/révocables.
- [ ] Plan de migration progressif, phase par phase.
## 19. URLs utiles
- SQLite WAL : https://www.sqlite.org/wal.html
- SQLite `PRAGMA optimize` : https://www.sqlite.org/pragma.html#pragma_optimize
- SQLite VACUUM : https://www.sqlite.org/lang_vacuum.html
- Drizzle ORM SQLite : https://orm.drizzle.team/docs/get-started-sqlite
- Drizzle migrations : https://orm.drizzle.team/docs/migrations
- PostgreSQL JSON types : https://www.postgresql.org/docs/current/datatype-json.html
- Docker volumes : https://docs.docker.com/engine/storage/volumes/
- Docker Compose volumes : https://docs.docker.com/reference/compose-file/volumes/
## 20. Liens parent/enfant avec les autres tâches
- Parent direct : aucune, cette tâche structure la donnée cible.
- Enfants directs :
- `tache2.md` : contrats JSON/templates à stocker.
- `tache3.md` : préférences UI, état tuiles.
- `tache4.md` : profils/scripts/variables.
- `tache5.md` : API/jobs/historique.
- `tache6.md` : Hermes/MCP/audit.
- `tache7.md` : rétention, métriques, nettoyage.
- `tache8.md` : `api_clients` et compatibilité app locale.
- Validation : `validation_tache1.9.md`.
Reference in New Issue View Git Blame Copy Permalink