Annexe A · Base de données

create type token_status as enum ('submitted', 'analyzed', 'validated', 'rejected', 'candidate', 'cto_live', 'sending', 'revived', 'dead_again');
create type pons_version as enum ('v1', 'v2');
create type token_phase as enum ('curve', 'graduated');
create type social_type as enum ('x', 'telegram', 'website', 'docs', 'whitepaper', 'github', 'dex', 'pons');
create type social_status as enum ('active', 'silent', 'dead', 'unknown');
create type cycle_status as enum ('draft', 'published', 'voting', 'closed', 'cancelled');
create type operation_status as enum ('pending', 'running', 'paused', 'done', 'failed', 'stopped');
create type step_status as enum ('waiting', 'queued', 'running', 'awaiting_validation', 'done', 'failed', 'skipped');
create type agent_name as enum ('scanner', 'designer', 'webdev', 'cm', 'tg_bot', 'builder');
create type validation_status as enum ('pending', 'approved', 'rejected', 'expired');
create type slice_status as enum ('planned', 'executing', 'done', 'skipped', 'failed');
create type buyback_target as enum ('cto_token', 'vivra');
create type ledger_type as enum ('fees_in', 'cto_fees_in', 'split', 'buyback_slice', 'vivra_burn', 'ai_spend', 'refill');
create type cto_request_status as enum ('draft', 'submitted', 'accepted', 'refused', 'recipient_pending', 'recipient_active');
create type verdict as enum ('revived', 'dead_again');

create table tokens (
  address        text primary key,
  ticker         text not null,
  name           text not null,
  pons_version   pons_version not null,
  launched_at    timestamptz not null,
  launch_block   bigint not null,
  creator        text not null,
  fee_recipient  text not null,
  creator_tax_bps int not null default 0,
  pair_asset     text not null,              -- 'ETH' ou adresse
  phase          token_phase not null,
  pool_id        text,                       -- id du pool v4 après graduation
  status         token_status not null default 'submitted',
  submitted_by   text,                       -- wallet de l'équipe qui a soumis l'adresse
  submitted_links jsonb not null default '{}', -- X, Telegram, docs, GitHub fournis à la soumission
  analyzed_at    timestamptz,
  review_reason  text,                       -- motif de validation hors règles, ou de rejet
  tracking       text not null default 'none', -- 'none' | 'candidate' | 'live' | 'hourly' | 'fees_daily'
  avatar_url     text,                       -- photo de profil du projet, copiée depuis son X
  banner_url     text,                       -- bannière du projet, copiée depuis son X
  category       text,                       -- ex. 'Yield vaults', proposé par le Scanner
  ath_price_usd  numeric(30,12),
  ath_market_cap_usd numeric(20,6),
  ath_at         timestamptz,
  died_at        timestamptz,                -- premier instantané Dead
  is_tech        boolean,                    -- null = pas encore revu ; proposé par le Scanner, confirmé par l'équipe
  updated_at     timestamptz not null default now(),
  created_at     timestamptz not null default now()
);
create index on tokens (status);
create index on tokens (lower(ticker));

create table token_snapshots (
  id             bigserial primary key,
  token          text not null references tokens(address),
  taken_at       timestamptz not null,
  price_usd      numeric(30,12),
  market_cap_usd numeric(20,6),
  liquidity_usd  numeric(20,6),
  volume_24h_usd numeric(20,6),
  volume_7d_usd  numeric(20,6),
  holders        int,                        -- solde > poussière (paramètre)
  top10_pct      numeric(6,3),
  dev_bag_pct    numeric(6,3),
  curve_progress_pct numeric(6,3),          -- null si gradué
  unique (token, taken_at)
);
create index on token_snapshots (token, taken_at desc);

create table dev_wallets (
  token          text not null references tokens(address),
  address        text not null,
  relation       text not null,              -- 'creator' | 'funded_by_creator' | 'funded_creator'
  last_tx_at     timestamptz,
  primary key (token, address)
);

create table contract_checks (
  token          text primary key references tokens(address),
  has_mint       boolean not null,
  can_sell       boolean not null,
  has_owner      boolean not null,
  owner_functions text[] not null default '{}',
  liquidity_locked boolean not null,
  checked_at     timestamptz not null
);

create table socials (
  id             bigserial primary key,
  token          text not null references tokens(address),
  type           social_type not null,
  url            text not null,
  metric         int,                        -- followers, membres, commits...
  last_activity_at timestamptz,               -- dernier tweet du compte, dernier commit...
  account_exists boolean,                    -- compte X supprimé ou suspendu = false
  status         social_status not null default 'unknown',
  checked_at     timestamptz,
  unique (token, type)
);

create table dead_checks (
  token          text primary key references tokens(address),
  is_dead        boolean not null,
  volume_ok      boolean not null,           -- < 1 000 $/j sur 7 j
  drawdown_ok    boolean not null,           -- baisse >= 85 %
  dev_inactive_ok boolean not null,          -- 14 j sans tx
  target_ok      boolean not null,           -- filtres de la cible Vivra (plus haut, market cap, X, docs, tech)
  safety_ok      boolean not null,           -- règles de sécurité
  eligible       boolean not null,           -- target_ok and safety_ok
  exclusion_reasons text[] not null default '{}',
  computed_at    timestamptz not null
);

create table holder_positions (               -- pour « holders sous l'eau » et l'ancienneté
  token          text not null references tokens(address),
  wallet         text not null,
  balance        numeric(78,0) not null,
  bought_amount  numeric(78,0) not null default 0,
  avg_cost_usd   numeric(30,12),             -- prix de revient moyen des achats lus à l'analyse
  first_held_at  timestamptz not null,
  updated_at     timestamptz not null default now(),
  primary key (token, wallet)
);

create table recovery_scores (
  id             bigserial primary key,
  token          text not null references tokens(address),
  score          int not null check (score between 0 and 100),
  components     jsonb not null,             -- détail par critère, voir annexe E
  weights_version int not null,
  computed_at    timestamptz not null
);
create index on recovery_scores (token, computed_at desc);

create table settings (
  key            text primary key,           -- ex. 'dead.volume_max_usd'
  value          jsonb not null,
  updated_by     bigint,
  updated_at     timestamptz not null default now()
);

create table users (
  id             bigserial primary key,
  email_hash     bytea not null unique,      -- HMAC-SHA256(email normalisé, clé serveur), pour l'unicité
  email_enc      bytea not null,             -- email chiffré (AES-GCM), jamais exposé par l'API
  is_team        boolean not null default false,
  signup_ip_hash bytea,
  device_hash    bytea,
  blocked        boolean not null default false,
  created_at     timestamptz not null default now()
);

create table auth_codes (
  id             bigserial primary key,
  email_hash     bytea not null,
  code_hash      bytea not null,             -- code à 6 chiffres ou jeton du lien magique, haché
  expires_at     timestamptz not null,       -- 15 min
  attempts       int not null default 0,     -- bloqué à 5
  used_at        timestamptz,
  created_at     timestamptz not null default now()
);
create index on auth_codes (email_hash, created_at desc);

create table sessions (
  id             uuid primary key default gen_random_uuid(),
  user_id        bigint not null references users(id),
  expires_at     timestamptz not null,       -- 30 jours
  revoked_at     timestamptz,
  created_at     timestamptz not null default now()
);

create table cycles (
  id             bigserial primary key,
  number         int not null unique,
  status         cycle_status not null default 'draft',
  publish_at     timestamptz not null,
  vote_opens_at  timestamptz not null,
  vote_closes_at timestamptz not null,       -- = J0
  min_account_age_hours int not null default 24, -- 0 au cycle 1
  winner_candidate_id bigint,
  created_at     timestamptz not null default now(),
  check (vote_closes_at > vote_opens_at)
);

create table candidates (
  id             bigserial primary key,
  cycle_id       bigint not null references cycles(id),
  token          text not null references tokens(address),
  position       int not null check (position between 1 and 5),
  tagline        text not null,
  thesis_short   text not null,              -- une phrase
  thesis_long    text not null,              -- un paragraphe
  reasons        text[] not null,            -- 3 raisons
  plan           jsonb not null,             -- identité, site, utilité, X, Telegram
  risks          text[] not null,            -- 1 à 3
  scanner_report_id bigint,                  -- token_files.id du pré-rapport
  recovery_score_id bigint references recovery_scores(id), -- figé à la publication
  published_at   timestamptz,
  unique (cycle_id, token),
  unique (cycle_id, position)
);

create table votes (
  cycle_id       bigint not null references cycles(id),
  user_id        bigint not null references users(id),
  candidate_id   bigint not null references candidates(id),
  updated_at     timestamptz not null default now(),
  created_at     timestamptz not null default now(),
  primary key (cycle_id, user_id)            -- une voix par email et par cycle
);

create table supports (
  candidate_id   bigint not null references candidates(id),
  user_id        bigint not null references users(id),
  created_at     timestamptz not null default now(),
  primary key (candidate_id, user_id)
);

create table operations (
  id             bigserial primary key,
  cycle_id       bigint not null references cycles(id) unique,
  token          text not null references tokens(address),
  status         operation_status not null default 'pending',
  j0             timestamptz not null,       -- clôture du vote
  budget_usd     numeric(20,6) not null,     -- plafond global
  spent_usd      numeric(20,6) not null default 0,
  verdict        verdict,
  verdict_at     timestamptz,
  handover_at    timestamptz,                -- J+30
  created_at     timestamptz not null default now()
);

create table operation_steps (
  id             bigserial primary key,
  operation_id   bigint not null references operations(id),
  key            text not null,              -- ex. 'designer.directions', voir annexe C
  agent          agent_name,                 -- null pour les étapes non IA
  due_at         timestamptz not null,       -- T+x
  status         step_status not null default 'waiting',
  attempts       int not null default 0,
  depends_on     text[] not null default '{}',
  output_file_id bigint,
  error          text,
  started_at     timestamptz,
  finished_at    timestamptz,
  unique (operation_id, key)
);

create table token_files (                   -- le dossier du token, versionné
  id             bigserial primary key,
  operation_id   bigint references operations(id), -- null pour un pré-rapport de candidat
  token          text not null references tokens(address),
  version        int not null,
  section        text not null,              -- 'scan', 'identity', 'site', 'x', 'telegram', 'utility', 'verdict'
  content        jsonb not null,
  author         text not null,              -- agent ou 'team:<id>'
  created_at     timestamptz not null default now(),
  unique (token, section, version)
);

create table assets (
  id             bigserial primary key,
  operation_id   bigint not null references operations(id),
  kind           text not null,              -- 'logo', 'pfp', 'banner_x', 'banner_tg', 'og', 'meme'
  url            text not null,              -- stockage objet
  meta           jsonb not null default '{}',
  created_at     timestamptz not null default now()
);

create table log_entries (                   -- journal public
  id             bigserial primary key,
  operation_id   bigint not null references operations(id),
  step_id        bigint references operation_steps(id),
  agent          agent_name,
  kind           text not null,              -- 'llm_call', 'tool_call', 'step', 'validation', 'alert'
  summary        text not null,              -- une ligne lisible, affichée sur la dapp
  model          text,
  input_tokens   int,
  output_tokens  int,
  cost_usd       numeric(20,6) not null default 0,
  payload        jsonb,                      -- détail, non public
  created_at     timestamptz not null default now()
);
create index on log_entries (operation_id, created_at);

create table validations (
  id             bigserial primary key,
  operation_id   bigint references operations(id),
  kind           text not null,              -- 'x_post', 'site', 'tg_pinned', 'asset', 'cto_dossier'
  ref_id         bigint,
  preview        jsonb not null,
  status         validation_status not null default 'pending',
  decided_by     bigint references users(id),
  decided_at     timestamptz,
  reason         text,                       -- motif de refus, renvoyé à l'agent
  created_at     timestamptz not null default now()
);

create table social_posts (
  id             bigserial primary key,
  account        text not null,              -- 'vivra' ou adresse du token CTO
  platform       social_type not null,
  template       text,
  body           text not null,
  media_urls     text[] not null default '{}',
  validation_id  bigint references validations(id),
  scheduled_at   timestamptz,
  published_at   timestamptz,
  external_id    text,
  created_at     timestamptz not null default now()
);

create table wallets (
  name           text primary key,           -- 'collecte', 'equipe', 'buyback_reserve', 'buyback_hot', 'ia', 'frais_cto'
  address        text not null unique,
  min_balance_wei numeric(78,0),
  max_balance_wei numeric(78,0)
);

create table buyback_plans (
  id             bigserial primary key,
  target         buyback_target not null,
  token          text not null,              -- token CTO ou $VIVRA
  operation_id   bigint references operations(id),
  envelope_wei   numeric(78,0) not null,
  cap_wei        numeric(78,0),
  starts_at      timestamptz not null,
  ends_at        timestamptz not null,
  stopped_at     timestamptz,
  stop_reason    text,
  created_at     timestamptz not null default now()
);

create table buyback_slices (
  id             bigserial primary key,
  plan_id        bigint not null references buyback_plans(id),
  index          int not null,
  scheduled_at_enc bytea not null,           -- horaire chiffré, publié après exécution
  executed_at    timestamptz,
  status         slice_status not null default 'planned',
  amount_in_wei  numeric(78,0),
  min_out        numeric(78,0),
  amount_burned  numeric(78,0),
  liquidity_usd_at_exec numeric(20,6),
  slippage_bps   int,
  tx_hash        text,
  error          text,
  unique (plan_id, index)
);

create table ledger_entries (
  id             bigserial primary key,
  type           ledger_type not null,
  token          text,                       -- jeton déplacé (null = ETH)
  amount         numeric(78,0) not null,
  amount_usd     numeric(20,6),
  from_wallet    text,
  to_wallet      text,
  operation_id   bigint references operations(id),
  tx_hash        text,
  occurred_at    timestamptz not null,
  created_at     timestamptz not null default now()
);
create index on ledger_entries (occurred_at desc);

create table cto_requests (
  id             bigserial primary key,
  operation_id   bigint not null references operations(id) unique,
  status         cto_request_status not null default 'draft',
  dossier        jsonb not null,             -- champs du formulaire Pons
  verification_tweet_url text,
  submitted_by   bigint references users(id),
  submitted_at   timestamptz,
  pending_recipient_seen_at timestamptz,
  recipient_active_at timestamptz,
  updated_at     timestamptz not null default now()
);

create table team_wallets (                  -- accès au back-office
  address        text primary key,
  label          text not null,
  active         boolean not null default true
);

create table admin_audit (
  id             bigserial primary key,
  actor          text not null,              -- adresse de l'équipe
  action         text not null,
  target         text,
  detail         jsonb,
  created_at     timestamptz not null default now()
);