Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

6.1 KB
Raw Blame
-- Test-only schema, loaded as the first fixture with `migrations = false`.
-- CI clones test databases from a pre-migrated template1 (`just setup_test_template`),
-- so start from an empty public schema.
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- NOTE: This is used purely to setup the testing DB
-- Changes from `crates/macro_db_client/migrations` need to be reflected here as raw SQL
-- Changes here WILL NOT ever make it into the dev/production db.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE "macro_user" (
    "id"                 UUID PRIMARY KEY,
    "username"           TEXT UNIQUE NOT NULL,
    "email"              TEXT UNIQUE NOT NULL,
    "stripe_customer_id" TEXT UNIQUE NOT NULL
);

CREATE TABLE "User" (
    "id"               TEXT PRIMARY KEY,
    "email"            TEXT UNIQUE NOT NULL,
    "name"             TEXT,
    "stripeCustomerId" TEXT UNIQUE,
    "macro_user_id"    UUID NOT NULL,
    FOREIGN KEY ("macro_user_id") REFERENCES "macro_user"("id")
);

CREATE TABLE "Permission" (
  "id" TEXT PRIMARY KEY,
  "description" TEXT
);

CREATE TABLE "Role" (
  "id" TEXT PRIMARY KEY,
  "description" TEXT
);

CREATE TABLE "RolesOnPermissions" (
  "permissionId" TEXT,
  "roleId" TEXT,
  PRIMARY KEY ("permissionId", "roleId"),
  CONSTRAINT "fk_permission" FOREIGN KEY ("permissionId") REFERENCES "Permission" ("id"),
  CONSTRAINT "fk_role_permissions" FOREIGN KEY ("roleId") REFERENCES "Role" ("id")
);

CREATE TABLE "RolesOnUsers" (
  "userId" TEXT,
  "roleId" TEXT,
  PRIMARY KEY ("userId", "roleId"),
  CONSTRAINT "fk_user" FOREIGN KEY ("userId") REFERENCES "User" ("id"),
  CONSTRAINT "fk_role_users" FOREIGN KEY ("roleId") REFERENCES "Role" ("id")
);

CREATE TABLE "DocumentFamily" (
    "id"           BIGSERIAL PRIMARY KEY,
    "rootDocumentId" TEXT NOT NULL
);

-- Document table
CREATE TABLE "Document" (
    -- Setting to type text as UUID was causing tests to fail and I didn't want to debug it
    "id"               TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
    "name"             TEXT NOT NULL,
    "owner"            TEXT NOT NULL,
    "fileType"         TEXT NOT NULL,
    -- Setting to type text as UUID was causing tests to fail and I didn't want to debug it
    "branchedFromId"   TEXT,
    "branchedFromVersionId" BIGINT,
    "documentFamilyId"   BIGINT,
    "createdAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY ("owner") REFERENCES "User"("id")
);

-- DocumentInstance table
CREATE TABLE "DocumentInstance" (
    "id"           BIGSERIAL PRIMARY KEY,
    "revisionName" TEXT ,
    -- Setting to type text as UUID was causing tests to fail and I didn't want to debug it
    "documentId"   TEXT NOT NULL,
    "createdAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "sha"          TEXT NOT NULL,
    FOREIGN KEY ("documentId") REFERENCES "Document"("id") ON DELETE CASCADE
);

CREATE TABLE "DocumentBom" (
    "id"           BIGSERIAL PRIMARY KEY,
    "revisionName" TEXT ,
    -- Setting to type text as UUID was causing tests to fail and I didn't want to debug it
    "documentId"   TEXT NOT NULL,
    "createdAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY ("documentId") REFERENCES "Document"("id") ON DELETE CASCADE
);

CREATE TABLE "BomPart" (
    "id"           TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
    "sha"          TEXT NOT NULL,
    "path"          TEXT NOT NULL,
    "documentBomId"   BIGSERIAL NOT NULL,
    "createdAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt"    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY ("documentBomId") REFERENCES "DocumentBom"("id") ON DELETE CASCADE
);

CREATE TABLE "DocumentProcessResult" (
    "id"           BIGSERIAL PRIMARY KEY,
    "sha"          TEXT NOT NULL,
    "jobType"          TEXT NOT NULL,
    "content"          TEXT NOT NULL
);

CREATE TABLE "JobToDocumentProcessResult" (
    "jobId"           TEXT NOT NULL,
    "documentProcessResultId"   BIGSERIAL NOT NULL,
    FOREIGN KEY ("documentProcessResultId") REFERENCES "DocumentProcessResult"("id") ON DELETE CASCADE
);

CREATE TABLE "OpenedFileEvent" (
    "userId" TEXT NOT NULL,
    "documentId" TEXT NOT NULL,
    "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE,
    FOREIGN KEY ("documentId") REFERENCES "Document"("id") ON DELETE CASCADE,
    UNIQUE ("userId", "documentId")
);

CREATE TABLE "Pin" (
    "userId" TEXT NOT NULL,
    "pinnedItemId" TEXT NOT NULL,
    "pinnedItemType" TEXT NOT NULL,
    "pinIndex" INTEGER NOT NULL,
    "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE,
    UNIQUE ("userId", "pinnedItemId", "pinnedItemType")
);

CREATE TABLE "DocxUploadJob" (
    "id"           BIGSERIAL PRIMARY KEY,
    "jobId"          TEXT NOT NULL,
    "documentId"          TEXT,
    "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "Chat" (
    "id"               TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
    "name"             TEXT NOT NULL,
    "userId"            TEXT NOT NULL,
    "createdAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY ("userId") REFERENCES "User"("id")
);

CREATE TABLE "ChatMessage" (
    "id"           BIGSERIAL PRIMARY KEY,
    "content" TEXT NOT NULL,
    "role" TEXT NOT NULL,
    "chatId"   TEXT NOT NULL,
    FOREIGN KEY ("chatId") REFERENCES "Chat"("id") ON DELETE CASCADE
);

CREATE TABLE "ChatUpload" (
    "id"               TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
    "name"             TEXT NOT NULL,
    "sha"              TEXT NOT NULL,
    "fileType"         TEXT NOT NULL,
    "chatMessageId"    BIGSERIAL NOT NULL,
    "createdAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY ("chatMessageId") REFERENCES "ChatMessage"("id") ON DELETE CASCADE
);