Pages and regions

Pages

A page has a number (unique in the app), a name, a title, and these behaviours:

Property	Values	Effect
`mode`	`normal`, `modal`	Modal pages open in a dialog over the page that linked to them. After a successful submit the dialog closes and the page below reloads (showing the success message). On phones the dialog is full screen. Opened directly, a modal page works as a normal page
`dialog_position`	`center`, `left`, `right`, `top`, `bottom`	Modal pages: a centred dialog (the default) or a drawer that slides in from that edge (APEX: the Drawer page template; 26.1: top and bottom drawers). See Dialogs and drawers
`dialog_size`	`small`, `medium`, `large`	Modal pages: the dialog's or side drawer's width, the height of a top or bottom drawer
`parent_page`	page number	Breadcrumb trail, and which menu entry is highlighted
`requires_auth`	boolean	`false` makes the page public in an app with a login
`authz`	scheme name	Who may open it (403 otherwise)
`protection`	`checksum`, `unrestricted`	Whether URL item values need a checksum

Dialogs and drawers

A modal page (Page Designer → Page → Appearance: Page mode Modal dialog) opens over the page that linked to it. Dialog position chooses how:

Position	Opens as	Sizes (small / medium / large)
Centred dialog	A dialog in the middle of the screen	480 / 760 / 1100 px wide
Drawer from the right or left	A full-height panel docked to that edge, sliding in	400 / 560 / 860 px wide
Drawer from the top or bottom	A full-width panel docked to that edge	about a third, half or most of the screen high

On phones centred dialogs and side drawers fill the screen; top and bottom drawers keep their height. Users who prefer reduced motion get no slide-in animation. Everything else (closing after a submit, the Dialog Closed dynamic action, opening the page directly as a normal page) works the same for every position. In the HR example, Leave request (page 7) is a drawer from the right.

Layout

The page content is a 12-column grid. Each region's columns (1–12) sets its width on desktops. On tablets, regions of 6 columns or less pair up (two per row) and wider ones take the full width; on phones every region takes the full width.

Region templates:

`template`	Appearance
`standard`	A card with a header (title and buttons) and a body
`plain`	No frame and no header: content only (good for KPI cards and intro text)
`collapsible`	A standard card whose body can be collapsed by clicking the header

Every region also has seq (order), condition (a SQL boolean expression; the region renders only when true) and authz (an authorization scheme).

Template options

Like APEX's Template Options, regions, buttons and items have Template options (Page Designer, group Appearance): checkboxes for CSS classes from a fixed list per component type, kept in the component's template_options column (text[]) and added to its class. Only classes from the list are written into the page; anything else (an older export, a hand edit) is ignored. The styles are in public/app.css, and follow the app's style variant (accent colour, corners).

Region option	Class	Effect
Accent top border	`to-accent`	A 3 px top border in the accent colour
Flat	`to-flat`	No shadow
No border or background	`to-borderless`	Transparent, no border or shadow
Compact	`to-compact`	Less padding in header and body
No body padding	`to-no-padding`	The body content touches the frame (tables, maps)
Scroll the body	`to-scroll`	The body is at most 24 rem high and scrolls
Stretch to the row height	`to-stretch`	Regions side by side get the same height
Centre the text	`to-center`	Body text centred
Hide the header	`to-hide-header`	The header is hidden visually but kept for screen readers (standard template)

Button option	Class	Effect
Small / Large	`to-small`, `to-large`	Smaller or larger button
Full width	`to-block`	The button takes the full width
Pill	`to-pill`	Rounded ends
Outline	`to-outline`	Transparent with an accent-coloured border and text
Looks like a link	`to-link`	No frame, underlined accent text
Success / Danger	`to-success`, `to-danger`	Green or red button

Item option	Class	Effect
Stretch	`to-stretch`	The item takes the whole row of the form grid
Large field	`to-large`	A larger field and text
Quiet	`to-quiet`	No border or background until the field has the focus
Bold value	`to-bold`	The value in bold (also for display items)
Hide the label	`to-hide-label`	The label is hidden visually but kept for screen readers

Report columns get a Display choice in the region's Report settings (one per column): Bold (to-col-bold), Muted, No wrapping, Monospace, Right-aligned or Centred, kept in the region's config as "column_options": {"name": ["to-col-mono"]} and only taken from that list.

In SQL: update meta.region set template_options = '{to-accent,to-compact}' where id = 12; The database only checks the shape (at most 12 names of lower case letters, digits and -).

Region types

Type	Purpose
`report`	Read-only table from a SELECT, with search, filters, sorting, control break, aggregates, highlights, computed columns, group by, pivot and chart views, row selection, saved reports, paging and CSV/Excel/PDF download
`grid`	Editable table on one database table, with aggregates, frozen, movable and resizable columns, saved grid reports, a row actions menu, master-detail and copy/paste of cells
`form`	Fields for one row of a table, with automatic fetch and save
`chart`	Bar, column, stacked, line, area, combo, scatter, bubble, donut, pie, gauge, funnel, radar, Gantt, pyramid or polar chart from a SELECT, with drill-down links
`cards`	Cards or KPI tiles from a SELECT
`calendar`	Month, week, day and list views of dated rows, with create on click and drag and drop
`facets`	Checkbox, range and star filters with counts and a search field for a report
`smart_filters`	One search field with filter chips and suggestions for a report
`display_selector`	Tabs or a select list that show one region (or group of regions) of the page at a time
`map`	Places (markers) and shapes on an interactive map
`tree`	Rows with a parent as an expandable tree
`list`	A list (Shared Components → Lists) as nested links, a badge list, cards or tabs
`data_reporter`	Business users build, save and share their own reports from tables and views the developer offers
`ai_assistant`	A chat with an AI service (Claude or OpenAI), with context from queries and tools the model may call (an AI agent)
`template_component`	Each row (or all rows) of a SELECT through a template component: badges, contact cards, timelines, your own
`tasks`	Task list: approvals and actions for the signed-in user
`workflows`	Workflow console: the workflows the user started or administers
`static`	Fixed HTML with `&ITEM.` substitutions
`dynamic`	HTML produced by a SELECT

Region-specific options go in the region's attributes (config, a JSON object).

Static ID (APEX: Static ID, optional): lower case letters, digits, _ and -, starting with a letter, unique on the page. It names the region in exported files, so renaming the region keeps its file and the references to it, and the page renders it as data-static-id on the region (<section id="R12" data-static-id="staff-list">), for your CSS ([data-static-id="staff-list"]) and JavaScript. The element's id stays R<id>.

Regions on a REST data source

Every region type that reads a query (report, grid without saving, chart, cards, calendar, map, tree, template component) can read a REST data source instead of a table: set the region's REST data source property. Its Source is then optional SQL over a CTE named rest (select * from rest where …), and parameters go into {"rest_params": {"city": "&P1_CITY."}} in the attributes. See chapter 19.

`report` (interactive report)

Source: any SELECT. Bind variables filter it:

sql

select e.empno, e.ename, e.job, d.dname as department, e.sal
  from hr.emp e left join hr.dept d using (deptno)
 where :P2_DEPTNO is null or e.deptno = :P2_DEPTNO::int

Features for end users:

Search across all columns.
Sort by clicking a column heading (again for descending), or from the Actions menu.
Actions menu:
- column filters (=, ≠, contains, does not contain, >, ≥, <, ≤, is empty, is not empty) and sort;
- control break: group the rows by a column, with a heading row per group;
- aggregates: sum, average, count, minimum or maximum of a column, over all filtered rows (not just the page). A Total row closes the table, and with a control break every group gets a Subtotal;
- highlight: color the rows that match a condition (yellow, green, red, blue or gray; the first matching rule wins);
- compute: add a column calculated from others, e.g. Year pay = sal * 12 (see computed columns); it can be filtered, sorted, aggregated and highlighted like any column;
- group by: up to three columns, with the number of rows and any sums, averages, counts, minimums or maximums per group;
- pivot: one column's values as columns (up to 30), with a function of a value column in the cells and a total per row, e.g. salary per department × job;
- chart: a bar, column, line, area, donut or pie chart of a function per label column (up to 50 labels), with its data table. The chart view has one series, so the stacked, combo and scatter kinds are only available in a chart region;
- saved reports: save the current search, filters, sort, break, aggregates, highlights, computed columns and views under a name, and switch between saved reports (below);
- rows per page, download CSV / Excel / PDF, print, reset.
Active search, filters, break, aggregates, highlights, computed columns and views appear as removable chips. Once a group by, pivot or chart is set, Report / Group by / Pivot / Chart links switch between the views; all views use the same search, filters and facets.
Paging with Previous/Next.
On phones every row reflows into a card with labelled values.

The report's state lives in the URL (?r12_q=…&r12_s=3&r12_d=desc&r12_b=job&r12_a=sum|sal&r12_h=sal|gt|green|3000), so it can be bookmarked and shared. Everything the user enters is applied safely: search, filter and highlight values become literals, columns must exist in the result, operators, aggregate functions, chart types and colors come from fixed lists, sort positions are integers, and computed column expressions are parsed (below), never pasted into SQL.

Natural-language filters (Ask in your own words)

With an AI service, an interactive report can take a question in the user's own words (APEX: natural language to interactive report): "analysts and managers hired before 1982, highest salary first". Set "ai_filter": {"service": "NAME"} in the report's attributes (or Ask in your own words (AI) under the report's settings in the page designer); a question box appears above the report when the application may use the service.

The question goes to the service with the report's shown columns (names, headings and kinds: text, number, date) and its filter operators; the answer is a structured output (filters, a search text, a sort column) that pgkiln checks again (only those columns and operators, at most five filters, short values) and turns into the report's ordinary URL parameters. So the model can only set what the user could set by hand in the Actions menu; the report builds the SQL (values as escaped literals) and runs it as the application's role, and the user sees which filters were applied and can change or remove them. Hidden columns (hidden) are not offered. Signed-in users only, unless "public": true; logged in the AI usage log with the source nl2ir.

Key	Meaning
`ai_filter.service`	The AI service's name
`ai_filter.placeholder`	The question box's placeholder
`ai_filter.public`	Also for users who are not signed in

Computed columns

An expression uses the report's columns by name (case doesn't matter; quote names with spaces as "Total pay"), numbers, text in single quotes, + - * /, || to join text, parentheses and these functions: abs ceil floor round trunc mod power upper lower initcap length trim substr left right replace concat coalesce nullif greatest least. Division returns a decimal number, and x / 0 gives an empty value instead of an error. Up to five per report; an expression that doesn't parse is shown as an error and left out, so the report keeps working.

sal * 12 + coalesce(comm, 0)
upper(ename) || ' (' || job || ')'
round(sal / 12, 2)

Row selection

"selection": {"column": "empno", "item": "P2_SELECTED"} puts a checkbox in front of every row (with select all in the header). When the page is submitted, the checked rows' values reach the item, colon separated (7839:7902), like a checkbox group; a process can then work on them:

sql

update hr.emp set active = false
 where empno = any(string_to_array(:P2_SELECTED, ':')::int[]);

The item must be on the same page and is usually hidden; it accepts posted values only because the selection names it. The values come from the browser, so treat them as user input (RLS and your process's own checks apply).

Rows chosen on one page of the report stay chosen on the others: every checked or cleared row is recorded in the item's session state right away (a small request in the background), so paging doesn't lose them, the report shows how many rows are selected, and a submit sends them all (the other pages' rows as hidden values). Without JavaScript only the rows of the page you submit from can be chosen. At most 5000 values; values may not contain :.

Saved reports (like APEX's saved interactive reports) belong to the signed-in user. A region with "public_reports": "ADMIN" lets users who pass that authorization scheme save public reports, which everyone who can see the report can apply; only the owner can delete a report. Saved reports are user data: they stay in meta.saved_report and aren't part of the application export.

Attributes (most of them are also in the page designer's Report settings form):

Key	Default	Meaning
`page_size`	`15`	Rows per page (the user can change it, up to 500)
`pagination`	X–Y of Z	`"range"`: "Rows X–Y" without a total, for large tables (see large tables)
`keyset`	none	With `"range"`: columns that make a row unique (e.g. `["id"]`); Next/Previous seek instead of using an offset (see large tables)
`max_rows`	none	Maximum row count: the report, its total and its downloads read at most this many rows (1 to 1,000,000)
`lazy`	`false`	Load the region after the page shows (see large tables)
`cache`	none	Keep the rendered region: `{"scope": "user" \| "session" \| "all", "seconds": 300}`
`searchable`	`true`	Show the search box (`false` = a "classic report")
`interactive`	`true`	Show the Actions menu (only when searchable)
`sortable`	`true`	Allow sorting (`false` keeps the query's order, e.g. for trees)
`mobile`	`"reflow"`	`"scroll"` keeps a horizontally scrolling table on phones
`hidden`	`[]`	Column names to leave out of the display (they can still be used in links). Users can't search, filter, sort, compute or aggregate on them either, also not by editing the URL
`headings`	`{}`	Column headings, e.g. `{"sal": "Salary"}`; by default `hire_date` becomes "Hire Date"
`formats`	`{}`	Format masks per column, e.g. `{"sal": "FML999G990D00", "hiredate": "DD-MON-YYYY"}` (see number formats)
`link`	none	Makes one column a link: `{"column": "empno", "page": 3, "items": {"P3_EMPNO": "#empno#"}}`. `#col#` is replaced by the row's value; the link carries a checksum and is hidden when the user may not open that page
`empty`	"No data found"	Text when there are no rows
`preformatted`	`[]`	Columns shown with preserved spaces (e.g. indented trees)
`saved_reports`	`true`	`false` hides saved reports for this report
`public_reports`	none	Authorization scheme whose users may save public reports
`pdf`	none	PDF layout, columns and widths; see report layouts
`selection`	none	Row selection: `{"column": "empno", "item": "P2_SELECTED"}` (see row selection)

Items placed in a report region appear in its toolbar; that's how filter fields (for example a department select list with submit_on_change) are made.

The CSV and Excel downloads use the current search, filters and sort, grouped by the control break column when there is one (up to DOWNLOAD_MAX_ROWS, default 1,000,000, or the report's max_rows). They are streamed from a database cursor, so the server holds one batch of 1,000 rows at a time. Text cells that start with =, +, - or @ are prefixed with ' in CSV so spreadsheets don't execute them. The PDF uses them too; see downloads and printing. Computed columns are included in the downloads; highlights, aggregates and the group by, pivot and chart views are shown on screen only.

`grid` (interactive grid)

An editable table on one table. Required properties: table_name (e.g. hr.dept), pk_column (single-column primary key) and a source SELECT that includes the key column:

sql

select deptno, dname, loc from hr.dept

It is saved by a process of type grid_dml whose region is the grid, which the wizard creates. Without such a process the grid is read-only.

A grid can also edit the rows of a REST data source (region property REST data source instead of a table; the key column is pk_column or the source's first key column): Save, Add row and Delete then call the source's update, insert and delete operations, and are offered only for the operations the source defines (chapter 19).

End users can edit cells inline, add rows (Add row), tick rows for deletion, search, page, and Save. On save:

only changed cells are written (each row posts its original values);
new rows with at least one value are inserted; blank ones are ignored;
ticked rows are deleted;
if any row fails (a constraint, trigger, RLS or a missing required value), nothing is saved and each problem is reported with its row number;
the user is warned before leaving the page with unsaved changes.

Which columns are editable: columns of table_name that appear in the SELECT, except the primary key, generated columns, GENERATED ALWAYS identity columns and readonly ones. Other selected columns (e.g. from a join) are shown read-only. Each existing row's key is signed, so a user cannot redirect an update or delete to another row by editing the page.

Attributes:

Key	Default	Meaning
`page_size`	`25`	Rows per page (max 200)
`pagination`, `max_rows`		As for reports
`allow`	all `true`	`{"insert": false, "update": true, "delete": false}`
`readonly`	`[]`	Columns that may not be edited
`columns`	`{}`	Per column: `{"deptno": {"lov": "LOV:DEPARTMENTS", "required": true}}`. `lov` makes it a select list (any list of values)
`headings`, `hidden`, `formats`		As for reports
`aggregates`		Totals in the footer: `{"sal": ["sum", "avg"], "empno": "count"}` (`sum`, `avg`, `count`, `min`, `max`)
`layout`		The default column layout: `{"order": ["ename", "sal"], "hidden": ["comm"], "widths": {"ename": 180}, "frozen": 1}`
`frozen`	`0`	Shorthand for `layout.frozen`: the first 1 to 5 shown columns stay in view while the grid scrolls sideways
`row_actions`		A menu per row: `{"edit": {"page": 3, "items": {"P3_EMPNO": "#empno#"}}, "duplicate": true, "delete": true, "links": [{"label": "Reviews", "page": 20, "items": {"P20_EMPNO": "#empno#"}}]}`
`select_row`		Makes it a master grid: `{"column": "deptno", "item": "P27_DEPTNO"}` (below)
`master`		Makes it (or a report, chart, cards …) a detail: `{"item": "P27_DEPTNO", "column": "deptno"}` (below)
`actions`	`true`	`false` hides the Actions menu (columns, aggregates, saved reports)
`saved_reports`, `public_reports`	`true`, none	As for reports

Cell editors follow the column type: number, date, date-time, checkbox (boolean), select list (with lov) or text.

Aggregates are computed in the database over every row of the search, not only the rows on the page, and shown in a footer row under their columns. Besides the developer's (aggregates), users add their own with Actions → Aggregate (kept in the URL as r<id>_a=fn|column, like a report's) and remove them with the × on their chip.

Column layout. Users arrange the grid for themselves: Actions → Columns lists every column with Shown, Position and Width (px), and how many columns are frozen (they stick to the left while the table scrolls sideways; the grid's leading columns, the delete box and the row menu, freeze along). With JavaScript, they can also drag a header to move a column and drag its right edge to resize it; the result is saved at once. A user's layout is kept per grid in meta.saved_report (kind layout; for the public user in the session) and applied whenever they open the page; Reset goes back to the developer's layout. Hidden columns are still part of the grid: their values are posted and saved as they were. The developer's hidden columns never show, whatever the layout.

Saved grid reports work like a report's (Actions → Saved reports): they keep the search, the user's aggregates and the column layout. Applying one makes its layout the user's own. public_reports names the authorization scheme whose users may share a report with everyone.

Row actions. With row_actions, each row gets a ⋮ menu: Edit opens a page with the row's values in its items (#column#; a signed link, shown only when the user may open that page, as a dialog when it is a modal page), Duplicate copies the row into a new, unsaved row, Delete ticks the row's delete box, and links adds more such links. Without JavaScript Duplicate is a link that shows the page again with the copy as a new row.

Master-detail. A master grid with select_row gets a radio-style link in front of each row; choosing one puts the row's column value into the page item item (a hidden item on the page). Regions with "master": {"item": …} are its details: they use the item in their query,

sql

select empno, ename, job, sal from hr.emp where deptno = :P27_DEPTNO::int

and show "Select a row above" until a row is selected. With JavaScript the details are refreshed in place (GET …/region/:id, no page reload); without, the link reloads the page. The selection is a signed link (bound to the application, page, user, region and value), so a user can only select a row the server showed them; the item is not taken from the URL or a submit otherwise. A detail grid with master.column fills that column with the selected value on its new rows (and never lets users edit it); saving new rows without a selection is refused.

Copy and paste. Click a cell and Shift+click another to select a range; Ctrl+C copies it as tab-separated text (which spreadsheets paste as cells). Ctrl+V pastes such text (from a spreadsheet or another grid) from the focused cell on, adding rows when the grid allows inserts; one value pasted into a selected range fills it, Delete empties the range. Read-only cells are skipped, select lists match a value or its display text. Pasted values are only saved by Save, through the same validation as typed ones.

The HR example's page 27 (Departments and staff) shows all of this: a master grid of departments, a detail grid of their staff (totals, a frozen name column, a row menu) and a detail report.

`form`

A form edits one row of table_name, identified by pk_column, whose value is kept in the item pk_item (usually a hidden item). Every item in the region with a source_column maps to that column.

Fetch: when the page is shown and pk_item has a value, the row is read into the items. A row that doesn't exist (or is hidden by RLS) gives "record not found".
Save: a process of type form_dml does the DML according to the pressed button (a form on a REST data source calls the source's insert, update and delete operations instead, and fetches its row through the source: chapter 19):

Button name	Operation
`CREATE`, `INSERT`, `ADD`	`INSERT` of the non-empty item values (empty columns get their default); the new key is stored in `pk_item`
`SAVE`, `UPDATE`, `APPLY`, `APPLY_CHANGES`	`UPDATE` of all editable mapped items
`DELETE`	`DELETE` (only validations with `when_button` `DELETE` run; required and list checks are skipped)

Typical form buttons (as generated by the wizard): Cancel (redirect), Delete (condition :P3_ID is not null), Apply Changes (same condition) and Create (condition :P3_ID is null).

The easiest way to get a form is the Report and form wizard, which opens the form as a modal dialog from the report, or the Form wizard for a form page on its own (see Create pages from a table). The Cards, Calendar, Map and Faceted search wizards can add a modal form page too.

`chart`

Source: the first column is the label; each further numeric column is a series, named by its column alias:

sql

select d.dname as department,
       sum(e.sal)  as "Salary",
       sum(e.comm) as "Commission"
  from hr.dept d left join hr.emp e using (deptno)
 group by d.dname order by 1

Attributes:

Key	Meaning
`kind`	`bar` (default), `column`, `stacked`, `line`, `area`, `combo`, `scatter`, `bubble`, `donut`, `pie`, `gauge`, `funnel`, `radar`, `gantt`, `pyramid` or `polar`
`link`	Drill-down: `{"page": 2, "items": {"P2_DEPTNO": "#deptno#"}}` makes every data point a link (see below)
`gauge`	For `gauge`: `{"min": 0, "max": 120, "warning": 80, "critical": 100}` (all optional)
`format_mask`	A number format mask for the values in labels, tips and the data table, e.g. `"FML999G990"` (see number formats)
`empty`	Text when there are no rows

Kind	Best for	Notes
`bar`	ranking categories with long labels	horizontal bars, values at the tips
`column`	comparing a few categories or series	values on the caps for ≤ 12 categories
`stacked`	parts of a total per category	one column per label with the series stacked on top of each other; negative values stack downwards
`line` / `area`	change over time	label column should be ordered (dates, years)
`combo`	two measures with one shared label	the first series is drawn as columns, the other series as lines over them
`scatter`	the relation between two numbers	the first column must be numeric (the x axis); each further column is a y value. Rows with an empty or non-numeric x are left out. The axes don't have to start at zero
`donut` / `pie`	parts of a whole (≤ 6 slices)	uses the first series; more than 6 slices fold into "Other"; zero and negative values are left out. The donut shows the total in the middle
`bubble`	three measures per item	label, then x, y and size columns; the bubble's area is proportional to the size. The axes don't have to start at zero
`gauge`	one value against a target, per row	a half dial per row (up to 12) from `min` (default 0) to `max` (default: rounded up from the values). With `warning` and/or `critical` thresholds each dial shows a status (On target, Warning, Critical) with an icon and a label, and the thresholds as a coloured ring. A `warning` above `critical` means low values are bad
`funnel`	stages of a process	the first series, in the query's order (sort it); each stage shows its share of the first stage
`radar`	several measures per series, side by side	one axis per row (3 to 12 rows), one polygon per series, all on one scale from zero
`gantt`	tasks over time	label, start, end (dates or timestamps; an empty end, or one equal to the start, is a milestone ◆), then optional columns by name: `progress` (0 to 100, the filled part of the bar), `task_id` and `depends_on` (the ids a task waits for: `3`, `3,4` or an array). A time axis in hours, days, weeks, months or years, a dashed line for now (in the session's time zone, like the timestamps the query returns), and elbow lines from the end of each predecessor to the start of the task. Rows without a start are left out
`pyramid`	levels of a hierarchy, or two groups compared per band	one series: a triangle cut into segments from the top (the first row) down, each segment's area in proportion to its value (≤ 8; more fold into "Other"). Two series: back-to-back bars (a population pyramid), the first series to the left, both on one scale; negative values count as their size
`polar`	values per period or direction (months, weekdays)	a polar area chart: one equal sector per row (up to 24) clockwise from 12 o'clock, the radius in proportion to the value on rings from zero; several series share a row's sector

A stacked chart, a combination of columns and a line, and a scatter plot:

sql

-- stacked: one column per department, a segment per job
select d.dname as department,
       count(e.empno) filter (where e.job = 'CLERK')    as "Clerk",
       count(e.empno) filter (where e.job = 'SALESMAN') as "Salesman",
       count(e.empno) filter (where e.job = 'ANALYST')  as "Analyst"
  from hr.dept d left join hr.emp e using (deptno)
 group by d.dname order by 1

-- combo: the budget as columns, the average as a line
select initcap(job) as job, sum(sal) as "Salary budget", round(avg(sal)) as "Average salary"
  from hr.emp group by job order by 2 desc

-- scatter: x = years of service, y = salary
select extract(year from age(current_date, hiredate))::int as "Years of service", sal as "Salary"
  from hr.emp where active order by 1

A bubble chart, gauges, a funnel and a radar (HR page 24 "Planner"):

sql

-- bubble: x = years of service, y = average salary, size = headcount (deptno only for the link)
select d.dname as department,
       round(avg(extract(year from age(current_date, e.hiredate)))::numeric, 1) as "Years of service",
       round(avg(e.sal)) as "Average salary", count(*) as "Employees", d.deptno
  from hr.emp e join hr.dept d using (deptno) group by d.deptno, d.dname

-- gauge, {"gauge": {"max": 120, "warning": 80, "critical": 100}}: one dial per department
select d.dname, round(100.0 * coalesce(sum(e.sal), 0) / 10000) as "Budget used", d.deptno
  from hr.dept d left join hr.emp e on e.deptno = d.deptno group by d.deptno, d.dname

-- funnel: stages in order
select 'Requested' as stage, count(*) as "Requests" from hr.leave_request
union all select 'Approved', count(*) filter (where status = 'APPROVED') from hr.leave_request

-- radar: an axis per job, a polygon per department
select initcap(job) as job,
       count(*) filter (where deptno = 10) as "Accounting",
       count(*) filter (where deptno = 20) as "Research"
  from hr.emp group by job order by 1

Gantt, pyramid and polar charts (HR page 32 "Project plan"):

sql

-- gantt: progress, task_id and depends_on by name; deptno only for the link
select t.name as "Task", t.starts as "Starts", t.ends as "Ends", t.progress,
       t.id as task_id, array_to_string(t.depends_on, ',') as depends_on, t.deptno
  from hr.project_task t order by t.starts, t.id

-- pyramid with two series: back to back per salary band
select b.band as "Salary",
       count(e.empno) filter (where e.deptno = 20) as "Research",
       count(e.empno) filter (where e.deptno = 30) as "Sales"
  from (values (1, '3000+', 3000, null), (2, '2000–2999', 2000, 3000)) b(k, band, lo, hi)
  left join hr.emp e on e.sal >= b.lo and (b.hi is null or e.sal < b.hi)
 group by b.k, b.band order by b.k

-- polar: a sector per month
select to_char(make_date(2000, m, 1), 'Mon') as month, count(e.empno) as "Hires"
  from generate_series(1, 12) m left join hr.emp e on extract(month from e.hiredate) = m
 group by m order by m

A Gantt chart reads dates as their wall clock (a timestamptz in the session's time zone), shows a time only when it is not midnight, and has its own data table (label, start, end, progress, dependencies). Its rows count toward the chart's row limit (max_rows, default 1000).

Up to 8 series; two or more get a legend. Every chart has hover/focus tooltips and a Data table toggle (the accessible alternative). Colours come from a palette checked for colour-vision deficiency, in light and dark mode; the gauge's status colours are reserved for status and always come with an icon and a label. Charts resize with the screen.

Drill-down links

With link, every data point is a link to a page, like a report link: #column# in the item values is replaced by the value of the point's row, and #series# by the name of its series (the column alias). The URL carries a checksum, so pages with session state protection accept it, and there are no links when the user may not open the target page.

json

{"kind": "column", "link": {"page": 9, "items": {"P9_DEPTNO": "#deptno#", "P9_JOB": "#series#"}}}

Columns that only the link refers to (here deptno) are not drawn as a series, so the query can return a key next to the label. What links: bars, columns and stacked segments (per series), line and area points, scatter dots and bubbles, gauge dials, funnel stages, radar axis labels, pie and donut slices and their legend entries (not "Other"), Gantt bars and milestones (the whole row: #series# is empty), pyramid segments and bars, and polar sectors and their labels. The marks are for the mouse; from the keyboard the data table has the same links (the labels, or each value when there are several series), and so do the radar and polar labels and the donut and pyramid legends.

`cards`

Source: a SELECT with any of these columns: title, subtitle, body, badge, icon (an icon name). Other columns can be used in the link.

sql

select d.dname as title, initcap(d.loc) as subtitle,
       count(e.empno) || ' employees' as body, d.deptno as badge, 'building' as icon, d.deptno
  from hr.dept d left join hr.emp e using (deptno) group by d.deptno

Attributes:

Key	Meaning
`style`	`"metric"`: KPI tiles with a big value (`badge`) and a label (`title`)
`link`	`{"page": 5, "items": {"P5_DEPTNO": "#deptno#"}}` makes each card a link
`empty`	Text when there are no rows
`max_rows`	Most cards shown (default 500); when there are more, "Showing the first 500 rows." follows
`formats`	Format masks for `title`, `subtitle`, `body` and `badge`, e.g. `{"badge": "FML999G999G990"}`

`calendar`

Source: a SELECT with start_date, optional end_date (inclusive, for multi-day events) and title, plus any columns used in the link:

sql

select l.start_date, l.end_date, initcap(e.ename) as title, l.id
  from hr.leave_request l join hr.emp e using (empno)

start_date and end_date can be dates (all-day events) or timestamps (events with a time; a timestamp at midnight without an end time counts as all-day).

Attributes:

Key	Meaning
`link`	`{"page": 7, "items": {"P7_ID": "#id#"}}`: each event links to a page (the edit link)
`views`	The views users can switch between, from `["month", "week", "day", "list"]` (default: all four)
`view`	The view shown first (default: the first of `views`)
`day_start`, `day_end`	The hours of the week and day views (default 8 to 18); widened when an event needs it
`create`	Create on click: `{"page": 7, "items": {"P7_START": "#start#", "P7_END": "#end#"}}`
`move`	Drag and drop: SQL that moves an event (see below)
`key`	The column that identifies an event for `move` (default `id`)
`move_authz`	An authorization scheme for dragging (default: everyone who sees the calendar)

Views. Month: a grid, Monday first, up to 4 events per day plus "+n more" (a link to that day); phones see an agenda list of the days with events. Week and Day: an "All day" row and a row per hour, each timed event in the hour it starts, with its times; on phones the week view is an agenda list too. List: the month's events per day. The buttons ‹, Today and › move by a month, week or day; Month / Week / Day / List switch views. Everything is a plain link (?r<id>_v=week&r<id>_d=2026-10-05, ?r<id>_m=2026-10 for month and list), so it works without JavaScript and can be bookmarked.

Create on click. With create, every day (month view, "All day" row) and every hour slot has a + link to the page, with #start#, #end# and #date# filled in: 2026-10-05 for a day, 2026-10-05 09:00 and 2026-10-05 10:00 for an hour. The link carries a checksum like every link (so the target page can keep session state protection on); with JavaScript a click anywhere on an empty part of the slot follows it. A date item takes the date; a date-time item shows a day as midnight.

Drag and drop. With move, users drag events with the mouse to another day or hour slot. The browser sends the event's key and the slot; the server checks the CSRF token, the page's and the region's authorization and condition, move_authz, and that the event is in the region's query for this user (as the application's database role, so row level security applies), works out the new start and end (an event keeps its length; a timed event dropped on an hour starts there, else it moves by whole days and keeps its time of day) and runs move as the application's role with three binds:

Bind	Value
`:EVENT_ID`	the key column's value
`:NEW_START`	`2026-10-07` or `2026-10-07 14:00` (the same form as the old start)
`:NEW_END`	the new end, or NULL when the event has none

json

{"move": "select hr.move_meeting(:EVENT_ID::int, :NEW_START::timestamp, :NEW_END::timestamp)"}

Put your own rules in the function (raise an exception with a message for the user, e.g. "Only the organizer can move this meeting."); the calendar redraws itself after a move and shows the error otherwise. Binds are replaced as literals outside quotes, so call a function rather than using them inside a DO block. Without a mouse (keyboard, touch, no JavaScript), the event's edit link is the way to change its dates.

`facets` (faceted search)

A panel of filters, each with a live count, that filters a report region on the same page. Counts take the search and all other facets into account. Three kinds of facet:

checkbox (the default): the most frequent values of a column, each with a checkbox. With "exclude": true the facet gets an Exclude the selected values switch: the chosen values are then left out instead (rows where the column is empty stay).
range: a number or date column in ranges, as radio buttons ("Any", then each range). A range includes its from and excludes its to, so ..1500, 1500..3000 and 3000.. don't overlap. With "custom": true (the default when no ranges are given) users can type their own from and to; those are inclusive (a date to includes the whole day).
star: a rating column as "5 stars and up", "4 stars and up", … down to 1 (max, default 5).

With "search": true the panel starts with a search field: the same search as the report's own (r<id>_q, the row as text contains the term).

Attributes:

json

{"report": 57,
 "search": true,
 "facets": [{"column": "job", "label": "Job", "exclude": true},
            {"column": "department"},
            {"column": "status", "limit": 5},
            {"column": "sal", "label": "Salary", "type": "range", "custom": true,
             "ranges": [{"to": 1500, "label": "Below 1500"}, {"from": 1500, "to": 3000}, {"from": 3000}]},
            {"column": "hiredate", "label": "Hired", "type": "range"},
            {"column": "rating", "type": "star", "max": 5}]}

Key	Meaning
`report`	The id of the report region to filter
`search`	`true`: a search field at the top
`facets[].column`	A column of the report's SELECT
`facets[].label`	Heading (default: the column name)
`facets[].type`	`checkbox` (default), `range` or `star`
`facets[].limit`	checkbox: most frequent values shown (default 12, max 50)
`facets[].exclude`	checkbox: `true` lets users exclude the chosen values
`facets[].ranges`	range: `[{"from": …, "to": …, "label": …}]`, numbers or `YYYY-MM-DD` dates; either bound may be left out (at most 20)
`facets[].custom`	range: `true` adds from/to fields (default: only when there are no `ranges`)
`facets[].max`	star: the highest rating, 2–10 (default 5)

Only filters that a facet on the page allows are read from the URL: a value for a column without a facet, a range that isn't one of the facet's own, or a from/to on a facet without custom is ignored. Values and bounds are sent as query parameters, never as SQL text. A range facet on a column that is neither a number nor a date shows a message instead.

Typical layout: facets region with columns: 3 and template collapsible, report with columns: 9. On phones the facets stack above the report. Without JavaScript an Apply button submits the panel; with JavaScript every change applies at once.

`smart_filters`

The compact alternative to a facets panel (APEX smart filters): one search field above a report, with the filters in use as chips (each with a × to remove it) and suggestions below it. Without a search term the suggestions are each facet's most frequent values (or its ranges); while the user types they are the values that contain the term, and choosing one replaces the term by that filter. If nothing matches, the term searches all columns of the report.

json

{"report": 57,
 "placeholder": "Search or filter employees…",
 "suggestions": 3,
 "facets": [{"column": "job", "label": "Job"},
            {"column": "department"},
            {"column": "sal", "label": "Salary", "type": "range",
             "ranges": [{"to": 1500}, {"from": 1500, "to": 3000}, {"from": 3000}]}]}

Key	Meaning
`report`	The id of the report region to filter
`facets`	As for `facets` (checkbox, range and star; `exclude` and `custom` are for the facets panel)
`suggestions`	Suggestions per facet, 0–10 (default 3)
`placeholder`	The text in the empty search field (translatable)

Everything is a link or a GET form on the report's own URL parameters, so it works without JavaScript, can be bookmarked and is kept in saved reports. A facets panel and smart filters may filter the same report; the first definition of a column on the page wins. Give the region template plain and columns: 12 above the report.

`display_selector` (region display selector)

A bar of tabs (or a select list) that shows one region of the page at a time, like APEX's region display selector. Regions take part through their own attributes:

"display_selector": true: a tab named after the region's title;
"display_selector": "Tab name": regions with the same name share one tab (e.g. a smart filters region and its report). The name is translatable.

json

{"style": "tabs", "show_all": true, "remember": true}

Key	Meaning
`style`	`tabs` (default) or `select`
`show_all`	`false` hides the Show all tab (default: shown)
`remember`	`false`: don't remember the chosen tab for the browser session (default: remembered per page)

Regions stay where the page puts them; regions hidden by a condition or authorization get no tab. Without JavaScript the bar is a list of links to the regions (#R<id>) and every region shows. With JavaScript the links become accessible tabs (arrow keys, Home and End), the other regions are hidden, and a link to #R<id> opens the tab of that region. In the page designer the display selector's settings list the page's regions with a checkbox and a tab name each.

The HR example's page 21 (Explore, examples/hr/hr_21_regions.sql) has a display selector with three tabs: smart filters over an employee report, a faceted search with a search field, an excludable job facet, salary ranges with from/to, a hire date range and a star rating, and a chart.

`map`

Source: a SELECT with one row per place. The position comes from lat and lng (or latitude/longitude), or from a location column holding latitude,longitude text (what a location item stores). Optional columns: title and body (the popup), and geojson (a GeoJSON geometry or feature, e.g. PostGIS st_asgeojson(geom)) to draw lines and areas; a GeoJSON point is a place like a row with lat/lng. With PostGIS installed, a geometry or geography column (in WGS 84, SRID 4326) is enough: the server turns it into GeoJSON.

sql

select dname as title, initcap(loc) as body, lat, lng, deptno
  from hr.dept where lat is not null

The map zooms to fit all places. Attributes:

Key	Meaning
`link`	`{"page": 5, "items": {"P5_DEPTNO": "#deptno#"}}`: the popup links there (only when the user may open that page)
`height`	`small`, `medium` (default) or `large`
`zoom`	Zoom level (1–19) when there is one place; default 14
`empty`	Text when no row has a position
`layer`	`markers` (default) or `heat`: a heat map of the places, each weighted by its `weight` column (default 1)
`cluster`	`true`: group markers that are close together (below)
`name`	The name of the region's own layer in the legend (default: the region title)
`layers`	More layers, each with its own query (below)
`visible_area`	`true`: load only the places in the visible area, again when the map moves (below)
`tiles`	`true`: serve the layer as vector tiles, for large data sets (below)
`report`	The id of a report region on the same page that the map filters (below)
`filter`	`area` (default) or `distance`: how the map filters that report

Heat map. With "layer": "heat" the places are drawn as a heat map instead of markers: where places (or heavier weights) are close together, the colour is darker. It suits many points, such as visits, incidents or sales. A legend (fewer → more) sits in the corner. GeoJSON shapes are still drawn.

sql

select lat, lng, sal as weight from hr.emp join hr.dept using (deptno)   -- {"layer": "heat"}

Marker clustering. With "cluster": true markers that are close together at the current zoom level are drawn as one round marker with their number; clicking it zooms in to them. Zooming regroups them, and at the two highest zoom levels every place is shown by itself. Use it for hundreds or thousands of places (a map shows at most 5,000 per layer).

Several layers (APEX: map layers). The region's query is the first layer; layers adds up to seven more, each with its own query (the same columns as above) and its own settings:

json

{"name": "Visits", "cluster": true,
 "layers": [
   {"name": "Offices", "source": "select lat, lng, dname as title, deptno from hr.dept",
    "link": {"page": 5, "items": {"P5_DEPTNO": "#deptno#"}}},
   {"name": "Sales areas", "source": "select dname as title, st_asgeojson(area) as geojson from hr.sales_area"},
   {"name": "Visit density", "source": "select lat, lng from hr.field_visit", "layer": "heat", "hidden": true}
 ]}

Each layer has name (shown in the legend, translatable like other texts), source, and optionally layer (markers or heat), cluster, link, hidden (off until the user switches it on), visible_area and tiles. On a map with more than one layer each layer's places, lines and areas get their own colour, and a Layers legend in the corner switches them on and off. Without JavaScript the list below the map has a part per layer. A layer whose query fails shows its error above the map; the other layers are still drawn. In the Page Designer the map's settings have a fieldset per layer (and one empty to add a layer; emptying a layer's query removes it), and the Advisor checks every layer's query. The HR example's page 33 (Field visits) has four layers: clustered customer visits, the offices, sales areas with delivery routes, and a heat map of the visits.

Large data sets: the visible area and vector tiles (APEX: map layers loaded by the visible area, vector tiles). By default a layer's rows come with the page (at most 5,000). Two settings load them as the map needs them instead, per layer (Load in the map's settings):

"visible_area": true (Rows in the visible area): the page carries no places for the layer. Once the map shows, and again a quarter second after each move or zoom, the browser asks for the places in the visible area (…/map/<region>/layer/<n>?bb=south,west,north,east) and draws them as usual (markers, clusters, a heat map, lines and areas). At most 2,000 come back; when there are more, the map says Not every place is shown: zoom in to see them all.
"tiles": true (Vector tiles): the layer is served as Mapbox Vector Tiles (MVT 2.1), …/map/<region>/tiles/<n>/<z>/<x>/<y>.mvt: each 256-pixel tile holds the rows in its area (with a small margin, at most 10,000 per tile). The browser fetches only the tiles it shows and draws the places as dots and the lines and areas in the layer's colour on a canvas; a click opens the popup of the place, line or area under the pointer. Tiles suit tens or hundreds of thousands of rows; they have no clustering and no list below the map. Any MVT client (MapLibre, OpenLayers, QGIS) can read the same URLs from a signed-in session.

Both filter in the layer's SQL on the server, like the report filter below: on a PostGIS geometry/geography column when PostGIS is installed, else on lat/lng (or location), so an index on those columns (or a spatial index) keeps them fast. A layer without position columns shows an error. The query runs as the application's role with the session's item values, after the same checks as the page (page access, the region's condition and authorization). The HR example's page 41 (Weather stations) has 20,000 stations as vector tiles and the stations above 2,000 m by the visible area.

Filtering a report by the map area (APEX: map as a spatial filter). Give the map "report": <region id> of an interactive report on the same page. When the user moves or zooms the map, a Show this area in the list button appears. It reloads the page with the report showing only the rows in the map's visible area, with a removable Map area chip and a Show everything button on the map. The report needs position columns like a map's (lat/lng, latitude/longitude or location). Without them the chip is marked and nothing is filtered. The area is in the URL (r<id>_bb=south,west,north,east), so it can be bookmarked and saved with a saved report. It also applies to the report's downloads. The HR example's page 16 (Locations) has a heat map of the payroll and an offices map that filters the employee list.

With "filter": "distance" the button reads Show places within … km of the centre instead: the distance is from the map's centre to its nearest edge, and the report shows the rows within that distance (a Within … km chip; the map draws the circle). In the URL it is r<id>_near=latitude,longitude,km. The HR example's page 33 filters its list of visits this way.

Spatial filtering on the server, with or without PostGIS. Both filters run in the report's SQL, never in the browser. When the PostGIS extension is installed in the database and the report has a geometry or geography column (one named geom, geometry, geog, geography, the_geom, shape or location first, else the first such column), pgkiln uses PostGIS: the area becomes ST_Intersects(column, ST_MakeEnvelope(west, south, east, north, 4326)) (two envelopes across the antimeridian) and the distance ST_DWithin(column::geography, point, metres), so spatial indexes can be used and lines and areas count when they touch the area. Geometry columns are expected in WGS 84 (SRID 4326). pgkiln finds PostGIS by itself (it looks in pg_extension once a minute) and calls its functions in the extension's schema, so the application's database role needs USAGE on that schema (PostGIS's default, public, has it). Without PostGIS, or for a report without such a column, the report's lat/lng (or location) columns are compared with numbers: a bounding box for the area, and for the distance a box around the circle first and then the great-circle (haversine) distance on a sphere of 6,371 km. The numbers in the URL are parsed and range-checked first (anything else is ignored), so no text from the URL reaches the SQL.

Below the map a collapsed list names every place (except for layers loaded by the visible area or as vector tiles), so the data is reachable without JavaScript and by screen readers. The map uses Leaflet (shipped with pgkiln, loaded only on pages with a map) and tiles from OpenStreetMap. Their tile usage policy suits light use; for production set MAP_TILE_URL (and MAP_ATTRIBUTION) to your own or a commercial tile server, e.g. https://tiles.example.com/{z}/{x}/{y}.png. The Content-Security-Policy allows images from that server only.

`tree`

Source: a SELECT returning id, parent_id and label, and optionally icon (an icon name). Rows whose parent isn't in the result are the top level; no recursive query is needed.

sql

select empno as id, mgr as parent_id, initcap(ename) || ' · ' || initcap(job) as label
  from hr.emp

Attributes: expanded (levels open at first, default 1), link (as for maps; #id# and any other column), empty. The tree is drawn on the server with <details>: it works without JavaScript, and the browser's find-in-page opens closed branches. Clicking a label follows the link; the rest of the row opens and closes the branch. Up to 5000 nodes.

`list` (lists)

A list (APEX: Shared Components → Lists) is a named set of links kept under Shared Components → Lists. A list region shows it; the same list can also be the application's navigation menu or navigation bar (Settings → Theme: Navigation menu list, Navigation bar list). A list is:

static: its entries (Shared Components → Lists → a list → Add entry), each with a label, an icon, a target, a badge, a description, a parent entry (a sub menu, up to 6 levels), a sequence, a condition (SQL), an authorization scheme and a build option; or
sql: the rows of a query, run as the application's database role with the usual binds:

sql

select d.dname as label, 2 as page, null::jsonb as items, 'building' as icon,
       (select count(*) from hr.emp e where e.deptno = d.deptno)::text as badge,
       initcap(d.loc) as description
  from hr.dept d order by d.dname

Only label is required; the other columns are page, items (JSON object of item values), url, icon, badge, description, and id / parent_id for nesting. At most 500 rows.

An entry's target is a page of the application with item values ({"P3_ID": "&P2_ID."}; links carry the checksum like other generated links), a path inside the application (2?tab=open), or an http(s):// address (opened with rel="noopener noreferrer"); other URLs (javascript:, //host, ../) are refused when saved and left out when they come from a query or a substitution. Labels, badges and descriptions take &ITEM. substitutions and are always escaped.

Entries the user may not see are left out: a failing condition or authorization scheme, an excluded build option, or a target page the user may not open (the page's authorization, like the navigation menu); a heading without a target and without visible children too. The entry for the current page is marked (aria-current="page"); in the navigation menu its parents are open.

Attributes (the region's Settings in the page designer):

Key	Meaning
`list`	The list's name (upper case)
`template`	`links` (default: nested links), `badges` (a row of tiles with the badge as the value), `cards` (a card per entry with its description) or `tabs` (the top-level entries as tabs)

The HR example's page 31 (Shortcuts) shows a static list with each template (with a badge from an item, child entries, a manager-only entry and one of an excluded build option) and a department list from a query; its navigation bar is the list HR_NAVBAR.

`data_reporter` (Data Reporter)

A Data Reporter region (APEX 26.1: Data Reporter) lets business users build their own reports in the running application, without the developer writing a page per question. The developer decides what may be reported on; users decide how.

The developer places the region (page designer gallery: Data Reporter) and, in its Settings, adds data sources: a table or view each (pgkiln's own meta schema and the system schemas are never offered), with a static id, a label and a description users see. Every column of the source is listed: tick the ones users may use and give them labels and, for numbers and dates, a format mask. New sources offer all columns except binary ones at first. A view that shows exactly what business users may see is often the best source; the builder warns when the application's database role may not read it.

Users choose a source and Start, then, in the report's editor (a plain form: it works without JavaScript, and the URL of a result can be bookmarked or sent):

Columns to show (all offered columns when none are ticked);
up to five filters (column, operator, value; the operators of interactive report filters);
up to three Group by columns and five totals (count, sum, average, minimum, maximum of a column, or the number of rows). With a group column the result has one row per group (at most 1000); totals without one give a single row over all rows;
up to three sort columns (in a grouped report: the group columns and the totals);
a chart (bar, column, line, area, donut, pie) of the totals by the first group column, drawn on the server like other charts.

Signed-in users save a report under a name (with a description), privately or shared with everyone who can see the region, open their own and shared reports from the region's list, change their own (Save, Save as new, Delete) and copy someone else's shared report by saving it under their own name. Detail rows are paged (page_size, 25 by default).

How it stays safe:

reports run as the application's database role: grants and row level security apply, so two users running one shared report may see different rows;
a definition (from the URL or a saved report) is checked before it runs: only offered columns that exist and can be read, whitelisted operators and functions (sum and average on numbers only), bounded counts and lengths; anything else is dropped. Identifiers are only the developer's schema, table and column names, always quoted; filter values are escaped literals;
reports are stored in meta.data_report, reached by applications only through the view meta.data_reports (the user's own and shared reports of the application) and the functions meta.save_data_report / meta.delete_data_report, which only change the signed-in user's own reports. Saving and deleting need the CSRF token and a region the user can see.

Attributes (written by the region's Settings):

Key	Default	Meaning
`sources`		`[{"id": "orders", "label": "Orders", "description": "…", "schema": "sales", "table": "order_v", "columns": [{"name": "total", "label": "Total", "format": "FML999G990D00"}]}]`
`sharing`	`true`	`false`: reports stay private
`share_authz`		An authorization scheme: only users that pass it may share reports
`page_size`	`25`	Rows per page of a detail report (5 to 200)
`empty`	`No data found.`	Text when there are no rows

The sources travel with the application export (they are the region's attributes); users' reports are user data, like saved reports, and stay with their region when an application is replaced. The HR example's page 36 (My reports) offers employees (a view without the user name and photo columns) and leave requests (row level security: an employee sees only their own), with King's shared report Salary by department.

`ai_assistant` (AI assistant)

An AI assistant region (APEX: AI Assistant, AI agents and tools) is a chat with an AI service (Claude or OpenAI) that an administrator configured and allowed for the application. The developer gives it a system prompt and, optionally, context queries and tools; the model then answers questions about the application's data, calling the tools when it needs to, like an agent. It works without JavaScript (the message is posted and the page comes back with the answer); with JavaScript the Send button shows that the assistant is thinking and Ctrl+Enter sends.

Conversations are kept per session and region (meta.ai_conversation): a follow-up question sends the earlier messages along. New conversation starts over; signing out (or the session's end) deletes it. Another user, or another session of the same user, never sees it. A conversation holds at most max_turns questions (20).
Context queries run before each question, as the application's database role (grants and row level security apply), read only, with :AI_PROMPT bound to the question (and :APP_USER, items as usual). Their rows (at most max_rows, 50) go to the model as delimited data (<data name="context:policy">…</data>) in the user's turn: a simple retrieval (RAG) over tables you choose, e.g. full-text search on policy texts.
Tools are SQL queries or REST data sources the model may call with arguments you declare (parameters: string (optionally an enum), integer, number, boolean or date; "optional": true allows none). pgkiln sends them as strict tools (JSON schemas with every property required, no others), checks the model's arguments against them again and runs the tool:
- a SQL tool runs as the application's role; the arguments are bind variables (:NAME, escaped literals, never SQL text) next to the usual :APP_USER and items (parameter names can't start with APP_). The SQL is one query (wrapped as a subquery, at most max_rows rows, a 15 s time limit) and is rolled back afterwards, unless the tool has "writes": true (for a function that changes data, e.g. one that files a request). Errors go back to the model as the message a user would see;
- a REST tool ("type": "rest", "source": "NAME") calls a REST data source of the application; the arguments are its parameters (as given: &ITEM. in them is not substituted), other parameters keep their defaults;
- "authz": "SCHEME" offers a tool only to users who pass the authorization scheme.
The model gets at most max_rounds (5) rounds of tool calls per question, then must answer.
The answer is shown escaped, with paragraphs, bullet and numbered lists, **bold** and `code` as the only formatting. It is never run as SQL or HTML.
Signed-in users only, unless the application has no sign-in or the region says "public": true (every question costs tokens). The service's daily limits apply to every call to the provider (a question with tool calls makes several); each call is logged in the AI usage log with the source assistant.

What goes to the provider: the system prompt (with &ITEM. values as delimited data, never the session id or passwords), what the user types, the context rows and the tools' results. Choose context queries and tools that return only what the user may see anyway (row level security helps).

Attributes (written by the region's Settings; they travel with the application export):

Key	Default	Meaning
`service`		The AI service's name
`system`		The system prompt (`&ITEM.` substitutions as delimited data)
`welcome`		The first message shown (translatable)
`placeholder`		Placeholder of the message box
`context`		`[{"name": "policy", "sql": "select title, body from hr.policy where … :AI_PROMPT …", "max_rows": 5}]`
`tools`		`[{"name": "my_leave", "description": "…", "sql": "select … where status = :STATUS", "parameters": {"STATUS": {"type": "string", "enum": ["PENDING", "APPROVED"], "optional": true}}, "max_rows": 50, "writes": false, "authz": "SCHEME"}, {"name": "weather", "type": "rest", "source": "WEATHER", "description": "…", "parameters": {"city": {"type": "string"}}}]`
`max_rounds`	`5`	Rounds of tool calls per question (1 to 10)
`max_turns`	`20`	Questions per conversation (1 to 100)
`public`	`false`	Also for users who are not signed in
`error_message`		Shown instead of the service's error (not for daily limits)

The HR example's page 38 (HR assistant) has an assistant with a context query over hr.policy and four tools (the user's own leave, colleagues, departments, and request_leave, which files a leave request), next to a staff list with Ask in your own words.

Template components

A template component (APEX 23.1+) is a piece of HTML with placeholders, kept under Shared Components → Template components and used in two places:

as a region of type template_component: one instance per row of the region's query (or once, without a query), or all rows inside the component's wrapper;
as a column template of a report: the cells of one column are drawn by the component.

A component has a static id (e.g. status_badge; regions and columns refer to it by this id), a name, a version, the template of one instance, an optional wrapper, layout classes and custom attributes.

Built-in template components

Every application can use these without importing anything (APEX: the Universal Theme's template components, with 26.1's Metric Card and group support). Show a region as multiple to get the group:

Static id	Component	Attributes (default: the column of that name)	As `multiple`
`ut_avatar`	Avatar: a picture or initials	`NAME`, `INITIALS`, `IMAGE` (a relative or https URL), `SIZE` (small, medium, large), `SHAPE` (circle, square)	An avatar group (overlapping)
`ut_badge`	Badge coloured by a state	`LABEL`, `STATE` (success, warning, danger, info, neutral; approved, pending, … are understood)	A row of badges
`ut_comments`	Comments	`USER`, `INITIALS`, `DATE`, `COMMENT`, `ACTIONS` (extra text)	One conversation
`ut_media_list`	Media list: picture or initials, title (linked when the region has a link), description, badge	`TITLE`, `DESCRIPTION`, `INITIALS`, `IMAGE`, `BADGE`, `STATE`	One list
`ut_metric_card`	Metric card: a key figure with its unit, change and trend	`LABEL`, `VALUE`, `UNIT`, `CHANGE`, `TREND` (up, down, flat), `GOOD` (up or down: which direction shows green), `DESCRIPTION`	A row of cards
`ut_timeline`	Timeline: when, who, title, text, a coloured marker	`TITLE`, `WHEN`, `WHO`, `BODY`, `STATE`	One timeline

They also work as report column templates (e.g. ut_badge on a status column). To change one, open Shared Components → Template components → Add and choose Copy into this application: the application's component with the same static id then replaces the built-in one everywhere in the application. HR page 39 Team overview uses all of them except the badge.

The template language

html

<span class="tc-badge tc-badge-{case STATE/}{when ok,approved/}success{when late/}danger{otherwise/}neutral{endcase/}">#LABEL#</span>

Syntax	Meaning
`#NAME#`	a custom attribute, a column of the row, `#LINK#`, `#APEX$ROW_NUM#` (and in a wrapper `#APEX$ROW_COUNT#`). Always HTML-escaped; there is no raw form (`#NAME!RAW#` is refused)
`#NAME!STRIPHTML#`	the value with tags removed, then escaped
`{if NAME/}…{elsif ?NAME/}…{else/}…{endif/}`	`NAME`: true (not empty, not `N`/`no`/`false`); `?NAME`: not empty; `!NAME`: not true
`{case NAME/}{when a/}…{when b,c/}…{otherwise/}…{endcase/}`	compare a value (ignoring case) with one or more values
`{loop "," NAME/}#APEX$ITEM# #APEX$I#{endloop/}`	each part of a value split on a separator (default `:`)
`#APEX$ROWS#`	in the wrapper only, exactly once: where the rows go

Templates may come from plug-in files someone else wrote, so they are held to more than "developer HTML is trusted". When a template is saved, imported and rendered, pgkiln checks it against an allow-list:

only ordinary content elements (div, span, p, a, img, ul, table, time, …): no <script>, <style>, forms, frames, <svg> or comments;
no event handler (on…), style or data-* attributes; every attribute value is quoted;
placeholders and directives only in text or inside a quoted attribute value, and a directive block starts and ends in the same place, so leaving a branch out never leaves half a tag;
href, src and cite allow only http(s), mailto:, tel: and relative URLs. They are checked again after substitution: a value like javascript:alert(1) drops the attribute.

So whatever the data, the page gets exactly the template's elements and attributes. For links to pages of the application use #LINK#: pgkiln fills it with a checksummed URL (and opens modal pages as a dialog). The look comes from classes (the Content-Security-Policy blocks inline styles): tc-badge (-success, -warning, -danger, -info, -neutral), tc-card, tc-card-head, tc-avatar, tc-title, tc-meta, tc-body, tc-actions, tc-stack, tc-row, tc-muted, tc-timeline, tc-timeline-item (tc-state-success, …), plus everything else in app.css.

Layout classes set how a region lays out the instances: tc-list (default, one below the other), tc-grid (responsive columns), tc-inline (in a line), tc-divided (with rules), tc-compact.

Custom attributes (JSON) are the component's settings, filled in where it is used:

json

[{"name": "LABEL", "label": "Label", "type": "text", "default": "#STATUS#"},
 {"name": "STATE", "type": "select", "options": ["ok", "late", "other"]},
 {"name": "COMPACT", "type": "checkbox"}]

Types: text, number, select (with options), checkbox (Y/N). A value (or default) may contain #column# and &ITEM. substitutions, so "default": "#STATUS#" makes the component work on any query with a status column. LINK is reserved.

The component's page in the builder shows its attributes, the columns it expects and a preview with sample rows (NAME=value per line, a blank line between rows).

As a region

Choose Type template_component and a Source SELECT (or none, for one instance from the attributes). Under the region, Template component settings writes its config:

json

{"component": "contact_card",
 "attributes": {"SUBTITLE": "#job#"},
 "display": "multiple",
 "link": {"page": 3, "items": {"P3_EMPNO": "#empno#"}},
 "max_rows": 50, "empty": "No colleagues yet"}

display: each (default) or multiple (all rows in the component's wrapper, e.g. one <ol class="tc-timeline">). link fills #LINK# (empty for users who may not open the page). At most 500 rows.

As a report column template

Under a report region, Column templates picks a component per column, with attribute values (NAME=value per line). The template sees every column of the row as #NAME# (also hidden ones), and #LINK# is the report's link. Stored in the region's config:

json

{"column_templates": {"status": {"component": "status_badge", "attributes": {"STATE": "#status#"}}}}

Column templates apply to the report view (not to downloads, which keep the plain value).

Plug-ins

A component travels as one JSON plug-in file ("format": "pgkiln-plugin/1", "type": "template_component"): Download plug-in file on its page, and Import a plug-in under Shared Components → Template components → Add (optionally replacing a component with the same static id). The template is checked before anything is saved. In SQL:

sql

select meta.export_template_component(<app id>, 'status_badge');
select meta.import_template_component(<app id>, '<plug-in json>'::jsonb, p_replace => false);

examples/plugins/ has three to start from: status-badge, contact-card and timeline-item. The HR example installs them (examples/hr/hr_19_template_components.sql): page 19 (Team) shows contact cards linking to the employee form, recent hires on a timeline, and leave requests with a status badge. Template components are part of an application export (template_components).

Plug-ins with their own code

APEX's region, item, dynamic action and process plug-ins. A plug-in file ("format": "pgkiln-plugin/2") brings, by type:

`type`	Brings	Used as
`region`	a template component that renders the region's rows, and JavaScript that gets the region	a region of type `plugin`
`item`	JavaScript that enhances a text field (the value is posted and stored as text, so the form works without it)	an item of type `plugin`
`dynamic_action`	a JavaScript function	a dynamic action with action `plugin`, Code the plug-in's name
`process`	a PL/pgSQL function `schema.fn(attributes jsonb) returns text` (the message), usually created by its install SQL	a process of type `plugin`

Every plug-in has custom attributes (like a template component's: text, number, select, checkbox, with defaults); a region, item or process sets their values in config, a dynamic action in its config:

json

{"plugin": "show_more", "attributes": {"VISIBLE": "4", "BUTTON": "Show everyone"}}

Values may contain &ITEM. substitutions. JavaScript and CSS are static application files that come with the plug-in; pages that use it load them. The JavaScript registers the plug-in by name:

pgkiln.plugins.register('show_more', ({ type, element, item, attributes }) => {
  // region and item plug-ins: element is the region's (or field's) element; called again after a refresh
  // dynamic action plug-ins: the action's context (items, elements, region, value, message) and attributes
});

The page passes the plug-in's name and attribute values in data-plugin and data-plugin-attrs (and in the dynamic action's JSON); code never travels in the page, so the Content-Security-Policy stays script-src 'self'. A region plug-in's template is an ordinary template component (checked against the allow-list above); its attributes are the plug-in's, and a region Source SELECT gives it rows.

Install SQL (tables, functions, grants a plug-in needs) is shown under Shared Components → Plug-ins and runs only when a developer asks, as the application's database role, in one transaction. A plug-in's code runs with the application's rights: install only plug-ins you trust, after reading their files.

A plug-in file is built from a source directory with pgkiln plugin build <dir>: plugin.json (the file without contents: type, name, label, version, help, attributes, files, template_component, sql_function), the files it lists, template.html (and wrapper.html) for a region, and install.sql. pgkiln plugin install <file|dir> --app <alias> adds it to an application; so does Import a plug-in in the builder and, in SQL, meta.import_plugin(<app id>, '<plug-in json>'::jsonb, p_replace => false). Download plug-in file gives it back. Plug-ins are part of an application export (plugins; shared/plugins/ in a directory export).

examples/plugins/ has four, each as a source directory and a built file: show-more (region: the first rows and a Show all button), char-counter (item: a maximum length with a live count), copy-value (dynamic action: copy an item's value to the clipboard) and log-event (process: record an event in pgkiln_plugins.event_log). HR example part 47 installs them and uses all four on page 40 (Plug-ins).

`static` and `dynamic` content

static: source is HTML written by the developer, with &ITEM. substitutions (HTML-escaped). Items placed in the region are rendered below the HTML.
dynamic: source is a SELECT; the first column of each row is output as HTML, like APEX's "PL/SQL Dynamic Content". The SQL is trusted, so escape data with meta.html_escape():

sql

select '<ul>' || string_agg('<li>' || meta.html_escape(ename) || '</li>', '') || '</ul>'
  from hr.emp where job = 'MANAGER'

A dynamic region outputs at most max_rows rows (default 1000).

The content security policy blocks inline <script>, event handler attributes, style="…" attributes and <style> blocks in both: use the classes of /static/app.css (for example muted, lead, alert alert-success, tag, btn, cards/card) instead. A browser ignores blocked styles and reports them in its developer console.

Large tables

pgkiln is meant for tables of any size. Reports and grids page in the database (limit / offset), so only one page of rows reaches the server; the settings below keep the rest of the page fast too. The HR example's page 25 (Large tables, examples/hr/hr_25_large_tables.sql) shows them on 200,000 generated rows.

Pagination. By default a report shows "1–15 of 2,345", which counts every row the search and filters leave. On a large table that count reads the whole result. With "pagination": "range" (page designer → Report settings → Pagination: Row ranges) the report shows "Rows 1–15" and reads one row more than it shows to know whether there is a next page, like APEX's "row ranges X to Y" pagination. Grids take the same setting.

Keyset paging. An offset still makes the database read and skip every row before the page, so page 8,000 is slower than page 1. A row-range report with "keyset": ["id"] (Report settings → Keyset columns: columns that make a row unique and are not null, ideally the primary key) pages by position instead ("seek" paging): the report is ordered by the user's sort column (if any) and then the keyset columns, and Next and Previous carry the last or first row's values in a signed URL parameter (r<id>_k). The next page is where (id) > ($1) order by id limit 16, which an index answers directly, however deep the page, and rows added or removed meanwhile don't shift the pages. The values are always query parameters; a position that is not signed by the server for this region, sort and page, is too large, or doesn't fit the column's type is ignored, and the report pages with the offset as before. Offset paging is also used without a position (a page number typed in the URL), for a control break, or in the group by, pivot and chart views. Search, filters and sorting work as usual: a new sort or filter starts on page 1.

Maximum row count. "max_rows": 10000 caps what a report reads: its total is counted over at most 10,001 rows ("1–15 of more than 10000"), the pager stops at the last page within the maximum, and the downloads hold at most that many rows. Page numbers beyond it show the last page; the server also limits page numbers (1,000,000) and page sizes (500, grids 200), whatever the URL says.

Row limits. Regions without paging read at most max_rows rows:

Region	Default	When cut off
`cards`	500	"Showing the first 500 rows."
`chart`	1000	the same note under the chart
`dynamic`	1000
lists of values (an item's `max_rows`)	5000	(up to 50,000)

Calendars (2,000 events), maps (5,000 places per layer; 2,000 per visible area, 10,000 per vector tile) and trees have their own limits.

Lazy loading. "lazy": true (report, chart, cards, dynamic, tree and template component regions) sends the page with a placeholder; the browser then fetches the region from GET /a/<alias>/<page>/region/<id> (with the page's query string, so paging and filters apply) and puts it in place. One slow chart no longer delays the whole page. The request is checked like the page: the session, page access, and the region's condition and authorization scheme. Without JavaScript the placeholder is a link that shows the page with the region in it (r<id>_load=1).

Region caching. "cache": {"scope": "user", "seconds": 300} keeps the region's rendered HTML in the server's memory for 1 second to 1 day (same region types as lazy loading):

Scope	Shared by
`session`	one session
`user`	the user's sessions
`all`	all users with the same roles

The cache key also holds the application, page, region definition, language, the request's query string and the values of the items the region refers to (and of its own items), so a region is never shared across applications, and a changed region or item value renders anew. A submit of the page empties its cached regions; a dynamic action's Refresh region renders the region anew. Regions whose links carry per-user checksums are not cached for all users, the session's CSRF token is never cached, and a region that shows an error is not cached. A lazy region that is in the cache shows at once. Each server process has its own cache: REGION_CACHE_MAX_ENTRIES (default 1000) and REGION_CACHE_MAX_MB (default 64) bound it, and one region over 2 MB is not cached. Use it for regions that are expensive and may be a little old (dashboards, summaries).

Pages and regions ​

Pages ​

Dialogs and drawers ​

Layout ​

Template options ​

Region types ​

Regions on a REST data source ​

report (interactive report) ​

Natural-language filters (Ask in your own words) ​

Computed columns ​

Row selection ​

grid (interactive grid) ​

form ​

chart ​

Drill-down links ​

cards ​

calendar ​

facets (faceted search) ​

smart_filters ​

display_selector (region display selector) ​

map ​

tree ​

list (lists) ​

data_reporter (Data Reporter) ​

ai_assistant (AI assistant) ​

Template components ​

Built-in template components ​

The template language ​

As a region ​

As a report column template ​

Plug-ins ​

Plug-ins with their own code ​

static and dynamic content ​

Large tables ​

Pages and regions

Pages

Dialogs and drawers

Layout

Template options

Region types

Regions on a REST data source

`report` (interactive report)

Natural-language filters (Ask in your own words)

Computed columns

Row selection

`grid` (interactive grid)

`form`

`chart`

Drill-down links

`cards`

`calendar`

`facets` (faceted search)

`smart_filters`

`display_selector` (region display selector)

`map`

`tree`

`list` (lists)

`data_reporter` (Data Reporter)

`ai_assistant` (AI assistant)

Template components

Built-in template components

The template language

As a region

As a report column template

Plug-ins

Plug-ins with their own code

`static` and `dynamic` content

Large tables