Base de Dados

O APAH Assistant utiliza PostgreSQL como base de dados principal, com Drizzle ORM para gestão de schemas e queries.

Visão Geral

A base de dados é organizada em schemas lógicos que representam os diferentes domínios da aplicação:

• Autenticação - Utilizadores, sessões, contas
• Chat - Conversas e mensagens
• Documentos - Biblioteca e embeddings
• Permissões - Roles e permissões
• Feedback - Avaliações e reportes

Schema de Autenticação

Tabela users

Armazena informações dos utilizadores:

export const users = pgTable("users", {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    email: text("email").notNull().unique(),
    emailVerified: boolean("email_verified").default(false),
    image: text("image"),
    createdAt: timestamp("created_at").defaultNow(),
    updatedAt: timestamp("updated_at").defaultNow(),
});

Tabela sessions

Gestão de sessões de utilizadores:

export const sessions = pgTable("sessions", {
    id: text("id").primaryKey(),
    userId: text("user_id")
        .notNull()
        .references(() => users.id, { onDelete: "cascade" }),
    token: text("token").notNull().unique(),
    expiresAt: timestamp("expires_at").notNull(),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    createdAt: timestamp("created_at").defaultNow(),
});

Tabela accounts

Contas OAuth e credenciais:

export const accounts = pgTable("accounts", {
    id: text("id").primaryKey(),
    userId: text("user_id")
        .notNull()
        .references(() => users.id, { onDelete: "cascade" }),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    expiresAt: timestamp("expires_at"),
    password: text("password"),
});

Schema de Chat

Tabela chats

Conversas dos utilizadores:

export const chats = pgTable("chats", {
    id: text("id").primaryKey(),
    userId: text("user_id")
        .notNull()
        .references(() => users.id, { onDelete: "cascade" }),
    title: text("title").default("Nova Conversa"),
    createdAt: timestamp("created_at").defaultNow(),
    updatedAt: timestamp("updated_at").defaultNow(),
});

Tabela messages

Mensagens individuais:

export const messages = pgTable("messages", {
    id: text("id").primaryKey(),
    chatId: text("chat_id")
        .notNull()
        .references(() => chats.id, { onDelete: "cascade" }),
    role: text("role").notNull(), // "user" | "assistant" | "system"
    content: text("content").notNull(),
    createdAt: timestamp("created_at").defaultNow(),
});

Schema de Documentos

Tabela documents

Documentos da biblioteca:

export const documents = pgTable("documents", {
    id: text("id").primaryKey(),
    title: text("title").notNull(),
    description: text("description"),
    fileName: text("file_name").notNull(),
    fileType: text("file_type").notNull(),
    fileSize: integer("file_size").notNull(),
    filePath: text("file_path").notNull(),
    uploadedBy: text("uploaded_by")
        .notNull()
        .references(() => users.id),
    isActive: boolean("is_active").default(true),
    createdAt: timestamp("created_at").defaultNow(),
    updatedAt: timestamp("updated_at").defaultNow(),
});

Tabela document_chunks

Chunks de documentos para RAG:

export const documentChunks = pgTable("document_chunks", {
    id: text("id").primaryKey(),
    documentId: text("document_id")
        .notNull()
        .references(() => documents.id, { onDelete: "cascade" }),
    content: text("content").notNull(),
    embedding: vector("embedding", { dimensions: 1024 }),
    metadata: jsonb("metadata"),
    chunkIndex: integer("chunk_index").notNull(),
    createdAt: timestamp("created_at").defaultNow(),
});

Schema de Permissões

Tabela roles

Definição de roles:

export const roles = pgTable("roles", {
    id: text("id").primaryKey(),
    name: text("name").notNull().unique(),
    description: text("description"),
    createdAt: timestamp("created_at").defaultNow(),
});

Tabela permissions

Permissões disponíveis:

export const permissions = pgTable("permissions", {
    id: text("id").primaryKey(),
    name: text("name").notNull().unique(),
    description: text("description"),
    resource: text("resource").notNull(),
    action: text("action").notNull(),
    createdAt: timestamp("created_at").defaultNow(),
});

Tabela user_roles

Associação utilizador-role:

export const userRoles = pgTable(
    "user_roles",
    {
        userId: text("user_id")
            .notNull()
            .references(() => users.id, { onDelete: "cascade" }),
        roleId: text("role_id")
            .notNull()
            .references(() => roles.id, { onDelete: "cascade" }),
    },
    (table) => ({
        pk: primaryKey({ columns: [table.userId, table.roleId] }),
    })
);

Diagrama ER

┌─────────────────┐       ┌─────────────────┐
│     users       │       │    sessions     │
├─────────────────┤       ├─────────────────┤
│ id (PK)         │──────<│ userId (FK)     │
│ name            │       │ id (PK)         │
│ email           │       │ token           │
│ emailVerified   │       │ expiresAt       │
│ image           │       └─────────────────┘
│ createdAt       │
│ updatedAt       │       ┌─────────────────┐
└────────┬────────┘       │    accounts     │
         │                ├─────────────────┤
         ├───────────────<│ userId (FK)     │
         │                │ id (PK)         │
         │                │ providerId      │
         │                │ password        │
         │                └─────────────────┘
         │
         │                ┌─────────────────┐
         ├───────────────<│     chats       │
         │                ├─────────────────┤
         │                │ userId (FK)     │
         │                │ id (PK)         │
         │                │ title           │
         │                └────────┬────────┘
         │                         │
         │                ┌────────┴────────┐
         │                │    messages     │
         │                ├─────────────────┤
         │                │ chatId (FK)     │
         │                │ id (PK)         │
         │                │ role            │
         │                │ content         │
         │                └─────────────────┘
         │
         │                ┌─────────────────┐
         └───────────────<│   user_roles    │
                          ├─────────────────┤
                          │ userId (FK)     │
                          │ roleId (FK)     │
                          └────────┬────────┘
                                   │
                          ┌────────┴────────┐
                          │     roles       │
                          ├─────────────────┤
                          │ id (PK)         │
                          │ name            │
                          └─────────────────┘

Migrações

As migrações são geridas pelo Drizzle Kit:

# Gerar nova migração após alterar schema
pnpm db:generate

# Aplicar migrações pendentes
pnpm db:migrate

# Verificar estado das migrações
pnpm db:check

# Abrir interface visual
pnpm db:studio

Ficheiros de Migração

As migrações ficam em drizzle/:

drizzle/
├── 0000_youthful_paper_doll.sql
├── 20250904143429_first_spiral.sql
├── ...
└── meta/
    ├── _journal.json
    └── *_snapshot.json

Seeds

Scripts para popular a base de dados com dados iniciais:

// drizzle/seeds/index.ts
import { seedUsers } from "./createUsersWithRoles";
import { seedRoles } from "./seedDB";

async function seed() {
    await seedRoles();
    await seedUsers();
    console.log("✅ Seed concluído");
}

seed();

Boas Práticas

1. Usar Transações

await db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ ... }).returning();
  await tx.insert(userRoles).values({ userId: user.id, roleId: "admin" });
});

2. Soft Deletes

Preferir isActive: false a deletar registos:

await db.update(documents).set({ isActive: false }).where(eq(documents.id, documentId));

3. Índices

Criar índices para queries frequentes:

export const documentsIdx = pgTable(
    "documents",
    {
        // ...
    },
    (table) => ({
        titleIdx: index("title_idx").on(table.title),
        userIdx: index("user_idx").on(table.uploadedBy),
    })
);

4. Validação com Zod

Usar Zod para validar dados antes de inserir:

const insertUserSchema = createInsertSchema(users);
const validatedData = insertUserSchema.parse(input);
await db.insert(users).values(validatedData);