Invitation System¶

A generic invitation system for inviting users to tenants, groups, permission sets, individual permissions, and resource-access-controlled entities. Invitations carry ordered, typed actions that are executed across lifecycle phases -- database actions run immediately, while backend/external actions are returned to the caller for async processing. Source: 041_tables_invitation.sql, 042_functions_invitation.sql

Overview¶

The invitation system models the full lifecycle of an invitation:

Create -- an inviter creates an invitation targeting an email/phone, with a set of actions to execute
on_create actions -- immediate phase: backend actions like sending SMS or email are returned to the caller
Accept / Reject -- the target user (or backend on their behalf) accepts or rejects
on_accept / on_reject actions -- phase-specific actions fire: database actions execute inline, backend actions are returned
Completion -- when all actions finish (completed, skipped, or failed-but-optional), the invitation is marked completed

Key design principles:

Phase-based execution -- each action belongs to a phase (on_create, on_accept, on_reject, on_expired) controlling when it fires
Condition evaluation -- each action has a condition (always, user_not_in_tenant, user_not_in_group, etc.) evaluated at execution time; if false, the action is skipped
Executor model -- database actions run inline in SQL; backend/external actions are returned to the caller with resolved payloads
Payload schema -- action types declare a payload_schema with field definitions and source mappings (e.g., "source": "invitation.target_email") for auto-population from invitation context
Sequence + is_required -- actions are grouped by sequence (integer); a required action failure stops later sequences within the same phase
Templates -- reusable invitation_template definitions with pre-configured action lists and payload templates

Invitation Lifecycle¶

stateDiagram-v2
    [*] --> pending: create_invitation
    pending --> processing: accept_invitation
    pending --> rejected: reject_invitation
    pending --> revoked: revoke_invitation
    pending --> expired: expires_at reached
    processing --> completed: all actions done
    processing --> failed: required action failed

Phases and Conditions¶

Phases¶

Phase	When it fires	Typical actions
`on_create`	Immediately when invitation is created	Send SMS, send email
`on_accept`	When invitation is accepted	Add to tenant, add to group, assign permissions
`on_reject`	When invitation is rejected	Notify inviter of rejection
`on_expired`	When expired invitation is detected	Cleanup, notify inviter

Conditions¶

Conditions are evaluated at execution time against the current database state. If a condition returns false, the action is skipped.

Condition	Evaluates
`always`	Always true (default)
`user_not_in_tenant`	Target user is not yet a member of the invitation's tenant
`user_not_in_group`	Target user is not yet a member of the group specified in `payload.user_group_id`
`user_has_no_perm_set`	Target user does not have the permission set specified in `payload.perm_set_code`
`user_has_no_resource_access`	Target user has no access to the resource specified in `payload.resource_type` + `payload.resource_id`

Executor Model¶

Executor	Behavior
`database`	Executed immediately by `process_invitation_actions` inside the SQL transaction
`backend`	Returned to the caller as a pending action row with resolved payload; caller handles it asynchronously
`external`	Same as backend -- returned for external system processing

Built-in Database Actions¶

Action Type	What it does
`add_tenant_user`	Inserts into `auth.tenant_user` (ON CONFLICT DO NOTHING)
`add_group_member`	Calls `unsecure.create_user_group_member` using `payload.user_group_id`
`assign_perm_set`	Calls `unsecure.assign_permission` using `payload.perm_set_code`
`assign_permission`	Calls `unsecure.assign_permission` using `payload.permission_code`
`assign_resource_access`	Calls `auth.assign_resource_access` using `payload.resource_type`, `payload.resource_id`, `payload.access_flags`

Built-in Backend Actions¶

Action Type	Payload schema fields
`send_welcome_email`	`email`, `invitation_uuid`, `inviter_user_id`, `message`, `tenant_id` (auto-resolved from invitation context)
`send_sms_invite`	`mobile_phone` (from `invitation.target_email`), `invitation_uuid`, `message`
`notify_inviter`	`inviter_user_id`, `target_email`, `invitation_uuid`, `message`

Payload Schema Resolution¶

Action types declare a payload_schema describing what fields the executor needs and where they come from:

{
  "fields": {
    "email":           {"type": "string",  "required": true,  "source": "invitation.target_email"},
    "invitation_uuid": {"type": "string",  "required": true,  "source": "invitation.uuid"},
    "inviter_user_id": {"type": "integer", "required": true,  "source": "invitation.inviter_user_id"},
    "message":         {"type": "string",  "required": false, "source": "invitation.message"}
  }
}

When process_invitation_actions encounters a backend/external action, it calls unsecure.resolve_action_payload which:

Loads the payload_schema from const.invitation_action_type
For each field with a source, resolves the value from the invitation context
Action-level payload values take precedence over schema defaults
Extra payload fields (not in schema) are preserved

Functions¶

Creating Invitations¶

`auth.create_invitation`¶

Creates an invitation with inline action definitions. Returns the invitation ID, UUID, and any pending on_create backend actions as a JSON array.

select * from auth.create_invitation(
    'app', 1, 'corr-123', 1,               -- created_by, user_id, correlation_id, tenant_id
    'user@example.com',                      -- target_email
    _actions := '[
        {"action_type_code": "send_welcome_email", "phase_code": "on_create", "sequence": 0},
        {"action_type_code": "add_tenant_user",    "phase_code": "on_accept", "sequence": 0,
         "condition_code": "user_not_in_tenant"},
        {"action_type_code": "add_group_member",   "phase_code": "on_accept", "sequence": 1,
         "condition_code": "user_not_in_group", "payload": {"user_group_id": 5}}
    ]'::jsonb,
    _message := 'Welcome to our team!',
    _expires_at := now() + interval '7 days'
);
-- Returns: __invitation_id, __uuid, __on_create_actions (jsonb array of pending backend actions)

Signature:

auth.create_invitation(
    _created_by text, _user_id bigint, _correlation_id text, _tenant_id integer,
    _target_email text,
    _actions jsonb default '[]',
    _message text default null,
    _expires_at timestamptz default null,
    _extra_data jsonb default null,
    _request_context jsonb default null
) returns table(__invitation_id bigint, __uuid uuid, __on_create_actions jsonb)

Permission: invitations.create_invitation

`auth.create_invitation_from_template`¶

Creates an invitation from a reusable template, with optional payload overrides and message override.

select * from auth.create_invitation_from_template(
    'app', 1, 'corr-456', 1,
    'sms_group_invite',                      -- template_code
    '+420555666777',                          -- target_email (phone for SMS templates)
    _message := 'Custom message',
    _payload_overrides := '{"user_group_id": 5}'::jsonb
);

Permission: invitations.create_invitation

Accepting / Rejecting / Revoking¶

`auth.accept_invitation`¶

Validates the invitation (must be pending, not expired), sets status to processing, skips on_reject/on_expired actions, processes on_accept actions, and returns any pending backend actions.

select * from auth.accept_invitation('app', 1, 'corr-789', _invitation_id, _target_user_id);
-- Returns rows: __invitation_action_id, __action_type_code, __executor_code, __sequence, __payload

Permission: invitations.accept_invitation

`auth.reject_invitation`¶

Sets invitation to rejected, skips all non-reject-phase actions, processes on_reject actions.

select * from auth.reject_invitation('app', 1, 'corr-101', _invitation_id);
-- Returns pending on_reject backend actions (e.g., notify_inviter)

Permission: invitations.reject_invitation

`auth.revoke_invitation`¶

Sets invitation to revoked, skips all pending actions. Returns void.

perform auth.revoke_invitation('app', 1, 'corr-102', _invitation_id);

Permission: invitations.revoke_invitation

Completing Backend Actions¶

After the backend processes a returned action (e.g., sends the SMS), it reports the result:

-- Success
perform unsecure.complete_invitation_action('app', 1, 'corr-200', _action_id,
    '{"provider": "twilio", "sid": "SM123"}'::jsonb);

-- Failure
perform unsecure.fail_invitation_action('app', 1, 'corr-201', _action_id,
    'SMS provider timeout');

complete_invitation_action and fail_invitation_action both call check_invitation_completion which marks the invitation as completed when no actions remain in pending or processing state.

If a required action fails, all later-sequence actions in the same phase are skipped and the invitation is marked failed.

Querying Invitations¶

`auth.get_invitations`¶

select * from auth.get_invitations('app', 1, 'corr-300', 1,
    _status_code := 'pending',
    _target_email := 'user@'           -- substring match
);

Permission: invitations.get_invitations

`auth.get_invitation_actions`¶

select * from auth.get_invitation_actions('app', 1, 'corr-301', _invitation_id);

Permission: invitations.get_invitations

Template Management¶

`auth.create_invitation_template`¶

select * from auth.create_invitation_template(
    'app', 1, 'corr-400', 1,
    'sms_group_invite',
    'SMS Group Invitation',
    _description := 'Invite user to a group via SMS',
    _default_message := 'You have been invited!',
    _actions := '[
        {"action_type_code": "send_sms_invite",  "phase_code": "on_create", "sequence": 0, "is_required": false},
        {"action_type_code": "add_tenant_user",   "phase_code": "on_accept", "sequence": 0,
         "condition_code": "user_not_in_tenant"},
        {"action_type_code": "add_group_member",  "phase_code": "on_accept", "sequence": 1,
         "condition_code": "user_not_in_group", "payload_template": {}},
        {"action_type_code": "notify_inviter",    "phase_code": "on_reject", "sequence": 0}
    ]'::jsonb
);

Permission: invitations.manage_templates

`auth.update_invitation_template` / `auth.delete_invitation_template`¶

Permission: invitations.manage_templates

Tables¶

Core Tables¶

Table	Description
`auth.invitation`	Main invitation: uuid, tenant_id, inviter_user_id, target_email, target_user_id, status_code, message, expires_at, template_code, extra_data
`auth.invitation_action`	1:N ordered actions per invitation: action_type_code, executor_code, phase_code, condition_code, sequence, is_required, payload, result_data, error_message
`auth.invitation_template`	Reusable named templates (tenant-scoped or global)
`auth.invitation_template_action`	Template action definitions with phase_code, condition_code, payload_template

Configuration Tables (const schema)¶

Table	Values
`const.invitation_status`	pending, accepted, rejected, revoked, expired, processing, completed, failed
`const.invitation_action_status`	pending, processing, completed, failed, skipped
`const.invitation_executor`	database, backend, external
`const.invitation_phase`	on_create, on_accept, on_reject, on_expired
`const.invitation_condition`	always, user_not_in_tenant, user_not_in_group, user_has_no_perm_set, user_has_no_resource_access
`const.invitation_action_type`	Registry of action types with executor binding and payload_schema

Event Codes¶

Informational Events (22001-22012)¶

Code	Name	Description
22001	`invitation_created`	New invitation was created
22002	`invitation_accepted`	Invitation was accepted
22003	`invitation_rejected`	Invitation was rejected
22004	`invitation_revoked`	Invitation was revoked
22005	`invitation_expired`	Invitation expired
22006	`invitation_action_completed`	An action was completed
22007	`invitation_action_failed`	An action failed
22008	`invitation_completed`	All actions completed
22009	`invitation_failed`	Required action failure caused invitation failure
22010	`invitation_template_created`	Template created
22011	`invitation_template_updated`	Template updated
22012	`invitation_template_deleted`	Template deleted

Error Codes (39001-39006)¶

Code	Function	Description
39001	`error.raise_39001`	Invitation does not exist
39002	`error.raise_39002`	Invitation is not in pending state
39003	`error.raise_39003`	Invitation has expired
39004	`error.raise_39004`	Invitation action does not exist
39005	`error.raise_39005`	Invitation action is not in pending or processing state
39006	`error.raise_39006`	Invitation template does not exist or is inactive

End-to-End Example: SMS Group Invite¶

sequenceDiagram
    participant Backend
    participant DB

    Backend->>DB: auth.create_invitation_from_template('sms_group_invite', '+420...')
    DB-->>Backend: invitation_id, uuid, on_create_actions (send_sms_invite)

    Backend->>Backend: Send SMS via Twilio
    Backend->>DB: unsecure.complete_invitation_action(sms_action_id, result_data)

    Note over Backend,DB: User clicks link, registers, backend accepts

    Backend->>DB: auth.accept_invitation(invitation_id, new_user_id)
    Note over DB: Evaluates conditions:<br/>user_not_in_tenant → add_tenant_user<br/>user_not_in_group → add_group_member
    DB-->>Backend: no pending actions (all were database type)

    Note over DB: invitation status = completed