Content Service

CREATE TABLE page (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id    UUID         NOT NULL,
  slug        VARCHAR(255) NOT NULL,
  status      VARCHAR(16)  NOT NULL DEFAULT 'DRAFT'
                CHECK (status IN ('DRAFT', 'ACTIVE', 'ARCHIVED')),
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  UNIQUE (store_id, slug)
);

CREATE TABLE page_translation (
  page_id     UUID         NOT NULL REFERENCES page(id) ON DELETE CASCADE,
  locale      CHAR(5)      NOT NULL,
  title       VARCHAR(512) NOT NULL,
  body_html   TEXT,
  meta_title  VARCHAR(255),
  meta_desc   VARCHAR(512),
  PRIMARY KEY (page_id, locale)
);

CREATE TABLE blog (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id    UUID         NOT NULL,
  slug        VARCHAR(255) NOT NULL,
  UNIQUE (store_id, slug)
);

CREATE TABLE blog_translation (
  blog_id     UUID         NOT NULL REFERENCES blog(id) ON DELETE CASCADE,
  locale      CHAR(5)      NOT NULL,
  name        VARCHAR(255) NOT NULL,
  PRIMARY KEY (blog_id, locale)
);

CREATE TABLE blog_post (
  id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  blog_id      UUID         NOT NULL REFERENCES blog(id) ON DELETE CASCADE,
  store_id     UUID         NOT NULL,
  slug         VARCHAR(255) NOT NULL,
  status       VARCHAR(16)  NOT NULL DEFAULT 'DRAFT'
                 CHECK (status IN ('DRAFT', 'ACTIVE', 'ARCHIVED')),
  author_id    UUID,                              -- principal UUID from AGM
  published_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
  UNIQUE (store_id, slug)
);

CREATE TABLE blog_post_translation (
  post_id     UUID         NOT NULL REFERENCES blog_post(id) ON DELETE CASCADE,
  locale      CHAR(5)      NOT NULL,
  title       VARCHAR(512) NOT NULL,
  body_html   TEXT,
  excerpt     TEXT,
  meta_title  VARCHAR(255),
  meta_desc   VARCHAR(512),
  PRIMARY KEY (post_id, locale)
);

CREATE TABLE store_file (
  id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id      UUID         NOT NULL,
  filename      VARCHAR(512) NOT NULL,
  s3_key        TEXT         NOT NULL,
  content_type  VARCHAR(128) NOT NULL,
  size_bytes    BIGINT       NOT NULL,
  cdn_url       TEXT,                             -- CloudFront URL after propagation
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE sandbox (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id    UUID         NOT NULL,
  name        VARCHAR(255) NOT NULL,
  status      VARCHAR(16)  NOT NULL DEFAULT 'DRAFT'
                CHECK (status IN ('DRAFT', 'PENDING_APPROVAL', 'APPROVED', 'PUBLISHED', 'ROLLED_BACK')),
  created_by  UUID         NOT NULL,              -- principal UUID
  approved_by UUID,
  scheduled_at TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Each change within a sandbox, keyed by owning service + entity
CREATE TABLE sandbox_change (
  id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  sandbox_id   UUID         NOT NULL REFERENCES sandbox(id) ON DELETE CASCADE,
  service      VARCHAR(64)  NOT NULL,             -- e.g. "catalog", "content", "tax"
  entity_type  VARCHAR(64)  NOT NULL,
  entity_id    UUID         NOT NULL,
  diff         JSONB        NOT NULL,             -- JSON Patch (RFC 6902)
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ON sandbox_change (sandbox_id, service, entity_type, entity_id);

-- Sandbox audit log
CREATE TABLE sandbox_event (
  id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  sandbox_id   UUID         NOT NULL REFERENCES sandbox(id) ON DELETE CASCADE,
  action       VARCHAR(32)  NOT NULL,
  actor_id     UUID         NOT NULL,
  note         TEXT,
  occurred_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

service ContentReaderService {
  rpc GetPage(GetPageRequest) returns (PageResponse);
  rpc GetBlogPost(GetBlogPostRequest) returns (BlogPostResponse);
  rpc GetSandboxPreviewToken(GetSandboxPreviewTokenRequest) returns (PreviewTokenResponse);
}

service SandboxParticipant {
  rpc ApplySandbox(ApplySandboxRequest) returns (ApplySandboxResponse);
  rpc RollbackSandbox(RollbackSandboxRequest) returns (RollbackSandboxResponse);
}