71 lines
2.7 KiB
TypeScript
71 lines
2.7 KiB
TypeScript
import {
|
|
pgTable,
|
|
uuid,
|
|
text,
|
|
timestamp,
|
|
jsonb,
|
|
index,
|
|
unique,
|
|
} from "drizzle-orm/pg-core";
|
|
import { companies } from "./companies.js";
|
|
import { plugins } from "./plugins.js";
|
|
import type { PluginStateScopeKind } from "@paperclipai/shared";
|
|
|
|
/**
|
|
* `plugin_entities` table — persistent high-level mapping between Paperclip
|
|
* objects and external plugin-defined entities.
|
|
*
|
|
* This table is used by plugins (e.g. `linear`, `github`) to store pointers
|
|
* to their respective external IDs for projects, issues, etc. and to store
|
|
* their custom data.
|
|
*
|
|
* Unlike `plugin_state`, which is for raw K-V persistence, `plugin_entities`
|
|
* is intended for structured object mappings that the host can understand
|
|
* and query for cross-plugin UI integration.
|
|
*
|
|
* @see PLUGIN_SPEC.md §21.3
|
|
*/
|
|
export const pluginEntities = pgTable(
|
|
"plugin_entities",
|
|
{
|
|
id: uuid("id").primaryKey().defaultRandom(),
|
|
pluginId: uuid("plugin_id")
|
|
.notNull()
|
|
.references(() => plugins.id, { onDelete: "cascade" }),
|
|
/** Company scope — NULL for instance-level entities. */
|
|
companyId: uuid("company_id").references(() => companies.id, { onDelete: "cascade" }),
|
|
entityType: text("entity_type").notNull(),
|
|
scopeKind: text("scope_kind").$type<PluginStateScopeKind>().notNull(),
|
|
scopeId: text("scope_id"), // NULL for global scope (text to match plugin_state.scope_id)
|
|
externalId: text("external_id"), // ID in the external system
|
|
title: text("title"),
|
|
status: text("status"),
|
|
data: jsonb("data").$type<Record<string, unknown>>().notNull().default({}),
|
|
createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
|
|
updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
|
|
},
|
|
(table) => ({
|
|
pluginIdx: index("plugin_entities_plugin_idx").on(table.pluginId),
|
|
companyIdx: index("plugin_entities_company_idx").on(table.companyId),
|
|
typeIdx: index("plugin_entities_type_idx").on(table.entityType),
|
|
scopeIdx: index("plugin_entities_scope_idx").on(table.scopeKind, table.scopeId),
|
|
/**
|
|
* Per-tenant uniqueness on (companyId, pluginId, entityType, externalId).
|
|
* `.nullsNotDistinct()` is required because companyId is nullable for
|
|
* instance-scope entities (cron jobs, public webhooks): without it,
|
|
* postgres treats two NULL company_ids as distinct and a tuple like
|
|
* `(NULL, pluginId, entityType, externalId)` can be inserted multiple
|
|
* times, losing the dedup guarantee. Same pattern as plugin_state.ts.
|
|
* Requires PostgreSQL 15+.
|
|
*/
|
|
externalIdx: unique("plugin_entities_external_idx")
|
|
.on(
|
|
table.companyId,
|
|
table.pluginId,
|
|
table.entityType,
|
|
table.externalId,
|
|
)
|
|
.nullsNotDistinct(),
|
|
}),
|
|
);
|