Schema config.* — Configurações

Localização e Responsabilidade

O schema config reside no Policy DB (PostgreSQL), co-localizado com o schema policy.*. O Policy Service é o único escritor; o BFF do Back Office acede exclusivamente via API REST do Policy Service.

Aspecto	Detalhe
Base de dados	Policy DB
Owner	Policy Service
Consumidores	Back Office BFF (escrita via API), Returns Service (leitura via cache Redis)
Estratégia de deleção	Soft delete via is_active
Audit trail	Tabela audit_log append-only
Multi-tenancy	tenant_id em todas as tabelas
PKs	uuid

Visão Geral — 13 Tabelas

config.*
│
├── [Tenant & Scalar]
│   ├── tenant                    — insígnias suportadas
│   ├── tenant_settings           — N artigos, threshold geral, threshold CAV
│   └── profile_usage_limit       — limite de devoluções por perfil
│
├── [Motivos & Atributos]
│   ├── return_reason             — motivos de devolução
│   ├── reason_attribute          — atributos por motivo
│   ├── reason_attribute_option   — opções de dropdown
│   └── reason_attribute_profile  — obrigatoriedade por perfil
│
├── [Artigos Excluídos]
│   └── partial_return_exclusion  — SKUs excluídos de devolução parcial
│
├── [Métodos & Mensagens]
│   ├── refund_method_config      — configurador de reembolso por método
│   ├── message_template          — mensagens de Etapa 3/4/5 + email/SMS
│   └── store_owner_mapping       — mapeamento loja → owner Perto
│
└── [Transversal]
    ├── audit_log                 — registo imutável de todas as mutações
    └── section_permission        — controlo Admin/User por secção

Modelo de Entidades

Grupo 1 — Tenant & Scalar Settings

config.tenant

tenant_id   uuid           PRIMARY KEY
code        varchar(64)    NOT NULL UNIQUE   -- "COL", "CONTINENTE"
name        varchar(120)   NOT NULL
is_active   bool           NOT NULL DEFAULT true
created_at  timestamptz    NOT NULL
updated_at  timestamptz    NOT NULL
updated_by  varchar(120)   NOT NULL

config.tenant_settings

Relação 1:1 com tenant. Parâmetros escalares por insígnia.

tenant_id             uuid           PRIMARY KEY REFERENCES config.tenant
n_articles_threshold  int            NOT NULL DEFAULT 3
general_threshold     numeric(12,2)  NOT NULL
cav_threshold         numeric(12,2)  NULL       -- NULL → cliente CAV segue fluxo geral (AC-179)
updated_at            timestamptz    NOT NULL
updated_by            varchar(120)   NOT NULL

config.profile_usage_limit

limit_id      uuid         PRIMARY KEY
tenant_id     uuid         NOT NULL REFERENCES config.tenant
profile_code  varchar(64)  NOT NULL   -- "SELF_SERVICE" | "CC_COL" | "SAC_LOJA"
limit_type    varchar(16)  NOT NULL   -- "inactive" | "unlimited" | "fixed"
limit_value   int          NULL       -- obrigatório quando limit_type = 'fixed'
updated_at    timestamptz  NOT NULL
updated_by    varchar(120) NOT NULL

UNIQUE (tenant_id, profile_code)
CHECK (limit_type <> 'fixed' OR limit_value IS NOT NULL)

---

Grupo 2 — Motivos de Devolução & Atributos

config.return_reason

reason_id   uuid         PRIMARY KEY
tenant_id   uuid         NOT NULL REFERENCES config.tenant
code        varchar(64)  NOT NULL   -- imutável: "CHANGED_MIND" | "PRODUCT_QUALITY" | "OPERATION_QUALITY" | "NO_SUBSTITUTION_INTEREST"
nickname    varchar(120) NOT NULL   -- label editável exibido ao utilizador
is_active   bool         NOT NULL DEFAULT true
sort_order  int          NOT NULL DEFAULT 0
created_at  timestamptz  NOT NULL
updated_at  timestamptz  NOT NULL
updated_by  varchar(120) NOT NULL

UNIQUE (tenant_id, code)

config.reason_attribute

attribute_id    uuid         PRIMARY KEY
reason_id       uuid         NOT NULL REFERENCES config.return_reason
name            varchar(120) NOT NULL
attribute_type  varchar(32)  NOT NULL   -- "text" | "numeric" | "textarea" | "dropdown" | "datepicker" | "file_upload"
is_active       bool         NOT NULL DEFAULT true
sort_order      int          NOT NULL DEFAULT 0
created_at      timestamptz  NOT NULL
updated_at      timestamptz  NOT NULL
updated_by      varchar(120) NOT NULL

config.reason_attribute_option

Opções de dropdown. Só relevante quando attribute_type = 'dropdown'.

option_id     uuid         PRIMARY KEY
attribute_id  uuid         NOT NULL REFERENCES config.reason_attribute
label         varchar(120) NOT NULL
sort_order    int          NOT NULL DEFAULT 0
is_active     bool         NOT NULL DEFAULT true

config.reason_attribute_profile

Obrigatoriedade de cada atributo por perfil.

attribute_id  uuid         NOT NULL REFERENCES config.reason_attribute
profile_code  varchar(64)  NOT NULL   -- "ALL" | "SELF_SERVICE" | "CC_COL" | "SAC_LOJA"
is_required   bool         NOT NULL DEFAULT false

PRIMARY KEY (attribute_id, profile_code)

---

Grupo 3 — Artigos Excluídos de Devolução Parcial

config.partial_return_exclusion

exclusion_id  uuid         PRIMARY KEY
tenant_id     uuid         NOT NULL REFERENCES config.tenant
sku           varchar(64)  NOT NULL
display_name  varchar(120) NOT NULL
is_active     bool         NOT NULL DEFAULT true
created_at    timestamptz  NOT NULL
updated_at    timestamptz  NOT NULL
updated_by    varchar(120) NOT NULL

UNIQUE (tenant_id, sku)
INDEX (tenant_id, is_active)

---

Grupo 4 — Métodos de Reembolso & Mensagens

config.refund_method_config

config_id       uuid         PRIMARY KEY
tenant_id       uuid         NOT NULL REFERENCES config.tenant
return_method   varchar(32)  NOT NULL   -- "NO_PHYSICAL" | "STORE_RETURN" | "HOME_PICKUP"
payment_method  varchar(64)  NOT NULL
refund_method   varchar(64)  NOT NULL
output_message  text         NULL
is_active       bool         NOT NULL DEFAULT true
created_at      timestamptz  NOT NULL
updated_at      timestamptz  NOT NULL
updated_by      varchar(120) NOT NULL

UNIQUE (tenant_id, return_method, payment_method)

config.message_template

template_id    uuid         PRIMARY KEY
tenant_id      uuid         NOT NULL REFERENCES config.tenant
category       varchar(32)  NOT NULL   -- "RETURN_METHOD" | "REFUND_METHOD" | "COMPLETION" | "EMAIL" | "SMS"
scenario_code  varchar(64)  NOT NULL
body           text         NOT NULL   -- suporta variável {{case_number}}
is_active      bool         NOT NULL DEFAULT true
created_at     timestamptz  NOT NULL
updated_at     timestamptz  NOT NULL
updated_by     varchar(120) NOT NULL

UNIQUE (tenant_id, category, scenario_code)

category	scenario_code possíveis
RETURN_METHOD	NO_PHYSICAL, STORE_RETURN, HOME_PICKUP
REFUND_METHOD	BANK_TRANSFER, SAME_PAYMENT, USER_CHOICE, CONTINENTE_CARD
COMPLETION	NO_PHYSICAL, STORE_RETURN, HOME_PICKUP, NOT_ELIGIBLE, NOT_RECEIVED
EMAIL / SMS	NO_PHYSICAL, STORE_RETURN, HOME_PICKUP, NOT_RECEIVED_ABOVE, NOT_RECEIVED_BELOW

config.store_owner_mapping

mapping_id   uuid         PRIMARY KEY
tenant_id    uuid         NOT NULL REFERENCES config.tenant
store_code   varchar(64)  NOT NULL
owner        varchar(120) NOT NULL
created_at   timestamptz  NOT NULL
updated_at   timestamptz  NOT NULL
updated_by   varchar(120) NOT NULL

UNIQUE (tenant_id, store_code)
INDEX (tenant_id, owner)

---

Grupo 5 — Transversal

config.audit_log

Registo imutável — append-only, nunca actualizado nem eliminado.

log_id       uuid         PRIMARY KEY
tenant_id    uuid         NULL REFERENCES config.tenant
entity_type  varchar(64)  NOT NULL
entity_id    uuid         NOT NULL
action       varchar(8)   NOT NULL   -- "INSERT" | "UPDATE" | "DELETE"
old_value    jsonb        NULL
new_value    jsonb        NULL
changed_by   varchar(120) NOT NULL
changed_at   timestamptz  NOT NULL

INDEX (tenant_id, changed_at)
INDEX (entity_type, entity_id)
INDEX (changed_at)

config.section_permission

Controla quais secções o perfil USER pode editar. ADMIN tem acesso total implícito.

tenant_id     uuid         NOT NULL REFERENCES config.tenant
section_code  varchar(64)  NOT NULL   -- "profile_limits" | "return_reasons" | "thresholds" | "messages" | "store_mapping" | "exclusions" | "refund_config"
can_write     bool         NOT NULL DEFAULT false
updated_at    timestamptz  NOT NULL
updated_by    varchar(120) NOT NULL

PRIMARY KEY (tenant_id, section_code)

Diagrama DBML

Table config.tenant {
  tenant_id  uuid         [pk]
  code       varchar(64)  [not null, unique]
  name       varchar(120) [not null]
  is_active  bool         [not null, default: true]
  created_at timestamptz  [not null]
  updated_at timestamptz  [not null]
  updated_by varchar(120) [not null]
}

Table config.tenant_settings {
  tenant_id             uuid          [pk, ref: - config.tenant.tenant_id]
  n_articles_threshold  int           [not null, default: 3]
  general_threshold     numeric(12,2) [not null]
  cav_threshold         numeric(12,2) [null, note: 'NULL → fluxo geral para CAV (AC-179)']
  updated_at            timestamptz   [not null]
  updated_by            varchar(120)  [not null]
}

Table config.profile_usage_limit {
  limit_id     uuid        [pk]
  tenant_id    uuid        [not null, ref: > config.tenant.tenant_id]
  profile_code varchar(64) [not null, note: 'SELF_SERVICE | CC_COL | SAC_LOJA']
  limit_type   varchar(16) [not null, note: 'inactive | unlimited | fixed']
  limit_value  int         [null]
  updated_at   timestamptz [not null]
  updated_by   varchar(120)[not null]

  Indexes {
    (tenant_id, profile_code) [unique]
  }
}

Table config.return_reason {
  reason_id  uuid        [pk]
  tenant_id  uuid        [not null, ref: > config.tenant.tenant_id]
  code       varchar(64) [not null, note: 'CHANGED_MIND | PRODUCT_QUALITY | OPERATION_QUALITY | NO_SUBSTITUTION_INTEREST']
  nickname   varchar(120)[not null]
  is_active  bool        [not null, default: true]
  sort_order int         [not null, default: 0]
  created_at timestamptz [not null]
  updated_at timestamptz [not null]
  updated_by varchar(120)[not null]

  Indexes {
    (tenant_id, code) [unique]
  }
}

Table config.reason_attribute {
  attribute_id   uuid        [pk]
  reason_id      uuid        [not null, ref: > config.return_reason.reason_id]
  name           varchar(120)[not null]
  attribute_type varchar(32) [not null, note: 'text | numeric | textarea | dropdown | datepicker | file_upload']
  is_active      bool        [not null, default: true]
  sort_order     int         [not null, default: 0]
  created_at     timestamptz [not null]
  updated_at     timestamptz [not null]
  updated_by     varchar(120)[not null]
}

Table config.reason_attribute_option {
  option_id    uuid        [pk]
  attribute_id uuid        [not null, ref: > config.reason_attribute.attribute_id]
  label        varchar(120)[not null]
  sort_order   int         [not null, default: 0]
  is_active    bool        [not null, default: true]
}

Table config.reason_attribute_profile {
  attribute_id uuid        [not null, ref: > config.reason_attribute.attribute_id]
  profile_code varchar(64) [not null, note: 'ALL | SELF_SERVICE | CC_COL | SAC_LOJA']
  is_required  bool        [not null, default: false]

  Indexes {
    (attribute_id, profile_code) [pk]
  }
}

Table config.partial_return_exclusion {
  exclusion_id uuid        [pk]
  tenant_id    uuid        [not null, ref: > config.tenant.tenant_id]
  sku          varchar(64) [not null]
  display_name varchar(120)[not null]
  is_active    bool        [not null, default: true]
  created_at   timestamptz [not null]
  updated_at   timestamptz [not null]
  updated_by   varchar(120)[not null]

  Indexes {
    (tenant_id, sku) [unique]
    (tenant_id, is_active)
  }
}

Table config.refund_method_config {
  config_id      uuid        [pk]
  tenant_id      uuid        [not null, ref: > config.tenant.tenant_id]
  return_method  varchar(32) [not null, note: 'NO_PHYSICAL | STORE_RETURN | HOME_PICKUP']
  payment_method varchar(64) [not null]
  refund_method  varchar(64) [not null]
  output_message text        [null]
  is_active      bool        [not null, default: true]
  created_at     timestamptz [not null]
  updated_at     timestamptz [not null]
  updated_by     varchar(120)[not null]

  Indexes {
    (tenant_id, return_method, payment_method) [unique]
  }
}

Table config.message_template {
  template_id   uuid        [pk]
  tenant_id     uuid        [not null, ref: > config.tenant.tenant_id]
  category      varchar(32) [not null, note: 'RETURN_METHOD | REFUND_METHOD | COMPLETION | EMAIL | SMS']
  scenario_code varchar(64) [not null]
  body          text        [not null]
  is_active     bool        [not null, default: true]
  created_at    timestamptz [not null]
  updated_at    timestamptz [not null]
  updated_by    varchar(120)[not null]

  Indexes {
    (tenant_id, category, scenario_code) [unique]
  }
}

Table config.store_owner_mapping {
  mapping_id uuid        [pk]
  tenant_id  uuid        [not null, ref: > config.tenant.tenant_id]
  store_code varchar(64) [not null]
  owner      varchar(120)[not null]
  created_at timestamptz [not null]
  updated_at timestamptz [not null]
  updated_by varchar(120)[not null]

  Indexes {
    (tenant_id, store_code) [unique]
    (tenant_id, owner)
  }
}

Table config.audit_log {
  log_id      uuid        [pk]
  tenant_id   uuid        [null, ref: > config.tenant.tenant_id]
  entity_type varchar(64) [not null]
  entity_id   uuid        [not null]
  action      varchar(8)  [not null, note: 'INSERT | UPDATE | DELETE']
  old_value   jsonb       [null]
  new_value   jsonb       [null]
  changed_by  varchar(120)[not null]
  changed_at  timestamptz [not null]

  Indexes {
    (tenant_id, changed_at)
    (entity_type, entity_id)
    (changed_at)
  }
}

Table config.section_permission {
  tenant_id    uuid        [not null, ref: > config.tenant.tenant_id]
  section_code varchar(64) [not null, note: 'profile_limits | return_reasons | thresholds | messages | store_mapping | exclusions | refund_config']
  can_write    bool        [not null, default: false]
  updated_at   timestamptz [not null]
  updated_by   varchar(120)[not null]

  Indexes {
    (tenant_id, section_code) [pk]
  }
}

Mapeamento Critérios de Aceitação → Entidades

Critérios de Aceitação	Entidade(s)
AC-117 (rastreabilidade)	audit_log
AC-118–120	section_permission
AC-121–123	tenant
AC-125–127	profile_usage_limit
AC-128	tenant_settings.n_articles_threshold
AC-129–131	return_reason
AC-132–137	reason_attribute, reason_attribute_option, reason_attribute_profile
AC-138–140	partial_return_exclusion
AC-143–145	tenant_settings.general_threshold
AC-146–147	refund_method_config
AC-151–159	message_template
AC-170–175	store_owner_mapping
AC-178–179	tenant_settings.cav_threshold