User Object ACL Extension

[dbauszus-glx]

Database Schema

Table Structure

CREATE TABLE
  test.acl (
    _id serial NOT NULL,
    email text NOT NULL,
    password text NULL,
    verified boolean NULL DEFAULT false,
    approved boolean NULL DEFAULT false,
    verificationtoken text NULL,
    approvaltoken text NULL,
    failedattempts integer NULL DEFAULT 0,
    password_reset text NULL,
    api text NULL,
    approved_by text NULL,
    access_log text[] NULL DEFAULT '{}'::text[],
    blocked boolean NULL DEFAULT false,
    roles text[] NULL DEFAULT '{}'::text[],
    admin boolean NULL DEFAULT false,
    language text NULL DEFAULT 'en'::text,
    session text NULL,
    expires_on bigint NULL,

    locale_object JSONB, --new locale object storage field
  );

1. Write Operations

-- Insert new record
INSERT INTO acl (_id, roles, admin, local_object)
VALUES ($1, $2, $3, $4);

-- Update specific field
UPDATE user_acl 
SET locale_object = jsonb_set(locale_object, $2, $3::jsonb)
WHERE _id = $1;

-- Delete field
UPDATE user_acl 
SET locale_object = locale_object - $2,
    updated_at = NOW()
WHERE _id = $1;

Performance Considerations

JSONB vs JSON

• Using JSONB for better query performance
• Enables GIN indexing for complex queries
• Binary storage format reduces storage size