feat: upload of the projectHEADmaster

Signed-off-by: zwlucas <lucas.fariamo08@gmail.com>

9 files changed, 1412 insertions, 0 deletions

diff --git a/api/src/db/connection.ts b/api/src/db/connection.ts
new file mode 100644
index 0000000..2982336
--- /dev/null
+++ b/api/src/db/connection.ts
@@ -0,0 +1,37 @@
+/**
+ * connection.ts
+ *
+ * Singleton de conexão com o banco de dados.
+ *
+ * Exporta uma única instância do Drizzle ORM configurada com:
+ *  - better-sqlite3 como driver síncrono
+ *  - WAL mode para concorrência
+ *  - Foreign keys habilitadas
+ *  - Schema tipado para autocompletar queries
+ *
+ * Uso em qualquer módulo da aplicação:
+ *   import { db } from '@/db/connection'
+ */
+
+import Database from 'better-sqlite3';
+import { drizzle } from 'drizzle-orm/better-sqlite3';
+import * as schema from './schema.js';
+
+const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db';
+const dbPath = DATABASE_URL.replace(/^file:/, '');
+
+// ─── Criação do cliente SQLite ────────────────────────────────────────────────
+const sqlite = new Database(dbPath);
+
+// WAL mode: leituras não bloqueiam escritas (essencial para servidor web)
+sqlite.pragma('journal_mode = WAL');
+
+// Ativa integridade referencial (desativada por padrão no SQLite)
+sqlite.pragma('foreign_keys = ON');
+
+// ─── Instância Drizzle com schema completo ────────────────────────────────────
+// Passar o schema habilita db.query.tickets.findMany() com joins automáticos
+export const db = drizzle(sqlite, { schema });
+
+// Exporta o tipo para uso em funções que recebem `db` como parâmetro
+export type Database = typeof db;
diff --git a/api/src/db/migrate.ts b/api/src/db/migrate.ts
new file mode 100644
index 0000000..7964ef6
--- /dev/null
+++ b/api/src/db/migrate.ts
@@ -0,0 +1,56 @@
+/**
+ * migrate.ts
+ *
+ * Script responsável por aplicar todas as migrations pendentes no banco SQLite.
+ *
+ * Pode ser executado de duas formas:
+ *   1. Manual (CLI):  pnpm db:migrate
+ *   2. Programática:  importado e chamado no boot do servidor (server.ts)
+ */
+
+import Database from 'better-sqlite3';
+import { drizzle } from 'drizzle-orm/better-sqlite3';
+import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
+import path from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+// Resolve o diretório correto em módulos ESM (não existe __dirname por padrão)
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const MIGRATIONS_DIR = path.join(__dirname, 'migrations');
+const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db';
+
+// Drizzle espera o caminho sem o prefixo "file:"
+const dbPath = DATABASE_URL.replace(/^file:/, '');
+
+export async function runMigrations(): Promise<void> {
+  console.log('🗄️  Iniciando migrações do banco de dados...');
+  console.log(`   Banco  : ${dbPath}`);
+  console.log(`   Origem : ${MIGRATIONS_DIR}`);
+
+  // better-sqlite3 opera de forma síncrona (sem Promises)
+  const sqlite = new Database(dbPath);
+
+  // Otimizações de performance recomendadas para SQLite
+  sqlite.pragma('journal_mode = WAL');  // Write-Ahead Logging: leituras não bloqueiam escritas
+  sqlite.pragma('foreign_keys = ON');   // Ativa integridade referencial (desativada por padrão no SQLite)
+
+  const db = drizzle(sqlite);
+
+  // Aplica todas as migrations ainda não registradas na tabela __drizzle_migrations
+  migrate(db, { migrationsFolder: MIGRATIONS_DIR });
+
+  sqlite.close();
+  console.log('✅ Migrações aplicadas com sucesso.');
+}
+
+// ─── Execução direta (CLI) ────────────────────────────────────────────────────
+// Roda apenas quando o arquivo é chamado diretamente (pnpm db:migrate)
+// e não quando é importado como módulo pelo server.ts
+const isMain = process.argv[1] === fileURLToPath(import.meta.url);
+
+if (isMain) {
+  runMigrations().catch((err) => {
+    console.error('❌ Falha ao aplicar migrações:', err);
+    process.exit(1);
+  });
+}
diff --git a/api/src/db/migrations/0000_huge_luke_cage.sql b/api/src/db/migrations/0000_huge_luke_cage.sql
new file mode 100644
index 0000000..1abb4d2
--- /dev/null
+++ b/api/src/db/migrations/0000_huge_luke_cage.sql
@@ -0,0 +1,61 @@
+CREATE TABLE `categories` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`name` text NOT NULL,
+	`slug` text NOT NULL,
+	`description` text,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE `comments` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`content` text NOT NULL,
+	`is_internal` integer DEFAULT false NOT NULL,
+	`ticket_id` integer NOT NULL,
+	`author_id` integer NOT NULL,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade,
+	FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict
+);
+--> statement-breakpoint
+CREATE TABLE `tickets` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`title` text NOT NULL,
+	`description` text NOT NULL,
+	`status` text DEFAULT 'open' NOT NULL,
+	`priority` text DEFAULT 'medium' NOT NULL,
+	`creator_id` integer NOT NULL,
+	`assignee_id` integer,
+	`category_id` integer,
+	`due_at` text,
+	`resolved_at` text,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict,
+	FOREIGN KEY (`assignee_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE set null,
+	FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
+);
+--> statement-breakpoint
+CREATE TABLE `users` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`name` text NOT NULL,
+	`email` text NOT NULL,
+	`password` text NOT NULL,
+	`role` text DEFAULT 'requester' NOT NULL,
+	`is_active` integer DEFAULT true NOT NULL,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
+);
+--> statement-breakpoint
+CREATE UNIQUE INDEX `categories_slug_unique_idx` ON `categories` (`slug`);--> statement-breakpoint
+CREATE INDEX `comments_ticket_id_idx` ON `comments` (`ticket_id`);--> statement-breakpoint
+CREATE INDEX `comments_author_id_idx` ON `comments` (`author_id`);--> statement-breakpoint
+CREATE INDEX `tickets_status_idx` ON `tickets` (`status`);--> statement-breakpoint
+CREATE INDEX `tickets_priority_idx` ON `tickets` (`priority`);--> statement-breakpoint
+CREATE INDEX `tickets_assignee_id_idx` ON `tickets` (`assignee_id`);--> statement-breakpoint
+CREATE INDEX `tickets_creator_id_idx` ON `tickets` (`creator_id`);--> statement-breakpoint
+CREATE INDEX `tickets_category_id_idx` ON `tickets` (`category_id`);--> statement-breakpoint
+CREATE INDEX `tickets_status_assignee_idx` ON `tickets` (`status`,`assignee_id`);--> statement-breakpoint
+CREATE UNIQUE INDEX `users_email_unique_idx` ON `users` (`email`);--> statement-breakpoint
+CREATE INDEX `users_role_idx` ON `users` (`role`);
\ No newline at end of file
diff --git a/api/src/db/migrations/0001_ambitious_emma_frost.sql b/api/src/db/migrations/0001_ambitious_emma_frost.sql
new file mode 100644
index 0000000..c4256aa
--- /dev/null
+++ b/api/src/db/migrations/0001_ambitious_emma_frost.sql
@@ -0,0 +1,6 @@
+CREATE TABLE `system_settings` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`allow_requester_chat` integer DEFAULT true NOT NULL,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
+);
diff --git a/api/src/db/migrations/meta/0000_snapshot.json b/api/src/db/migrations/meta/0000_snapshot.json
new file mode 100644
index 0000000..8cdfb93
--- /dev/null
+++ b/api/src/db/migrations/meta/0000_snapshot.json
@@ -0,0 +1,446 @@
+{
+  "version": "6",
+  "dialect": "sqlite",
+  "id": "6d22de90-25a5-4565-8b37-8d1d479c2dc6",
+  "prevId": "00000000-0000-0000-0000-000000000000",
+  "tables": {
+    "categories": {
+      "name": "categories",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "slug": {
+          "name": "slug",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "description": {
+          "name": "description",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "categories_slug_unique_idx": {
+          "name": "categories_slug_unique_idx",
+          "columns": [
+            "slug"
+          ],
+          "isUnique": true
+        }
+      },
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "comments": {
+      "name": "comments",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "content": {
+          "name": "content",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "is_internal": {
+          "name": "is_internal",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": false
+        },
+        "ticket_id": {
+          "name": "ticket_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "author_id": {
+          "name": "author_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "comments_ticket_id_idx": {
+          "name": "comments_ticket_id_idx",
+          "columns": [
+            "ticket_id"
+          ],
+          "isUnique": false
+        },
+        "comments_author_id_idx": {
+          "name": "comments_author_id_idx",
+          "columns": [
+            "author_id"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "comments_ticket_id_tickets_id_fk": {
+          "name": "comments_ticket_id_tickets_id_fk",
+          "tableFrom": "comments",
+          "tableTo": "tickets",
+          "columnsFrom": [
+            "ticket_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "cascade",
+          "onUpdate": "no action"
+        },
+        "comments_author_id_users_id_fk": {
+          "name": "comments_author_id_users_id_fk",
+          "tableFrom": "comments",
+          "tableTo": "users",
+          "columnsFrom": [
+            "author_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "tickets": {
+      "name": "tickets",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "title": {
+          "name": "title",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "description": {
+          "name": "description",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "status": {
+          "name": "status",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'open'"
+        },
+        "priority": {
+          "name": "priority",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'medium'"
+        },
+        "creator_id": {
+          "name": "creator_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "assignee_id": {
+          "name": "assignee_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "category_id": {
+          "name": "category_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "due_at": {
+          "name": "due_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "resolved_at": {
+          "name": "resolved_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "tickets_status_idx": {
+          "name": "tickets_status_idx",
+          "columns": [
+            "status"
+          ],
+          "isUnique": false
+        },
+        "tickets_priority_idx": {
+          "name": "tickets_priority_idx",
+          "columns": [
+            "priority"
+          ],
+          "isUnique": false
+        },
+        "tickets_assignee_id_idx": {
+          "name": "tickets_assignee_id_idx",
+          "columns": [
+            "assignee_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_creator_id_idx": {
+          "name": "tickets_creator_id_idx",
+          "columns": [
+            "creator_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_category_id_idx": {
+          "name": "tickets_category_id_idx",
+          "columns": [
+            "category_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_status_assignee_idx": {
+          "name": "tickets_status_assignee_idx",
+          "columns": [
+            "status",
+            "assignee_id"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "tickets_creator_id_users_id_fk": {
+          "name": "tickets_creator_id_users_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "users",
+          "columnsFrom": [
+            "creator_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        },
+        "tickets_assignee_id_users_id_fk": {
+          "name": "tickets_assignee_id_users_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "users",
+          "columnsFrom": [
+            "assignee_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "set null",
+          "onUpdate": "no action"
+        },
+        "tickets_category_id_categories_id_fk": {
+          "name": "tickets_category_id_categories_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "categories",
+          "columnsFrom": [
+            "category_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "set null",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "users": {
+      "name": "users",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "email": {
+          "name": "email",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "password": {
+          "name": "password",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "role": {
+          "name": "role",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'requester'"
+        },
+        "is_active": {
+          "name": "is_active",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": true
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "users_email_unique_idx": {
+          "name": "users_email_unique_idx",
+          "columns": [
+            "email"
+          ],
+          "isUnique": true
+        },
+        "users_role_idx": {
+          "name": "users_role_idx",
+          "columns": [
+            "role"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    }
+  },
+  "enums": {},
+  "_meta": {
+    "schemas": {},
+    "tables": {},
+    "columns": {}
+  },
+  "internal": {
+    "indexes": {}
+  }
+}
\ No newline at end of file
diff --git a/api/src/db/migrations/meta/0001_snapshot.json b/api/src/db/migrations/meta/0001_snapshot.json
new file mode 100644
index 0000000..9fd41f7
--- /dev/null
+++ b/api/src/db/migrations/meta/0001_snapshot.json
@@ -0,0 +1,486 @@
+{
+  "version": "6",
+  "dialect": "sqlite",
+  "id": "e3832373-0ea8-46a1-8480-091761f1a94a",
+  "prevId": "6d22de90-25a5-4565-8b37-8d1d479c2dc6",
+  "tables": {
+    "categories": {
+      "name": "categories",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "slug": {
+          "name": "slug",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "description": {
+          "name": "description",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "categories_slug_unique_idx": {
+          "name": "categories_slug_unique_idx",
+          "columns": [
+            "slug"
+          ],
+          "isUnique": true
+        }
+      },
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "comments": {
+      "name": "comments",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "content": {
+          "name": "content",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "is_internal": {
+          "name": "is_internal",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": false
+        },
+        "ticket_id": {
+          "name": "ticket_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "author_id": {
+          "name": "author_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "comments_ticket_id_idx": {
+          "name": "comments_ticket_id_idx",
+          "columns": [
+            "ticket_id"
+          ],
+          "isUnique": false
+        },
+        "comments_author_id_idx": {
+          "name": "comments_author_id_idx",
+          "columns": [
+            "author_id"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "comments_ticket_id_tickets_id_fk": {
+          "name": "comments_ticket_id_tickets_id_fk",
+          "tableFrom": "comments",
+          "tableTo": "tickets",
+          "columnsFrom": [
+            "ticket_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "cascade",
+          "onUpdate": "no action"
+        },
+        "comments_author_id_users_id_fk": {
+          "name": "comments_author_id_users_id_fk",
+          "tableFrom": "comments",
+          "tableTo": "users",
+          "columnsFrom": [
+            "author_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "system_settings": {
+      "name": "system_settings",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "allow_requester_chat": {
+          "name": "allow_requester_chat",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": true
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {},
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "tickets": {
+      "name": "tickets",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "title": {
+          "name": "title",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "description": {
+          "name": "description",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "status": {
+          "name": "status",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'open'"
+        },
+        "priority": {
+          "name": "priority",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'medium'"
+        },
+        "creator_id": {
+          "name": "creator_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "assignee_id": {
+          "name": "assignee_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "category_id": {
+          "name": "category_id",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "due_at": {
+          "name": "due_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "resolved_at": {
+          "name": "resolved_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "tickets_status_idx": {
+          "name": "tickets_status_idx",
+          "columns": [
+            "status"
+          ],
+          "isUnique": false
+        },
+        "tickets_priority_idx": {
+          "name": "tickets_priority_idx",
+          "columns": [
+            "priority"
+          ],
+          "isUnique": false
+        },
+        "tickets_assignee_id_idx": {
+          "name": "tickets_assignee_id_idx",
+          "columns": [
+            "assignee_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_creator_id_idx": {
+          "name": "tickets_creator_id_idx",
+          "columns": [
+            "creator_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_category_id_idx": {
+          "name": "tickets_category_id_idx",
+          "columns": [
+            "category_id"
+          ],
+          "isUnique": false
+        },
+        "tickets_status_assignee_idx": {
+          "name": "tickets_status_assignee_idx",
+          "columns": [
+            "status",
+            "assignee_id"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "tickets_creator_id_users_id_fk": {
+          "name": "tickets_creator_id_users_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "users",
+          "columnsFrom": [
+            "creator_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "restrict",
+          "onUpdate": "no action"
+        },
+        "tickets_assignee_id_users_id_fk": {
+          "name": "tickets_assignee_id_users_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "users",
+          "columnsFrom": [
+            "assignee_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "set null",
+          "onUpdate": "no action"
+        },
+        "tickets_category_id_categories_id_fk": {
+          "name": "tickets_category_id_categories_id_fk",
+          "tableFrom": "tickets",
+          "tableTo": "categories",
+          "columnsFrom": [
+            "category_id"
+          ],
+          "columnsTo": [
+            "id"
+          ],
+          "onDelete": "set null",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "users": {
+      "name": "users",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "name": {
+          "name": "name",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "email": {
+          "name": "email",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "password": {
+          "name": "password",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "role": {
+          "name": "role",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "'requester'"
+        },
+        "is_active": {
+          "name": "is_active",
+          "type": "integer",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": true
+        },
+        "created_at": {
+          "name": "created_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        },
+        "updated_at": {
+          "name": "updated_at",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false,
+          "default": "(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))"
+        }
+      },
+      "indexes": {
+        "users_email_unique_idx": {
+          "name": "users_email_unique_idx",
+          "columns": [
+            "email"
+          ],
+          "isUnique": true
+        },
+        "users_role_idx": {
+          "name": "users_role_idx",
+          "columns": [
+            "role"
+          ],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {},
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    }
+  },
+  "enums": {},
+  "_meta": {
+    "schemas": {},
+    "tables": {},
+    "columns": {}
+  },
+  "internal": {
+    "indexes": {}
+  }
+}
\ No newline at end of file
diff --git a/api/src/db/migrations/meta/_journal.json b/api/src/db/migrations/meta/_journal.json
new file mode 100644
index 0000000..b3ea0c0
--- /dev/null
+++ b/api/src/db/migrations/meta/_journal.json
@@ -0,0 +1,20 @@
+{
+  "version": "7",
+  "dialect": "sqlite",
+  "entries": [
+    {
+      "idx": 0,
+      "version": "6",
+      "when": 1784989322795,
+      "tag": "0000_huge_luke_cage",
+      "breakpoints": true
+    },
+    {
+      "idx": 1,
+      "version": "6",
+      "when": 1785005330713,
+      "tag": "0001_ambitious_emma_frost",
+      "breakpoints": true
+    }
+  ]
+}
\ No newline at end of file
diff --git a/api/src/db/schema.ts b/api/src/db/schema.ts
new file mode 100644
index 0000000..ce95493
--- /dev/null
+++ b/api/src/db/schema.ts
@@ -0,0 +1,240 @@
+/**
+ * schema.ts
+ *
+ * Fonte única de verdade do banco de dados.
+ * Todos os tipos TypeScript são *inferidos* deste arquivo — nunca definidos
+ * manualmente — garantindo que DB e aplicação nunca fiquem dessincronizados.
+ */
+
+import { sql } from 'drizzle-orm';
+import {
+  sqliteTable,
+  text,
+  integer,
+  index,
+  uniqueIndex,
+} from 'drizzle-orm/sqlite-core';
+
+// ─────────────────────────────────────────────────────────────────────────────
+// HELPERS
+// ─────────────────────────────────────────────────────────────────────────────
+
+/**
+ * Colunas de auditoria reutilizáveis.
+ * SQLite armazena timestamps como texto ISO 8601.
+ */
+const timestamps = {
+  createdAt: text('created_at')
+    .notNull()
+    .default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
+  updatedAt: text('updated_at')
+    .notNull()
+    .default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
+};
+
+// ─────────────────────────────────────────────────────────────────────────────
+// ENUMS (simulados via union types — SQLite não tem tipo ENUM nativo)
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const USER_ROLES = ['requester', 'agent', 'admin'] as const;
+export type UserRole = (typeof USER_ROLES)[number];
+
+export const TICKET_STATUSES = [
+  'open',         // Chamado aberto, ainda não atribuído
+  'in_progress',  // Atribuído a um agente, em andamento
+  'waiting',      // Aguardando resposta do solicitante
+  'resolved',     // Solução aplicada, aguardando confirmação
+  'closed',       // Encerrado definitivamente
+] as const;
+export type TicketStatus = (typeof TICKET_STATUSES)[number];
+
+export const TICKET_PRIORITIES = ['low', 'medium', 'high', 'critical'] as const;
+export type TicketPriority = (typeof TICKET_PRIORITIES)[number];
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TABELA: users
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const users = sqliteTable(
+  'users',
+  {
+    id: integer('id').primaryKey({ autoIncrement: true }),
+
+    // Identificação
+    name:     text('name').notNull(),
+    email:    text('email').notNull(),
+    password: text('password').notNull(), // hash bcrypt — nunca plain text
+
+    // Controle de acesso
+    role: text('role', { enum: USER_ROLES }).notNull().default('requester'),
+
+    // Conta ativa? Soft-delete sem perder histórico de tickets/comments
+    isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
+
+    ...timestamps,
+  },
+  (table) => ({
+    // Garante emails únicos na camada do banco
+    emailUniqueIdx: uniqueIndex('users_email_unique_idx').on(table.email),
+
+    // Consultas frequentes por role (ex: listar todos os agentes)
+    roleIdx: index('users_role_idx').on(table.role),
+  }),
+);
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TABELA: categories
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const categories = sqliteTable(
+  'categories',
+  {
+    id:          integer('id').primaryKey({ autoIncrement: true }),
+    name:        text('name').notNull(),
+
+    // Slug para uso em URLs e filtros: "hardware", "acesso-sistema", etc.
+    slug:        text('slug').notNull(),
+
+    description: text('description'),
+
+    ...timestamps,
+  },
+  (table) => ({
+    slugUniqueIdx: uniqueIndex('categories_slug_unique_idx').on(table.slug),
+  }),
+);
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TABELA: tickets
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const tickets = sqliteTable(
+  'tickets',
+  {
+    id: integer('id').primaryKey({ autoIncrement: true }),
+
+    // Conteúdo
+    title:       text('title').notNull(),
+    description: text('description').notNull(),
+
+    // Classificação
+    status: text('status', { enum: TICKET_STATUSES })
+      .notNull()
+      .default('open'),
+
+    priority: text('priority', { enum: TICKET_PRIORITIES })
+      .notNull()
+      .default('medium'),
+
+    // ── Relacionamentos ────────────────────────────────────────────────────
+    // Quem abriu o chamado (obrigatório)
+    creatorId: integer('creator_id')
+      .notNull()
+      .references(() => users.id, { onDelete: 'restrict' }),
+
+    // Agente responsável (opcional — pode ser atribuído depois)
+    assigneeId: integer('assignee_id')
+      .references(() => users.id, { onDelete: 'set null' }),
+
+    // Categoria do chamado (opcional — pode ser classificado depois)
+    categoryId: integer('category_id')
+      .references(() => categories.id, { onDelete: 'set null' }),
+
+    // Data limite para resolução (SLA)
+    dueAt: text('due_at'),
+
+    // Data em que o chamado foi efetivamente resolvido
+    resolvedAt: text('resolved_at'),
+
+    ...timestamps,
+  },
+  (table) => ({
+    // Índices para os filtros mais comuns no painel do agente
+    statusIdx:     index('tickets_status_idx').on(table.status),
+    priorityIdx:   index('tickets_priority_idx').on(table.priority),
+    assigneeIdx:   index('tickets_assignee_id_idx').on(table.assigneeId),
+    creatorIdx:    index('tickets_creator_id_idx').on(table.creatorId),
+    categoryIdx:   index('tickets_category_id_idx').on(table.categoryId),
+
+    // Índice composto: painel do agente filtra por status + assignee com frequência
+    statusAssigneeIdx: index('tickets_status_assignee_idx').on(
+      table.status,
+      table.assigneeId,
+    ),
+  }),
+);
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TABELA: comments
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const comments = sqliteTable(
+  'comments',
+  {
+    id: integer('id').primaryKey({ autoIncrement: true }),
+
+    content: text('content').notNull(),
+
+    /**
+     * Diferencia mensagens públicas de notas internas da equipe.
+     * true  → nota interna (visível apenas para agentes/admins)
+     * false → resposta pública (visível para o solicitante)
+     */
+    isInternal: integer('is_internal', { mode: 'boolean' })
+      .notNull()
+      .default(false),
+
+    // ── Relacionamentos ────────────────────────────────────────────────────
+    ticketId: integer('ticket_id')
+      .notNull()
+      .references(() => tickets.id, { onDelete: 'cascade' }),
+
+    authorId: integer('author_id')
+      .notNull()
+      .references(() => users.id, { onDelete: 'restrict' }),
+
+    ...timestamps,
+  },
+  (table) => ({
+    // Buscar todos os comentários de um ticket é a query mais frequente
+    ticketIdx: index('comments_ticket_id_idx').on(table.ticketId),
+    authorIdx: index('comments_author_id_idx').on(table.authorId),
+  }),
+);
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TABELA: system_settings
+// ─────────────────────────────────────────────────────────────────────────────
+
+export const systemSettings = sqliteTable(
+  'system_settings',
+  {
+    id: integer('id').primaryKey({ autoIncrement: true }),
+    
+    // Configurações Globais
+    allowRequesterChat: integer('allow_requester_chat', { mode: 'boolean' }).notNull().default(true),
+    
+    ...timestamps,
+  }
+);
+
+// ─────────────────────────────────────────────────────────────────────────────
+// TIPOS INFERIDOS
+//
+// Use estes tipos em toda a aplicação.
+// Ex: função que retorna um ticket → retorno tipado como `Ticket`
+// ─────────────────────────────────────────────────────────────────────────────
+
+export type User     = typeof users.$inferSelect;
+export type NewUser  = typeof users.$inferInsert;
+
+export type Category    = typeof categories.$inferSelect;
+export type NewCategory = typeof categories.$inferInsert;
+
+export type Ticket    = typeof tickets.$inferSelect;
+export type NewTicket = typeof tickets.$inferInsert;
+
+export type Comment    = typeof comments.$inferSelect;
+export type NewComment = typeof comments.$inferInsert;
+
+export type SystemSetting = typeof systemSettings.$inferSelect;
diff --git a/api/src/db/seed.ts b/api/src/db/seed.ts
new file mode 100644
index 0000000..2cde3ce
--- /dev/null
+++ b/api/src/db/seed.ts
@@ -0,0 +1,60 @@
+import Database from 'better-sqlite3';
+import { drizzle } from 'drizzle-orm/better-sqlite3';
+import * as schema from './schema.js';
+import bcrypt from 'bcryptjs';
+import path from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const DATABASE_URL = process.env['DATABASE_URL'] ?? 'file:./helpdesk.db';
+const dbPath = DATABASE_URL.replace(/^file:/, '');
+
+const sqlite = new Database(dbPath);
+const db = drizzle(sqlite, { schema });
+
+async function seed() {
+  console.log('🌱 Verificando usuário admin...');
+
+  const existingAdmin = await db.query.users.findFirst({
+    where: (users, { eq }) => eq(users.email, 'admin@helpdesk.local'),
+  });
+
+  if (!existingAdmin) {
+    const hashedPassword = await bcrypt.hash('admin123', 10);
+
+    await db.insert(schema.users).values({
+      name: 'Administrador TI',
+      email: 'admin@helpdesk.local',
+      password: hashedPassword,
+      role: 'admin',
+      isActive: true,
+    });
+
+    console.log('✅ Usuário admin criado com sucesso!');
+    console.log('E-mail: admin@helpdesk.local');
+    console.log('Senha: admin123');
+  } else {
+    console.log('✅ Usuário admin já existe!');
+  }
+
+  console.log('🌱 Verificando categorias...');
+  const existingCategories = await db.query.categories.findMany();
+  if (existingCategories.length === 0) {
+    await db.insert(schema.categories).values([
+      { name: 'Hardware', slug: 'hardware', description: 'Problemas físicos em computadores, impressoras e periféricos' },
+      { name: 'Software', slug: 'software', description: 'Instalação, erros e licenças de programas' },
+      { name: 'Redes e Internet', slug: 'redes', description: 'Sem conexão, lentidão ou falhas no Wi-Fi/Cabo' },
+      { name: 'Acessos e Senhas', slug: 'acessos', description: 'Redefinição de senha, criação de e-mail e acessos a sistemas' },
+      { name: 'Dúvidas e Suporte', slug: 'duvidas', description: 'Dúvidas gerais sobre uso de sistemas e equipamentos' }
+    ]);
+    console.log('✅ Categorias padrão criadas com sucesso!');
+  } else {
+    console.log('✅ Categorias já existem!');
+  }
+
+  sqlite.close();
+}
+
+seed().catch((err) => {
+  console.error('❌ Erro no seed:', err);
+  process.exit(1);
+});