nuxt4-demo/packages/drizzle-pkg/database/sqlite/schema/content.ts

import {
  index,
  integer,
  primaryKey,
  sqliteTable,
  text,
  uniqueIndex,
  type AnySQLiteColumn,
} from "drizzle-orm/sqlite-core";

import { users } from "./auth";

export const posts = sqliteTable(
  "posts",
  {
    id: integer().primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    title: text().notNull(),
    slug: text().notNull(),
    bodyMarkdown: text("body_markdown").notNull(),
    excerpt: text().notNull(),
    coverUrl: text("cover_url"),
    tagsJson: text("tags_json").notNull().default("[]"),
    publishedAt: integer("published_at", { mode: "timestamp_ms" }),
    visibility: text().notNull().default("private"),
    shareToken: text("share_token"),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).defaultNow().notNull(),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" })
      .defaultNow()
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [uniqueIndex("posts_user_id_slug_unique").on(table.userId, table.slug)],
);

export const mediaAssets = sqliteTable(
  "media_assets",
  {
    id: integer().primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    storageKey: text("storage_key").notNull(),
    mime: text().notNull(),
    sizeBytes: integer("size_bytes").notNull(),
    sha256: text("sha256"),
    variantsJson: text("variants_json"),
    status: text().notNull().default("ready"),
    firstReferencedAt: integer("first_referenced_at", { mode: "timestamp_ms" }),
    dereferencedAt: integer("dereferenced_at", { mode: "timestamp_ms" }),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("media_assets_storage_key_unique").on(table.storageKey),
    index("media_assets_user_id_idx").on(table.userId),
  ],
);

export const mediaRefs = sqliteTable(
  "media_refs",
  {
    ownerType: text("owner_type").notNull(),
    ownerId: integer("owner_id").notNull(),
    assetId: integer("asset_id")
      .notNull()
      .references(() => mediaAssets.id, { onDelete: "cascade" }),
  },
  (table) => [
    primaryKey({ columns: [table.ownerType, table.ownerId, table.assetId] }),
    index("media_refs_asset_id_idx").on(table.assetId),
  ],
);

export const timelineEvents = sqliteTable("timeline_events", {
  id: integer().primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  occurredOn: integer("occurred_on", { mode: "timestamp_ms" }).notNull(),
  title: text().notNull(),
  bodyMarkdown: text("body_markdown"),
  linkUrl: text("link_url"),
  visibility: text().notNull().default("private"),
  shareToken: text("share_token"),
  createdAt: integer("created_at", { mode: "timestamp_ms" }).defaultNow().notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp_ms" })
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
});

export const postComments = sqliteTable(
  "post_comments",
  {
    id: integer().primaryKey(),
    postId: integer("post_id")
      .notNull()
      .references(() => posts.id, { onDelete: "cascade" }),
    parentId: integer("parent_id").references((): AnySQLiteColumn => postComments.id),
    authorUserId: integer("author_user_id").references(() => users.id, { onDelete: "set null" }),
    guestDisplayName: text("guest_display_name"),
    body: text().notNull(),
    kind: text().notNull(),
    deletedAt: integer("deleted_at", { mode: "timestamp_ms" }),
    deletedByUserId: integer("deleted_by_user_id").references(() => users.id, {
      onDelete: "set null",
    }),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).defaultNow().notNull(),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" })
      .defaultNow()
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [
    index("post_comments_post_id_idx").on(table.postId),
    index("post_comments_parent_id_idx").on(table.parentId),
  ],
);