SQLproject

Imported from Davi-Gomes67/SQLproject (AGENTS.md). Install upstream with npx skills add Davi-Gomes67/SQLproject. Copyright stays with the author.

AGENTS.md

Guidance for AI agents and developers working in this repository.

Project Overview

GravitalSQL — an interactive, educational SQL platform that teaches SQL (CRUD + JOINs + transactions) to absolute beginners. Everything runs 100% in the browser — there is no backend. Real SQL commands are executed against a SQLite database compiled to WebAssembly (via sql.js).

Tech stack (mandatory — do not replace)

Getting Started

npm install
npm run dev      # Vite dev server on http://localhost:5173
npm run build    # production build (must exit 0 before finishing changes)
npm run preview  # serve the production build
npm test         # Vitest: runs src/testes/bateria.test.js against the battery

Visual Identity

• Logo: public/logo.png (favicon + brand). Official brand name: GravitalSQL — never write GravitaSQL, gravitaSQL, GRAVITASQL, Gravita Sql, etc.
• Theme: dark by default, light variant. Theme variables in src/index.css (mapped to Tailwind classes via @theme): fundo/painel/card/borda/texto plus semantic colors primario (bronze), sucesso (olive), erro (burnt red), aviso (amber).
• Industrial/metallic palette — dark: bg #121110, panel #1c1a17, card #252220, border #3d3831/#6b5d45, bronze #b08d57; light: bg #ece5d8, panel #f6f1e8, card #fbf8f2, primary #8a6d3f.
• Monaco themes: src/theme/monacoThemes.js registers gravital-dark/gravital-light.

Repository Layout

src/
├── main.jsx                  # entry point; bundles Monaco locally; registers Monaco themes
├── App.jsx                   # hash router + pipeline: executar → validação → revisão
├── index.css                 # theme CSS variables (dark/light) mapped via @theme
├── context/
│   └── ThemeContext.jsx      # ThemeProvider/useTheme (localStorage 'gravitalsql:tema', default 'dark')
├── theme/
│   └── monacoThemes.js       # gravital-dark / gravital-light Monaco themes
├── router/
│   └── hashRouter.js         # useRota()/navegarPara() — hash routing (#/inicio, #/playground, …)
├── components/
│   ├── Header.jsx            # logo + status pill SQLite (versão, tabelas) + menu mobile + Reiniciar
│   ├── ThemeToggle.jsx       # dark/light toggle
│   ├── SidebarNav.jsx        # 7 rotas: Início / Playground SQL / Missões / Minhas Tabelas / Academia SQL / Guia de Erros / Diagnóstico
│   ├── InspectorTabelas.jsx  # real-time table inspector (schema + UNIQUE + CHECK + FKs reversas + LIMIT 100)
│   ├── SQLEditor.jsx         # Monaco editor + Executar (Ctrl+Enter; prop esconderExecutar)
│   ├── ResultadoPanel.jsx    # humanized errors (novo shape), danger warnings, checklist da revisão
├── screens/
│   ├── InicioScreen.jsx      # hero, atalhos, progresso por categoria
│   ├── PlaygroundScreen.jsx  # SANDBOX livre: editor + resultado + inspector (≥ lg), sem missão
│   ├── MissoesScreen.jsx     # barras de progresso por categoria + chips 1–30 + resolução
│   │                         #   (editor, Executar/Revisar/Dica, checklist, Próxima); rota #/missoes/<id>
│   ├── MinhasTabelasScreen.jsx
│   ├── AcademiaScreen.jsx    # split-pane: menu por ACADEMIA_SECOES + verbete (rota #/academia/<id>)
│   ├── ErrosScreen.jsx       # guia de erros (detalhe por erro)
│   └── DiagnosticoScreen.jsx # roda a bateria num banco isolado
├── data/
│   ├── seedSQL.js            # SCRIPT_INICIAL: importacao_clientes(3), importacao_produtos(3), contas(3; 1000/500/2000)
│   ├── missoes.js            # exporta `missoes` (30) + CATEGORIAS; missão: { id, numero, titulo, categoria,
│   │                         #   descricao, objetivo, dicas[3], conteudoVinculado[], dificuldade }
│   ├── estadosMissoes.js     # preparadores idempotentes (ids 9,12,15,19,22,23,24,28,29) + prepararMissao(db, id)
│   ├── validacoes.js         # validarMissao(id, ctx) → { ok, detalhes[], verificacoes[] }
│   ├── errosSQL.js           # catálogo de erros (gatilho regex + oQueAconteceu/porQueAcontece/comoInvestigar/
│   │                         #   possiveisSolucoes/exemplo/comoEvitar)
│   └── academia.js           # academia (58 verbetes; obrigatórios 11 campos + opcionais variacoes/verTambem)
│                             #   + CATEGORIAS_ACADEMIA + ACADEMIA_SECOES (menu); suportado: S={sim:true}|{nota}
├── hooks/
│   └── useSQLDatabase.js     # sql.js lifecycle: init, executar, aplicarMutacao, restaurarDados, resetarBanco,
│                             #   versaoSQLite, persistência e rollback automático no catch
├── testes/
│   ├── bancoDeTeste.js       # banco ISOLADO (nunca toca o do usuário) — funciona no browser e no node
│   ├── bateria.js            # rodarBateria(criarBancoDeTeste) → grupos de {grupo, nome, ok, mensagem, assercoes}
│   └── bateria.test.js       # teste Vitest (falha se qualquer asserção não passar)
└── utils/
    ├── snapshotBanco.js      # citar/listarTabelas/snapshotBanco/contagem/snLogoDiferente/linhasIguais
    ├── humanizeSqlError.js   # humano = { id, titulo, oQueAconteceu, porQueAcontece, possiveisSolucoes,
    │                         #   exemplo, comoEvitar, alvos[], mensagemCrua }; também listarErros()
    ├── detectarPerigo.js     # flags UPDATE/DELETE sem WHERE
    └── progresso.js          # lerProgresso/salvarProgresso/adicionarConcluida (chave 'gravitalsql:progresso')

Mission System (important)

• src/data/missoes.js exports a lowercase missoes array (30 missions) and CATEGORIAS. Missions carry dicas as a 3-level array (conceito → estrutura → específica), conteudoVinculado (Academia ids for "Estudar este assunto") and dificuldade.
• Validation contract — src/data/validacoes.js: validarMissao(id, ctx) → { ok, detalhes[], verificacoes[] } with ctx = { db, sql, resultado, linhasAfetadas, antes }. ctx.resultado is the raw db.exec() array (list of {columns, values}, empty on failure) — do not pass the { ok, resultados } wrapper. ctx.antes comes from snapshotBanco(db).
• Validators query the real database state (SELECT against tables, PRAGMAs, error presence), never the editor text. FK and other blocking checks are probed internally with BEGIN/INSERT|DELETE/ROLLBACK.
• Some missions need initial data: estadosMissoes.js (prepararMissao(db, id)) creates the needed support tables/rows. It never answers the mission.
• Pedagogy rules:
  • Missions are solved on the /missoes/<id> screen (progress bars + chips + editor + actions). The Playground is a free sandbox — no mission panel.
  • Selecting a mission never fills the editor. Students write their own SQL.
  • Validation is triggered by the student (Executar / "Revisar Código") and does not auto-advance. On success show "Próxima missão"; on failure ResultadoPanel renders the full verificacoes checklist (each item rotulo: mensagem with ✓/✗) under "AINDA NÃO".
  • Don't auto-advance to the next mission.
• The seed (seedSQL.js) does NOT create clientes, produtos or pedidos (missions 1–3 ask the student to create them). It provides only:
  • importacao_clientes (3), importacao_produtos (3: Smart TV/Smartwatch/Smart Lamp),
  • contas (3: saldos 1000/500/2000 → soma 3500, used by the transaction missions).

Key Architecture Rules

1. Live database, no mocks. The inspector queries the real SQLite instance and re-queries automatically (never hardcode a fixed table list):

   • SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'
   • PRAGMA table_info, PRAGMA foreign_key_list, PRAGMA index_list + PRAGMA index_info for UNIQUE, sqlite_master.sql for CHECK clauses, reverse FKs, preview LIMIT 100.

2. Real-time refresh without page reload. useSQLDatabase bumps a versao counter after every successful execution; inspector and screens subscribe to it. Extend that pattern — never reload the page.

3. Monaco is bundled locally (no CDN). Keep MonacoEnvironment workers, loader.config({ monaco }) and registrarTemas() in src/main.jsx.

4. Invalid SQL must never crash the app. Every user execution returns a structured { ok, ... } result; the transaction recovery (rollback in useSQLDatabase) keeps the DB consistent; errors render inside ResultadoPanel via humanizeSqlError.

5. Persistence under gravitalsql:*: DB (base64 of db.export()), mission progress, theme, sidebar state. PRAGMA foreign_keys = ON runs after init and after restore. resetarBanco clears storage and reloads.

6. Empty result edge case. SELECT * on an empty table may omit the result object; the inspector falls back to column names from PRAGMA table_info.

Security Rules

• User SQL runs through the real SQLite engine (sql.js, WebAssembly). Never use eval(), new Function() or interpret SQL as JavaScript.
• No backend: data stays in the browser (localStorage). Never introduce a server or network calls for app data.
• Never render untrusted HTML (no dangerouslySetInnerHTML with external content).
• Invalid SQL must keep the app alive: structured { ok, ... } results, rollback in useSQLDatabase, friendly errors via humanizeSqlError.

Known Issues

• Large Monaco vendor chunk (~3.8 MB minified, ~1 MB gzip) — expected, Monaco is bundled locally.
• No lint script configured yet (only build/test).
• vitest is declared in devDependencies (^5) — keep it there so npm test is reproducible on a fresh clone.

Rules for Future Changes

1. Analyze before modifying — read the affected files and this guide first.
2. Preserve existing functionality; never rebuild the project from scratch.
3. Do not install unnecessary dependencies.
4. Test after every change: npm run build must exit 0 and npm test must pass.
5. Update this documentation when the architecture changes.
6. Never declare a task done without testing it.
7. Never invent support for SQLite/sql.js features that do not exist here.
8. Keep the official spelling GravitalSQL in any user-visible text.
9. Keep the current visual identity (industrial/metallic, semantic colors).
10. Validations must check the real database state — never treat a mission as done just because the code "looks right".

Conventions

• UI strings and comments in Brazilian Portuguese (pt-BR); AGENTS.md stays in English. No emojis in UI — use lucide-react icons.
• src/data/missoes.js is the canonical mission list (30). To add a mission, extend it with a matching validator and optionally a preparador.
• JS source files use double quotes when the content contains single quotes (e.g. dica: "SET status = 'Concluído'").
• Tests must never depend on the user's persisted DB: always run against criarBancoDeTeste() from src/testes/bancoDeTeste.js.

Validation Checklist (before finishing)

• npm run build exits with code 0.
• npm test passes (battery: ambiente, seed, 30 missões, guia de erros).
• The inspector still lists/refreshes tables after CREATE/INSERT/UPDATE/ DELETE/DROP/ALTER.
• Invalid SQL shows a friendly error and the app keeps working.
• Theme toggle, mission progress, and restored database survive a reload.

Notes / Known Behavior

• The bundle contains a large Monaco vendor chunk (~3.8 MB minified, ~1 MB gzip). Expected because Monaco is bundled locally.
• vitest resolves sql.js/dist/sql-wasm.wasm?url to a root-relative path in node; src/testes/bancoDeTeste.js converts it to an absolute file path via import.meta.url (works in browser and node).

Git Rules

• node_modules/, dist/ and dev-server artifacts are ignored.
• Commit in English, conventional style (e.g. feat:, fix:, docs:).
• Push to origin main (https://github.com/Davi-Gomes67/SQLproject.git).