lib/db/src/schema/userMemoryFacts.ts

import {
  pgTable,
  text,
  timestamp,
  integer,
  real,
  index,
} from "drizzle-orm/pg-core";
import { users } from "./users";

/**
 * Long-term, cross-session memory facts about the user.
 *
 * Populated either by the model (auto-extraction via the `<memory_fact>`
 * supervisor tag) or by the user (explicit "remember this" action /
 * Settings UI). Caps are enforced server-side regardless of UI: at most
 * 100 facts per user, ~2000 tokens injected per turn, and at most 5 new
 * auto-extracted facts per turn (manual creates always succeed up to the
 * 100-fact cap, evicting the lowest-salience oldest fact when full).
 *
 * The `normalized` column is a lowercased, whitespace-collapsed,
 * punctuation-stripped form of `content` used to dedupe near-identical
 * facts: re-emitting the same fact bumps `useCount` and refreshes
 * `salience` instead of inserting a duplicate row.
 */
export const userMemoryFacts = pgTable(
  "user_memory_facts",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    /** preference | fact | interest | domain | terminology | summary */
    kind: text("kind").notNull().default("fact"),
    /** Raw user-visible fact text (≤500 chars). */
    content: text("content").notNull(),
    /** Lowercased / whitespace-collapsed dedupe key. */
    normalized: text("normalized").notNull(),
    /** Stable SHA-256 hex of `normalized` for fast / index-friendly dedupe. */
    contentHash: text("content_hash").notNull().default(""),
    /** Soft-archive flag: archived facts are excluded from listing/ranking
     *  but are kept on disk so users can restore them. Eviction archives
     *  rather than hard-deletes; the explicit "Forget" action does delete. */
    archived: integer("archived").notNull().default(0),
    /** Model self-reported confidence at extraction time (0..1). */
    confidence: real("confidence").notNull().default(0.7),
    /** Ranking weight for per-turn injection (0..1). Manual = 0.9. */
    salience: real("salience").notNull().default(0.5),
    /** "auto" (model emitted) or "manual" (user clicked remember). */
    source: text("source").notNull().default("auto"),
    /** Originating message (nullable for fully manual facts). */
    sourceMessageId: text("source_message_id"),
    /** Originating conversation (nullable). */
    conversationId: text("conversation_id"),
    /** Number of turns the fact was injected into. */
    useCount: integer("use_count").notNull().default(0),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
  },
  (t) => ({
    userIdx: index("user_memory_facts_user_idx").on(t.userId),
    normalizedIdx: index("user_memory_facts_normalized_idx").on(
      t.userId,
      t.normalized,
    ),
    contentHashIdx: index("user_memory_facts_content_hash_idx").on(
      t.userId,
      t.contentHash,
    ),
    archivedIdx: index("user_memory_facts_archived_idx").on(
      t.userId,
      t.archived,
    ),
  }),
);

export type UserMemoryFact = typeof userMemoryFacts.$inferSelect;
export type InsertUserMemoryFact = typeof userMemoryFacts.$inferInsert;