forked from BRETT/BRETT
kibiandClaude Opus 5.5
06ac414457
New audit_log table records actor, action, target and a JSON snapshot of the details, so entries stay readable after the target is deleted; the actor is set to NULL when the user is removed. logAudit() is called from upload delete (single and bulk), comment delete, tag removal, role create/delete, role permission changes, role assign/revoke and MOTD updates. Logging failures only warn and never fail the action itself. The admin page gets an audit card behind the new audit.view permission with action filter, click-to-filter by user, readable summaries per action and server-side pagination. Action catalog lives in shared/utils/audit.ts, labels under admin.auditActions (de/en). Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
238 lines
12 KiB
TypeScript
238 lines
12 KiB
TypeScript
import { relations } from 'drizzle-orm'
|
|
import { bigint, boolean, char, index, int, json, mysqlEnum, mysqlTable, primaryKey, text, timestamp, varchar } from 'drizzle-orm/mysql-core'
|
|
|
|
export const users = mysqlTable('users', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
name: varchar('name', { length: 50 }).notNull().unique(),
|
|
email: varchar('email', { length: 255 }).unique(),
|
|
passwordHash: varchar('password_hash', { length: 255 }).notNull(),
|
|
color: varchar('color', { length: 7 }),
|
|
avatar: varchar('avatar', { length: 255 }),
|
|
cardEffect: varchar('card_effect', { length: 32 }),
|
|
cardEffectColor: varchar('card_effect_color', { length: 7 }),
|
|
cardVignette: boolean('card_vignette').default(false).notNull(),
|
|
cardBorderEffect: varchar('card_border_effect', { length: 32 }),
|
|
cardBorderEffectColor: varchar('card_border_effect_color', { length: 7 }),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const sessions = mysqlTable('sessions', {
|
|
id: varchar('id', { length: 64 }).primaryKey(),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
expiresAt: timestamp('expires_at').notNull(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const uploads = mysqlTable('uploads', {
|
|
id: varchar('id', { length: 8 }).primaryKey(),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
type: mysqlEnum('type', ['image', 'video', 'audio', 'youtube', 'twitch', 'link']).notNull(),
|
|
fileName: varchar('file_name', { length: 255 }),
|
|
externalUrl: varchar('external_url', { length: 2048 }),
|
|
originalName: varchar('original_name', { length: 255 }).notNull(),
|
|
mimeType: varchar('mime_type', { length: 100 }),
|
|
size: bigint('size', { mode: 'number', unsigned: true }),
|
|
title: varchar('title', { length: 255 }),
|
|
previewImage: varchar('preview_image', { length: 2048 }),
|
|
previewTitle: varchar('preview_title', { length: 500 }),
|
|
audioMeta: json('audio_meta'),
|
|
commentCount: int('comment_count', { unsigned: true }).notNull().default(0),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const comments = mysqlTable('comments', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
content: text('content').notNull(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const commentAttachments = mysqlTable('comment_attachments', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
commentId: bigint('comment_id', { mode: 'number', unsigned: true }).notNull().references(() => comments.id, { onDelete: 'cascade' }),
|
|
fileName: varchar('file_name', { length: 255 }).notNull(),
|
|
originalName: varchar('original_name', { length: 255 }).notNull(),
|
|
mimeType: varchar('mime_type', { length: 100 }),
|
|
size: bigint('size', { mode: 'number', unsigned: true }),
|
|
cover: varchar('cover', { length: 2048 }),
|
|
meta: json('meta'),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const commentReactions = mysqlTable('comment_reactions', {
|
|
commentId: bigint('comment_id', { mode: 'number', unsigned: true }).notNull().references(() => comments.id, { onDelete: 'cascade' }),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
emoji: varchar('emoji', { length: 32 }).notNull(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
}, t => [primaryKey({ columns: [t.commentId, t.userId, t.emoji] })])
|
|
|
|
export const uploadReactions = mysqlTable('upload_reactions', {
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
emoji: varchar('emoji', { length: 32 }).notNull(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
}, t => [primaryKey({ columns: [t.uploadId, t.userId, t.emoji] })])
|
|
|
|
export const favorites = mysqlTable('favorites', {
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
}, t => [primaryKey({ columns: [t.userId, t.uploadId] })])
|
|
|
|
export const threadSubscriptions = mysqlTable('thread_subscriptions', {
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
enabled: boolean('enabled').notNull().default(true),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
}, t => [primaryKey({ columns: [t.uploadId, t.userId] })])
|
|
|
|
export const linkPreviews = mysqlTable('link_previews', {
|
|
urlHash: char('url_hash', { length: 64 }).primaryKey(),
|
|
url: varchar('url', { length: 2048 }).notNull(),
|
|
image: varchar('image', { length: 2048 }),
|
|
title: varchar('title', { length: 500 }),
|
|
fetchedAt: timestamp('fetched_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const notifications = mysqlTable('notifications', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
actorId: bigint('actor_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
commentId: bigint('comment_id', { mode: 'number', unsigned: true }).notNull().references(() => comments.id, { onDelete: 'cascade' }),
|
|
isRead: boolean('is_read').notNull().default(false),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const roles = mysqlTable('roles', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
name: varchar('name', { length: 50 }).notNull().unique(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const userRoles = mysqlTable('user_roles', {
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).notNull().references(() => users.id, { onDelete: 'cascade' }),
|
|
roleId: bigint('role_id', { mode: 'number', unsigned: true }).notNull().references(() => roles.id, { onDelete: 'cascade' }),
|
|
}, t => [primaryKey({ columns: [t.userId, t.roleId] })])
|
|
|
|
export const permissions = mysqlTable('permissions', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
key: varchar('key', { length: 50 }).notNull().unique(),
|
|
label: varchar('label', { length: 255 }).notNull(),
|
|
})
|
|
|
|
export const rolePermissions = mysqlTable('role_permissions', {
|
|
roleId: bigint('role_id', { mode: 'number', unsigned: true }).notNull().references(() => roles.id, { onDelete: 'cascade' }),
|
|
permissionId: bigint('permission_id', { mode: 'number', unsigned: true }).notNull().references(() => permissions.id, { onDelete: 'cascade' }),
|
|
}, t => [primaryKey({ columns: [t.roleId, t.permissionId] })])
|
|
|
|
export const tags = mysqlTable('tags', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
name: varchar('name', { length: 64 }).notNull().unique(),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
})
|
|
|
|
export const uploadTags = mysqlTable('upload_tags', {
|
|
uploadId: varchar('upload_id', { length: 8 }).notNull().references(() => uploads.id, { onDelete: 'cascade' }),
|
|
tagId: bigint('tag_id', { mode: 'number', unsigned: true }).notNull().references(() => tags.id, { onDelete: 'cascade' }),
|
|
}, t => [primaryKey({ columns: [t.uploadId, t.tagId] })])
|
|
|
|
// Protokoll von Moderations- und Admin-Aktionen. Der Akteur bleibt beim
|
|
// Löschen des Nutzers als NULL erhalten, Details als JSON-Snapshot, damit
|
|
// Einträge auch nach dem Löschen des Ziels lesbar bleiben
|
|
export const auditLog = mysqlTable('audit_log', {
|
|
id: bigint('id', { mode: 'number', unsigned: true }).autoincrement().primaryKey(),
|
|
userId: bigint('user_id', { mode: 'number', unsigned: true }).references(() => users.id, { onDelete: 'set null' }),
|
|
action: varchar('action', { length: 50 }).notNull(),
|
|
targetType: varchar('target_type', { length: 32 }),
|
|
targetId: varchar('target_id', { length: 64 }),
|
|
details: json('details'),
|
|
createdAt: timestamp('created_at').defaultNow().notNull(),
|
|
}, t => [index('audit_log_action_idx').on(t.action)])
|
|
|
|
// Generischer Key-Value-Store für Seiten-Einstellungen (z. B. MOTD)
|
|
export const settings = mysqlTable('settings', {
|
|
key: varchar('key', { length: 100 }).primaryKey(),
|
|
value: text('value'),
|
|
})
|
|
|
|
export const usersRelations = relations(users, ({ many }) => ({
|
|
sessions: many(sessions),
|
|
uploads: many(uploads),
|
|
comments: many(comments),
|
|
userRoles: many(userRoles),
|
|
commentReactions: many(commentReactions),
|
|
uploadReactions: many(uploadReactions),
|
|
threadSubscriptions: many(threadSubscriptions),
|
|
}))
|
|
|
|
export const rolesRelations = relations(roles, ({ many }) => ({
|
|
userRoles: many(userRoles),
|
|
rolePermissions: many(rolePermissions),
|
|
}))
|
|
|
|
export const userRolesRelations = relations(userRoles, ({ one }) => ({
|
|
user: one(users, { fields: [userRoles.userId], references: [users.id] }),
|
|
role: one(roles, { fields: [userRoles.roleId], references: [roles.id] }),
|
|
}))
|
|
|
|
export const sessionsRelations = relations(sessions, ({ one }) => ({
|
|
user: one(users, { fields: [sessions.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const uploadsRelations = relations(uploads, ({ one, many }) => ({
|
|
user: one(users, { fields: [uploads.userId], references: [users.id] }),
|
|
comments: many(comments),
|
|
favorites: many(favorites),
|
|
reactions: many(uploadReactions),
|
|
threadSubscriptions: many(threadSubscriptions),
|
|
}))
|
|
|
|
export const commentsRelations = relations(comments, ({ one, many }) => ({
|
|
upload: one(uploads, { fields: [comments.uploadId], references: [uploads.id] }),
|
|
user: one(users, { fields: [comments.userId], references: [users.id] }),
|
|
reactions: many(commentReactions),
|
|
attachments: many(commentAttachments),
|
|
}))
|
|
|
|
export const commentAttachmentsRelations = relations(commentAttachments, ({ one }) => ({
|
|
comment: one(comments, { fields: [commentAttachments.commentId], references: [comments.id] }),
|
|
}))
|
|
|
|
export const commentReactionsRelations = relations(commentReactions, ({ one }) => ({
|
|
comment: one(comments, { fields: [commentReactions.commentId], references: [comments.id] }),
|
|
user: one(users, { fields: [commentReactions.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const uploadReactionsRelations = relations(uploadReactions, ({ one }) => ({
|
|
upload: one(uploads, { fields: [uploadReactions.uploadId], references: [uploads.id] }),
|
|
user: one(users, { fields: [uploadReactions.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const favoritesRelations = relations(favorites, ({ one }) => ({
|
|
upload: one(uploads, { fields: [favorites.uploadId], references: [uploads.id] }),
|
|
user: one(users, { fields: [favorites.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const rolePermissionsRelations = relations(rolePermissions, ({ one }) => ({
|
|
role: one(roles, { fields: [rolePermissions.roleId], references: [roles.id] }),
|
|
permission: one(permissions, { fields: [rolePermissions.permissionId], references: [permissions.id] }),
|
|
}))
|
|
|
|
export const threadSubscriptionsRelations = relations(threadSubscriptions, ({ one }) => ({
|
|
upload: one(uploads, { fields: [threadSubscriptions.uploadId], references: [uploads.id] }),
|
|
user: one(users, { fields: [threadSubscriptions.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const auditLogRelations = relations(auditLog, ({ one }) => ({
|
|
user: one(users, { fields: [auditLog.userId], references: [users.id] }),
|
|
}))
|
|
|
|
export const notificationsRelations = relations(notifications, ({ one }) => ({
|
|
user: one(users, { fields: [notifications.userId], references: [users.id] }),
|
|
actor: one(users, { fields: [notifications.actorId], references: [users.id] }),
|
|
upload: one(uploads, { fields: [notifications.uploadId], references: [uploads.id] }),
|
|
comment: one(comments, { fields: [notifications.commentId], references: [comments.id] }),
|
|
}))
|