Concepts: how pgkiln works

Applications are data

In pgkiln, as in Oracle APEX, an application is not code but rows in tables. A page is a row in meta.page, a report on that page is a row in meta.region whose source column holds a SELECT, a text field is a row in meta.item, and so on. The builder is a friendly editor for those rows, but you can create and change applications with plain SQL, for example in a migration script. That is exactly how the HR sample (examples/hr/hr.sql) and the tutorial app (examples/tasks-app.sql) are built.

Because definitions are read on every request (there is no cache of definitions), a change in the builder is live on the next page load. Regions can opt in to a cache of their rendered HTML; its key includes the region's definition, so a change shows at once there too (see large tables).

meta.app ─┬─ meta.page ─┬─ meta.region        (reports, forms, grids, charts, …)
          │             ├─ meta.item          (form fields, filters; session state variables)
          │             ├─ meta.button
          │             ├─ meta.dynamic_action
          │             ├─ meta.computation
          │             ├─ meta.validation
          │             ├─ meta.process
          │             └─ meta.branch
          ├─ meta.nav_entry     (navigation menu)
          ├─ meta.authz_scheme  (authorization schemes)
          ├─ meta.lov           (shared lists of values)
          ├─ meta.app_item      (application items)
          ├─ meta.app_process   (application processes)
          ├─ meta.build_option  (build options: include / exclude switches)
          └─ meta.app_user      (users of this application)

meta.session, meta.activity_log, meta.developer, meta.instance_setting

Every table and column is described in the reference.

Architecture

┌──────────── Browser ─────────────┐
│ server-rendered HTML + app.css   │   works without JavaScript;
│ /static/app.js (enhancements)    │   app.js adds dialogs, dynamic actions, grids
└──────────────┬───────────────────┘
               │ HTTP
┌──────────────┴───────────────────────────────────────────────┐
│ pgkiln (Node.js, Fastify)                                    │
│  /a/<alias>/<page>   runtime: renders and processes pages    │──── runtime pool (pgkiln_runtime)
│  /builder/...        builder, SQL Workshop                   │──── owner pool (DATABASE_URL)
└──────────────────────────────────────────────────────────────┘
               │
┌──────────────┴───────────────────────────────────────────────┐
│ PostgreSQL                                                   │
│  meta.*          application definitions, sessions, logs     │
│  your schemas    tables, views, PL/pgSQL, RLS policies        │
└──────────────────────────────────────────────────────────────┘

There is no client-side framework. Pages are rendered on the server, and forms post back to the server. This keeps applications fast, accessible, easy to debug and usable without JavaScript.

URLs

URL	Meaning
/a/<alias>	Redirects to the application's home page
/a/<alias>/<page>	Shows page <page> (a number)
/a/<alias>/<page>?P3_ID=7&cs=…	Shows the page with items set. The checksum cs is required on protected pages; links generated by pgkiln carry it automatically
/a/<alias>/<page>?clear=1	Shows the page with all its items cleared (e.g. an empty form)
/a/<alias>/login	Sign-in page
/builder	The builder

The report, grid, calendar and facet regions add their own parameters (search, sort, page…); see the reference.

What happens on a request

Showing a page (GET)

1. Load the application and the page definition; a missing app or page returns 404.
2. Find the session from the cookie; if the page requires sign-in and the user is anonymous, redirect to the login page.
3. Apply URL items (?P3_ID=7). On protected pages the checksum must be valid, or the request is refused with 403.
4. Open a transaction as the application's database role, with the user and session made visible to SQL (meta.app_user(), meta.v()).
5. Check the page's authorization scheme (403 if it fails).
6. Run application processes of type before page.
7. Take the first branch with point before header that applies, if any: redirect there.
8. Fetch form rows: for every form region whose primary-key item has a value, read the row into the items.
9. Run the computations with point before header, then page processes with point load.
10. Compute visibility: which regions, items, buttons and dynamic actions this user may see (authorization schemes + server-side conditions + read-only conditions).
11. Render the page, then commit and save the session state.

Components whose build option is excluded are left out when the page is loaded, as if they did not exist.

Submitting a page (POST)

1. Same loading and sign-in checks; then verify the CSRF token.
2. In one transaction as the app's role:
   1. check page authorization, run before page application processes;
   2. compute visibility using the state the page was rendered with, before reading the posted values;
   3. the pressed button must be one that was visible, or the request gets a 403 (this stops forged requests for hidden buttons);
   4. copy the posted values of editable items into session state. Hidden, display-only, read-only and unauthorized items are ignored;
   5. run the computations with point after submit;
   6. run validations (except for DELETE); any failure re-shows the page with messages;
   7. run the processes for this button, in sequence;
   8. pick the first branch (point after processing) for this button whose condition holds.
3. On success, commit, store the success message and redirect to the branch's target, or else the button's target page (POST-redirect-GET). In a modal dialog, the dialog closes and the page below reloads. On an error, everything is rolled back and the page is shown again with the entered values and the error.

A submit without a button (for example a select list with "submit on change") only stores the item values and reloads the page.

Session state

Every visitor gets a session (a random token in an httpOnly cookie; the database stores only a SHA-256 of it). The session holds session state: a key/value map of item values. Session state:

• survives across pages. An item's value stays until it is changed or cleared, so a filter on a report page is remembered when you come back;
• is strings or NULL (an empty string is NULL, as in APEX);
• is readable in SQL as a bind variable (:P2_DEPTNO) or with meta.v('P2_DEPTNO').

Sessions end after SESSION_IDLE_MINUTES without requests, SESSION_MAX_HOURS after sign-in, or on sign-out. Each application has its own session and cookie (pgkiln_app_<id>); the builder has another one (pgkiln_dev).

Page items and application items

• Page items belong to a page (e.g. P3_ENAME). They are rendered as fields and can be submitted by the browser if they're editable. By convention their names start with P<page number>_.
• Application items (e.g. AI_EMPNO) are not on any page and can never be set by the browser, only by server-side code (application processes, page processes and computations). Use them for per-session facts such as "the employee number of the signed-in user".

Bind variables: :NAME

Anywhere you write SQL (region sources, lists of values, conditions, validations, processes, dynamic actions), :NAME refers to the value of item NAME, case-insensitively:

select * from hr.emp where :P2_DEPTNO is null or deptno = :P2_DEPTNO::int

pgkiln replaces each bind variable with the value as an escaped, untyped SQL literal ('10', or NULL when empty). This matters in two ways:

1. It is safe. The value is always a quoted literal, never SQL text, so user input can't change the statement.
2. Postgres infers the type from the context, like Oracle's implicit conversion: deptno = '10' compares as integer, and '10' is null is valid. That is why the APEX pattern :X is null or col = :X works, while it fails with normal $1 parameters ("could not determine data type of parameter"). Add a cast when the context is ambiguous: :P3_SAL::numeric.

Built-in bind variables:

Name	Value
:APP_USER	the signed-in username, or nobody
:APP_ID, :APP_ALIAS	the application's id and alias
:APP_PAGE_ID	the current page number
:APP_SESSION	the internal session id
:REQUEST	the name of the pressed button (e.g. SAVE)

Bind variables are not replaced inside string literals, quoted identifiers, comments or dollar-quoted bodies ($$ … $$). Inside a function or DO block, use meta.v('P3_X') and meta.app_user().

Substitution strings: &NAME.

In HTML and text (static region content, page titles, button link targets), &NAME. (with the trailing dot) is replaced by the item's value, HTML-escaped:

<p>Welcome back, <b>&AI_ENAME.</b>!</p>

The application's database role

Each application has a db_role, the equivalent of APEX's parsing schema. Every request runs inside a transaction that does SET LOCAL ROLE <db_role>, so:

• the app can only touch objects that role has been granted;
• PostgreSQL row level security policies apply, and they can use meta.app_user() and meta.has_role() to decide per user;
• triggers and functions know which application user acted, which makes audit trails easy (see hr.audit() in the sample).

When you create an application in the builder, pgkiln creates the role app_<alias> with privileges on one schema, including default privileges for tables you create later.

Visibility

Before rendering, and again before accepting a submit, pgkiln decides what exists for the current user:

Component	Rendered when
Region	its authorization scheme passes and its server-side condition is true
Item	its region is rendered and its authorization scheme passes
Item (editable)	rendered, not hidden/display, and its read-only condition is false
Button	its region is rendered, its authorization scheme passes and its condition is true
Dynamic action	its authorization scheme passes
Navigation entry, link	the target page's authorization passes

A component that is not rendered cannot be used: its button can't be pressed and its item can't be set, even by a hand-crafted request. Conditions that raise an error count as false (hidden); read-only conditions that raise an error count as true (read-only).

Error messages

• An error you raise on purpose in PL/pgSQL is shown to the user as written:

  raise exception 'Not enough leave left.';

  With using column = 'sal', the message appears next to the item whose source column (or name) is sal.
• Constraint violations get friendly messages ("A record with these values already exists").
• Any other database error is logged in the activity log and shown as "An unexpected error occurred (reference #123)". Turn on debug mode in the application settings during development to see full error messages. Never leave it on in production.
• To see what a request did and how long each step took, turn on debug messages and write your own with meta.debug(level, text).