SQLDoc

-- @import '@sqldoc/ns-audit'
-- @import '@sqldoc/ns-codegen'
-- @import '@sqldoc/ns-docs'
-- @import '@sqldoc/ns-rls'
-- @import '@sqldoc/ns-validate'

-- @docs.description('User accounts for the application')
-- @audit(on: [insert])
-- @rls
-- @rls.policy(for: ALL, to: authenticated, using: 'user_id = current_setting(''app.user_id'')::int')
CREATE TABLE users (
  -- @codegen.rename('id')
  user_id SERIAL PRIMARY KEY,

  -- @validate.pattern('^.+@.+\..+$')
  email VARCHAR(100) NOT NULL,

  -- @audit.redact(strategy: omit)
  password_hash TEXT NOT NULL
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,

  email VARCHAR(100) NOT NULL,

  password_hash TEXT NOT NULL
);

-- Generated by sqldoc
-- sqldoc: @audit(on: [insert])
CREATE TABLE IF NOT EXISTS "users_audit_log" (
  id BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL,
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION "users_audit_fn"() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "users_audit_log" (table_name, operation, old_data, new_data, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD) END, row_to_json(NEW), now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "users_audit_trigger"
  AFTER INSERT ON "users"
  FOR EACH ROW EXECUTE FUNCTION "users_audit_fn"();
-- sqldoc: @rls
ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;
-- sqldoc: @rls.policy(for: ALL, to: authenticated, using: 'user_id = current_setting(''app.user_id'')::int')
CREATE POLICY "users_authenticated_all" ON "users"
  FOR ALL
  TO authenticated
  USING (user_id = current_setting();
-- sqldoc: @validate.pattern('^.+@.+\..+$')
ALTER TABLE "users" ADD CONSTRAINT "users_email_pattern" CHECK ("email" ~ '^.+@.+\..+$');