create table audit_logs (
  id serial primary key,
  table_name text not null,
  record_id uuid not null,
  commit_lsn bigint not null,
  action text not null,
  old_values jsonb,
  new_values jsonb,
  created_at timestamp not null default now(),
  updated_at timestamp,
  -- Fields for transaction annotations
  username text,
  source text,
  request_id text,
  metadata jsonb
);

create unique index on audit_logs(commit_lsn, record_id);

-- Optional: Add indexes for common queries
create index on audit_logs(table_name, record_id);
create index on audit_logs(created_at);
create index on audit_logs(username);
create index on audit_logs(request_id);