💾 Database Schema

Database structure for Aeternus.

PostgreSQL Tables

Users

CREATE TABLE "User" (
  id STRING PRIMARY KEY,
  email STRING UNIQUE,
  name STRING,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Conversations

CREATE TABLE "Conversation" (
  id STRING PRIMARY KEY,
  user_id STRING REFERENCES "User"(id),
  title STRING,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Messages

CREATE TABLE "Message" (
  id STRING PRIMARY KEY,
  conversation_id STRING REFERENCES "Conversation"(id),
  role STRING,  -- 'user' or 'assistant'
  content STRING,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Feedback

CREATE TABLE "Feedback" (
  id STRING PRIMARY KEY,
  user_id STRING REFERENCES "User"(id),
  conversation_id STRING REFERENCES "Conversation"(id),
  rating INTEGER,  -- 1-5
  comment STRING,
  created_at TIMESTAMP
);

---

Neo4j Graph (Optional)

Neo4j stores relationships between entities:

• User → Conversation
• Conversation → Message
• Message → Entity (knowledge graph)
• Entity → Related Entity

---

Prisma ORM

See prisma/schema.prisma for the canonical schema definition.

# Create a migration after schema changes
npx prisma migrate dev --name your_change_name

# View database
npx prisma studio

# Reset database (danger!)
npx prisma migrate reset

---

Backups

• PostgreSQL should be backed up regularly
• Use AWS RDS automated backups in production
• Neo4j has its own backup mechanism

For more details, see Setup Local.