AGM

CREATE TABLE principal (
  id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  email         VARCHAR(255) NOT NULL UNIQUE,
  display_name  VARCHAR(255),
  status        VARCHAR(16)  NOT NULL DEFAULT 'ACTIVE'
                  CHECK (status IN ('ACTIVE', 'SUSPENDED', 'OFFBOARDED')),
  mfa_enabled   BOOLEAN      NOT NULL DEFAULT TRUE,
  mfa_method    VARCHAR(8)   NOT NULL DEFAULT 'TOTP'
                  CHECK (mfa_method IN ('TOTP', 'EMAIL')),
  mfa_secret    TEXT,                          -- TOTP secret, encrypted at rest; NULL for EMAIL method
  source        VARCHAR(16)  NOT NULL DEFAULT 'LOCAL'
                  CHECK (source IN ('LOCAL', 'FEDERATED')),
  federation_source_id UUID  REFERENCES federation_source(id),
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- In-DB credentials (LOCAL principals only)
CREATE TABLE credential (
  principal_id  UUID PRIMARY KEY REFERENCES principal(id) ON DELETE CASCADE,
  password_hash TEXT    NOT NULL,              -- bcrypt
  must_reset    BOOLEAN NOT NULL DEFAULT FALSE,
  last_changed  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Store-scoped role grants
CREATE TABLE role_grant (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  principal_id UUID        NOT NULL REFERENCES principal(id) ON DELETE CASCADE,
  store_id     UUID        NOT NULL,
  role         VARCHAR(64) NOT NULL,
  granted_by   UUID        REFERENCES principal(id),
  granted_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (principal_id, store_id, role)
);

CREATE TABLE access_restriction (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  subject_type VARCHAR(16) NOT NULL CHECK (subject_type IN ('PRINCIPAL', 'ROLE')),
  subject_id   VARCHAR(128) NOT NULL,          -- principal UUID or role name
  store_id     UUID,                           -- NULL = platform-wide
  type         VARCHAR(32) NOT NULL,
  config       JSONB       NOT NULL,
  active       BOOLEAN     NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE federation_source (
  id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  name       VARCHAR(128) NOT NULL,
  type       VARCHAR(8)   NOT NULL CHECK (type IN ('SAML', 'OIDC', 'LDAP')),
  store_id   UUID,                             -- NULL = platform-wide
  config     JSONB        NOT NULL,
  active     BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT now()
);

{
  "idp_metadata_url": "https://idp.example.com/metadata.xml",
  "sp_entity_id": "https://agm.ss3.internal/saml/sp",
  "attribute_mapping": {
    "email": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress",
    "display_name": "http://schemas.microsoft.com/identity/claims/displayname"
  },
  "sign_requests": true,
  "want_assertions_signed": true
}

{
  "discovery_url": "https://accounts.google.com/.well-known/openid-configuration",
  "client_id": "...",
  "client_secret_ref": "ss3/kv/agm/oidc/{source_id}/client_secret",
  "scopes": ["openid", "email", "profile"],
  "attribute_mapping": { "email": "email", "display_name": "name" }
}

{
  "host": "ldap.corp.example.com",
  "port": 636,
  "use_tls": true,
  "bind_dn": "cn=agm-svc,dc=example,dc=com",
  "bind_password_ref": "ss3/kv/agm/ldap/{source_id}/bind_password",
  "user_search_base": "ou=users,dc=example,dc=com",
  "user_search_filter": "(mail={email})",
  "attribute_mapping": { "email": "mail", "display_name": "cn" }
}

service AgmReaderService {
  rpc ResolvePrincipal(ResolvePrincipalRequest) returns (PrincipalResponse);
  rpc ResolveOrProvisionFederated(FederatedIdentityRequest) returns (PrincipalResponse);
  rpc GetRestrictions(GetRestrictionsRequest) returns (RestrictionsResponse);
  // Returns method (TOTP|EMAIL) and secret for TOTP; triggers email OTP dispatch for EMAIL
  rpc GetMfaChallenge(GetMfaChallengeRequest) returns (MfaChallengeResponse);
  rpc GetFederationSource(GetFederationSourceRequest) returns (FederationSourceResponse);
}