Problem creating separate tables for admin

[holdenhinkle]

Hi @janko,

I'm trying to create a separate table for admins and I'm getting the following error:

Sequel::ForeignKeyConstraintViolation (PG::ForeignKeyViolation: ERROR:  insert or update on table "account_verification_keys" violates foreign key constraint "fk_rails_2e3b612008"
DETAIL:  Key (id)=(1) is not present in table "accounts".
):

(Please see the full logs below.)

I've got accounts_table and password_hash_table set in app/lib/rodauth_admin.rb:

    accounts_table :admin_accounts
    password_hash_table :admin_account_password_hashes

I created ALL the tables for admin_accounts (not just the two that are named above):

class CreateAdminAccountTables < ActiveRecord::Migration[6.0]
  def change
    enable_extension "citext"

    create_table :admin_accounts do |t|
      t.citext :email, null: false, index: { unique: true, where: "status IN ('unverified', 'verified')" }
      t.string :status, null: false, default: "unverified"
      t.string :role, null: false, default: "admin"
    end

    # Used if storing password hashes in a separate table (default)
    create_table :admin_account_password_hashes do |t|
      t.foreign_key :admin_accounts, column: :id
      t.string :password_hash, null: false
    end

    # Used by the password reset feature
    create_table :admin_account_password_reset_keys do |t|
      t.foreign_key :admin_accounts, column: :id
      t.string :key, null: false
      t.datetime :deadline, null: false
      t.datetime :email_last_sent, null: false, default: -> { "CURRENT_TIMESTAMP" }
    end

    # Used by the account verification feature
    create_table :admin_account_verification_keys do |t|
      t.foreign_key :admin_accounts, column: :id
      t.string :key, null: false
      t.datetime :requested_at, null: false, default: -> { "CURRENT_TIMESTAMP" }
      t.datetime :email_last_sent, null: false, default: -> { "CURRENT_TIMESTAMP" }
    end

    # Used by the verify login change feature
    create_table :admin_account_login_change_keys do |t|
      t.foreign_key :admin_accounts, column: :id
      t.string :key, null: false
      t.string :login, null: false
      t.datetime :deadline, null: false
    end

    # Used by the remember me feature
    create_table :admin_account_remember_keys do |t|
      t.foreign_key :admin_accounts, column: :id
      t.string :key, null: false
      t.datetime :deadline, null: false
    end
  end
end

The problem is an insert into account_verification_keys is complaining that the foreign key it's referencing in the accounts table doesn't exist. That's because it's in the admin_accounts table.

I searched the docs and issues about this, hoping that I needed to set the table names for the other tables needed like this in app/lib/rodauth_admin.rb:

    password_reset_key_table :admin_account_password_reset_keys
    verification_key_table :admin_account_verification_keys
    login_change_key_table :admin_account_login_change_keys
    remember_key_table :admin_account_remember_keys

But I couldn't find anything.

Can you please let me what I'm missing or doing wrong?

Thank you so much!

Holden

Full logs:

Started POST "/admin/create-account" for ::1 at 2021-07-04 14:56:00 -0400
Processing by RodauthApp#call as HTML
  Parameters: {"authenticity_token"=>"RC1oRVqTgdBhzEGRhUp550lywFt1FkoewW2n9NeqWFlUkuBshzgK0CUT1/CcuAyuautzGuR6Nrs5IBwJKObBgA==", "login"=>"holden@holdenhinkle.com", "password"=>"[FILTERED]", "password-confirm"=>"[FILTERED]"}
  Sequel (0.4ms)  SELECT * FROM "admin_accounts" WHERE (("email" = 'holden@holdenhinkle.com') AND ("status" IN ('unverified', 'verified'))) LIMIT 1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.1ms)  BEGIN
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.1ms)  SAVEPOINT active_record_1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (1.2ms)  SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' AND pg_class.oid = CAST(CAST('"admin_accounts"' AS regclass) AS oid)
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (4.5ms)  INSERT INTO "admin_accounts" ("email", "status") VALUES ('holden@holdenhinkle.com', 'unverified') RETURNING "id"
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.1ms)  RELEASE SAVEPOINT active_record_1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (2.3ms)  UPDATE "admin_account_password_hashes" SET "password_hash" = '$2a$12$oSDEoi56XFjNl9lIDvUAxu0euwhzZ8BEatMo6SwceRomuvVVYC84W' WHERE ("id" = 1)
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (0.6ms)  SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' AND pg_class.oid = CAST(CAST('"admin_account_password_hashes"' AS regclass) AS oid)
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (0.6ms)  INSERT INTO "admin_account_password_hashes" ("id", "password_hash") VALUES (1, '$2a$12$oSDEoi56XFjNl9lIDvUAxu0euwhzZ8BEatMo6SwceRomuvVVYC84W') RETURNING "id"
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (0.9ms)  SELECT 1 AS "one" FROM "account_verification_keys" WHERE ("id" = 1) LIMIT 1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.1ms)  SAVEPOINT active_record_1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (0.6ms)  SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' AND pg_class.oid = CAST(CAST('"account_verification_keys"' AS regclass) AS oid)
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
  Sequel (4.8ms)  INSERT INTO "account_verification_keys" ("id", "key") VALUES (1, '3okeqA-ZgGG0iCozvfBv-0idMhkSNfUljN_lUYYczkQ') RETURNING "id"
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.5ms)  ROLLBACK TO SAVEPOINT active_record_1
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
   (0.1ms)  ROLLBACK
  ↳ app/lib/rodauth_app.rb:13:in `block (2 levels) in <class:RodauthApp>'
Completed 500 Internal Server Error in 222ms (ActiveRecord: 16.9ms | Allocations: 21742)


  
Sequel::ForeignKeyConstraintViolation (PG::ForeignKeyViolation: ERROR:  insert or update on table "account_verification_keys" violates foreign key constraint "fk_rails_2e3b612008"
DETAIL:  Key (id)=(1) is not present in table "accounts".
):

[janko]

For table name configuration methods I typically open the corresponding feature documentation and search for the term "table". In your case, the correct configuration would be:

reset_password_table :admin_account_password_reset_keys
verify_account_table :admin_account_verification_keys
verify_login_change_table :admin_account_login_change_keys
remember_table :admin_account_remember_keys

Note that in your case all table names are the default table names prefixed with admin_*, so you could DRY up the logic to make it work for any future features as well:

methods.grep(/_table$/) do |table_method|
  public_send(table_method) { :"admin_#{super()}" }
end

[holdenhinkle]

Thank you Janko! This is what I was missing. I appreciate your fast response.

And awesome suggestion:

methods.grep(/_table$/) do |table_method|
  public_send(table_method) { :"admin_#{super()}" }
end

Thanks again.