

create table keepsakes (
  id char(36) primary key,
  subject_kind varchar(191) not null,
  subject_id varchar(191) not null,
  relation_id char(36) not null,
  state varchar(16) not null check (state in ('applied', 'revoked', 'expired')),
  expiry_policy json not null,
  applied_at datetime(6) not null,
  expires_at datetime(6),
  fulfilled_at datetime(6),
  revoked_at datetime(6),
  metadata json not null,
  created_at datetime(6) not null,
  updated_at datetime(6) not null,
  active_relation_key char(36) generated always as (
    case when state = 'applied' then relation_id else null end
  ) stored,
  constraint keepsakes_relation_fk foreign key (relation_id)
    references keepsake_relation_definitions(id),
  unique keepsakes_one_active_relation_per_subject
    (subject_kind, subject_id, active_relation_key)
);

create index keepsakes_active_subject_lookup
  on keepsakes (subject_kind, subject_id, relation_id, id);

create index keepsakes_active_relation_membership
  on keepsakes (relation_id, subject_kind, subject_id, id);

create index keepsakes_due_timed_expiry
  on keepsakes (expires_at, relation_id, subject_kind, subject_id, id);

create table keepsake_audit_events (
  id bigint primary key auto_increment,
  keepsake_id char(36) not null,
  relation_id char(36) not null,
  subject_kind varchar(191) not null,
  subject_id varchar(191) not null,
  actor_kind varchar(191) not null,
  actor_id varchar(191) not null,
  event_type varchar(64) not null,
  decision json not null,
  occurred_at datetime(6) not null,
  recorded_at datetime(6) not null default current_timestamp(6)
);

create index keepsake_audit_by_keepsake
  on keepsake_audit_events (keepsake_id, occurred_at, id);

create index keepsake_audit_by_relation
  on keepsake_audit_events (relation_id, occurred_at, id);
