Implementing Audit Logging
1. Adding Created Timestamp
| Field | Detail |
|---|---|
| createdAt: DateTime! | Set on insert; immutable |
| DB default | DEFAULT now() or trigger |
2. Adding Updated Timestamp
| Field | Detail |
|---|---|
| updatedAt: DateTime! | Set on every update |
| ORM hooks | Prisma @updatedAt, TypeORM @UpdateDateColumn |
3. Adding Created By Field
Example: Created/updated by
type Post {
createdAt: DateTime!
updatedAt: DateTime!
createdBy: User!
updatedBy: User
}4. Adding Updated By Field
| Source | Detail |
|---|---|
| Context user | Set in resolver from ctx.user.id |
| DB session var | Postgres SET LOCAL app.user_id = ... |
| ORM middleware | Auto-fill on save |
5. Logging Field Changes
Example: Diff before/after
const before = await db.post.findUnique({ where: { id } });
const after = await db.post.update({ where: { id }, data: input });
const diff = computeDiff(before, after);
await db.auditLog.create({
data: { entity: "Post", entityId: id, actor: ctx.user.id, op: "UPDATE", diff }
});6. Implementing Audit Trail
| Column | Use |
|---|---|
| id, occurredAt, actor | Who, when |
| entity, entityId | What was changed |
| operation | CREATE, UPDATE, DELETE, READ |
| before, after | JSON snapshots |
| requestId, ip, userAgent | Request context |
7. Storing Change Metadata
| Storage | Use |
|---|---|
| Same DB, separate table | Simple, transactional |
| Append-only log | Kafka, S3 — immutable |
| Time-series DB | Clickhouse, TimescaleDB |
8. Querying Audit Logs
Example: Audit log query
type Query {
auditLog(
entity: String
entityId: ID
actor: ID
dateRange: DateFilter
first: Int = 50
after: String
): AuditLogConnection!
}9. Implementing Version History
| Pattern | Detail |
|---|---|
| Version table | Each row is a snapshot |
| Bitemporal | Track validFrom/validTo + transactionTime |
| Diff-only | Store JSON-Patch per change |
10. Using Event Sourcing
| Concept | Detail |
|---|---|
| Events | Source of truth: PostCreated, PostEdited |
| Projections | Read models built by replaying events |
| CQRS | Mutations write events; queries read projections |
| Trade-off | Higher complexity; great for audit + replay |