Architecture and integration contract

Hopya is a self-hosted, single-instance task management application. Node 24 runs AdonisJS 7 and Astro. React islands render the interactive workspace. Lucid owns application queries, transactions, snapshots, and schema migrations for SQLite in WAL mode or PostgreSQL. Optional live Table connections use request-owned Knex clients for external PostgreSQL, MySQL, or SQLite schemas through an Effect failure boundary. Optional S3 or filesystem storage holds private attachments. No background cloud connection is required.

Adonis encryption is configured explicitly with the legacy driver and the existing APP_KEY, preserving signed cookies issued by the previous Adonis 6 runtime. Cookies still only identify random session tokens; database expiry, revocation and account checks remain authoritative. The framework upgrade does not change the SQLite schema or rotate operator secrets.

ops/init-env.ts is a dependency-free operator initializer, not an authentication bypass: it atomically creates a new private .env with independent random application/setup keys and an API-only automation credential keyring containing one random 32-byte AES key, never replaces existing configuration, prints no generated secret, and creates no account. Native development defaults to loopback and reads the configured frontend HOST/PORT; Docker continues to use its explicitly private proxy topology.

The pinned implementation notes are retained below for maintainers. For routine API work, use the structured REST reference above. Historical test counts describe upstream work, not new checks run for this website.

Base /api/v1 unless the path below already includes it or is /health. Success responses are JSON objects or arrays; the paginated item endpoint explicitly returns {items,nextCursor}. Errors before response headers use { error: string }; failed in-flight bulk streams abort rather than pretending a truncated document is complete. Hopya resource IDs are UUID strings and timestamps are ISO-8601 UTC; live SQL record IDs/cursors are opaque source-key tokens and their revisions are value hashes. Dates are YYYY-MM-DD or null. Browser sessions use HttpOnly cookies. All unsafe browser requests require an Origin matching APP_URL, including login/setup; the Vite development proxy preserves the browser origin. Bearer tokens provide programmatic authentication without cookies. See REST reference for examples and limits.

• GET /health returns service readiness.
• GET /api/v1/config returns { landingEnabled, registrationEnabled, setupRequired, ssoEnabled, aiEnabled, passwordResetEnabled, showSidebarAttribution, logo? } with safe public feature/branding metadata only.
• POST /auth/setup {name,email,password,setupToken} creates first site admin, only with operator setup secret.
• POST /auth/login {email,password}; POST /auth/logout; GET /auth/me returns user {id,name,email,isAdmin,photoUrl}.
• POST /auth/register {name,email,password} when registration enabled.
• GET|POST /workspaces lists memberships / creates {name}.
• GET /workspaces/:wid returns {workspace,role,permissions,members,roles,nodes,documents,tables,documentPages,fields,projectFields,listStatusConfigs,listTagColorConfigs}.
• Workspace metadata requires membership, not task-read permission. Hierarchy metadata is visible with task, document, Table, or structure access; document page links require both task and document read access. Fields, member directories, and role catalogs retain their narrower permission rules. Task, document-body, and Table-record endpoints still require their dedicated read permissions.
• PATCH /workspaces/:wid {name}. DELETE /workspaces/:wid is Owner-only and atomically audits counts before cascading all relational workspace contents; detached attachment objects remain in the bounded storage-cleanup ledger.
• GET|POST /workspaces/:wid/nodes; POST {name,kind,parentId,icon?,color?} where kind is project|folder|list. Projects and standalone lists may have no parent; folders require a project/folder parent and nested lists may use either. Icons are nullable IDs from a bounded built-in catalog; colors are nullable normalized lowercase six-digit hex strings. The eight former color names remain accepted as request aliases. Neither field accepts markup or arbitrary CSS. No cycles or cross-workspace references.
• Node POST and PATCH accept project-only description (at most 50,000 characters, empty string clears); all node responses include it. PATCH /workspaces/:wid/nodes/:id accepts {name?,description?,parentId?,expectedParentId?,icon?,color?}. Folder/list moves stay in the same workspace, preserve contents and reject self/descendant parents or resulting depth above 32. Projects stay at root and folders cannot move to root. Cross-project moves validate each inherited descendant list against the destination project’s statuses; overridden lists retain their effective statuses and move unchanged. Moving an inheriting list to workspace root atomically materializes its current workflow; moving a standalone list into a project preserves that workflow as an override. The optional exact expectedParentId condition requires a parent mutation and rejects stale moves with 409. DELETE still requires an empty node.
• GET|POST /workspaces/:wid/items; query nodeId, search, status; POST item fields below.
• GET /workspaces/:wid/items/page accepts the same filters plus limit (default 200, maximum 500) and opaque cursor; returns {items,nextCursor}. Pages seek by immutable (createdAt,id) using migration 003’s index and bind cursors to workspace/filters. The soft encoded page budget is 2 MiB with one complete schema-validated large-item exception; encoded records over 8 MiB fail. Per-page permission checks remain mandatory. Pages are not a cross-request snapshot; refresh after concurrent changes. UI collects all pages before presenting full-dataset filters/totals; MCP exposes the next cursor explicitly.
• Bulk GET /workspaces/:wid/items and workspace export preserve their complete JSON shapes, streaming from a separate read transaction instead of allocating full-response arrays. SQLite uses its WAL snapshot and PostgreSQL uses a read-only repeatable-read transaction. Packed chunks are at most 64 KiB; each yields to the event loop and rechecks live credentials/permissions. Eight global/two per-account stream slots and a 30-second deadline bound snapshot lifetime; abort/finish/error cleanup releases the iterator, transaction, connection and slot. HEAD opens none. Writes remain usable while readers are paused.
• GET|PATCH|DELETE /workspaces/:wid/items/:id. POST /workspaces/:wid/items/bulk atomically archives or permanently deletes 1-100 revision-checked tasks. Archive requires task read/write; delete requires task delete. Parent selections must include every descendant so bulk mutations cannot leave active or dangling subtree fragments.
• GET|POST|PATCH|DELETE /workspaces/:wid/documents... provides a separate document hierarchy at workspace root or under projects/folders. Documents are leaves with versioned Markdown bodies. Document-backed pages reopen in the same editor and render beneath their containing main document in the workspace hierarchy. Within the separate Pages sidebar, top-level pages remain peers while true subpages render and collapse beneath their actual page parent. The older task-page join remains supported for existing integrations. Links and document deletion never delete tasks. Page rendering is bounded to 500 while preserving complete totals.
• GET|POST|PATCH|DELETE /workspaces/:wid/tables... provides generic Table leaves at workspace root or under same-workspace projects/folders. Columns are ordered and typed as text|number|date|datetime|checkbox|select; a Table has at most 100 columns, select columns have 1-100 unique bounded options, and each record stores at most 100 known column values in a 256 KiB JSON object. Record pages seek by (createdAt,id), return at most 500 records within the 2 MiB encoded page budget, and bind opaque cursors to workspace and Table. Table/column/record mutations are transactional and count-only audited; existing-value updates use optimistic concurrency. Column deletion requires both tables:write and tables:delete, removes that key from all records in one bounded SQL update, and rolls back cleanup if its audit fails.
• Task and document comment routes support general comments and optional revision-checked UTF-16 quote anchors. Creation/reactions require comments:create plus target read access. Body edits reattach one context match or retain the thread as orphaned; replies inherit the root anchor. Tombstones, moderation, depth-32 replies, and bounded reactions apply to both target types.
• GET /workspaces/:wid/notifications and /notifications/unread-count return only the authenticated member’s assignment and user-mention notifications. PATCH|DELETE /notifications/:id can affect only that member’s row. User mentions in task bodies and comments are validated as active same-workspace task readers; self-mentions are ignored and each document is limited to 20 recipients.
• GET|POST /workspaces/:wid/fields; POST {name,type,options?,settings?,projectId?} where type is text|number|date|datetime|checkbox|select|checklist|rating|formula. Optional projectId creates and assigns atomically; otherwise the definition is catalog-only. Select remains dropdown; checklist reuses options with selected string-array values; rating is nullable integer 1..maxRating (default 5, configurable 1-10); datetime is an ISO timestamp with offset or Z.
• PATCH|DELETE /workspaces/:wid/fields/:id. PATCH accepts name/options/settings, never type, requires structure:write and rejects settings that invalidate stored values with 409, including unassigned values. Settings are {dateFormat?,maxRating?,formula?} with type-specific validation and replacement semantics. New formula fields define one shared expression at creation and tasks render its calculated result read-only; legacy formula fields without that setting retain per-task expressions. Shared formula references are workspace-validated and block referenced-field rename/deletion until updated.
• GET|PATCH /workspaces/:wid/projects/:projectId/fields reads or updates the field owner for a root project or standalone list as {projectId,fieldIds,builtInFields,statuses,dateFormat?,updatedAt}; the route/key retain their historical names. PATCH accepts fieldIds/builtInFields/statuses/dateFormat and optional expectedUpdatedAt; null dateFormat restores the default. Mutations require structure:write and commit with an audit. Project statuses are 1-50 unique {id,name,color,completed} objects; standalone statuses use the list-status endpoint and are reflected in field-owner reads. IDs are safe strings up to 64 characters, colors six-digit hex. Removing IDs in use returns 409. Date formats are yyyy-MM-dd|MMM d, yyyy|MMMM d, yyyy|dd/MM/yyyy for built-in dates and custom date/datetime settings. Assignments remain presentation configuration, not a new authorization boundary.
• GET|PATCH /workspaces/:wid/views/list/settings reads or replaces the authenticated member’s list-view settings. Optional projectId scopes GET; PATCH accepts {projectId?,columnOrder,hiddenColumns,sort,expectedUpdatedAt}. Both operations require items:read; the user identity always comes from authentication. null/omitted projectId is all-project scope. PATCH requires expectedUpdatedAt:null to create or the exact current timestamp to update and commits a count-only audit atomically.
• GET|PATCH /workspaces/:wid/lists/:listId/statuses reads or replaces {listId,statuses?,updatedAt,inheritedProjectUpdatedAt?}. A present statuses uses the same validated status array; omission means inherit the current root-project statuses. Standalone root lists always have explicit statuses, omit the project revision and reject statuses:null. PATCH accepts optional expectedUpdatedAt and expectedProjectUpdatedAt, requires structure:write, validates existing tasks in that list, and commits the config plus list.statuses.update audit atomically. Enabling an override from inheritance requires expectedProjectUpdatedAt to match the current owning project revision. GET has the same metadata access rule as project configuration.
• GET|PATCH /workspaces/:wid/lists/:listId/tag-colors reads or replaces up to 30 exact tag-name to six-digit hex-color mappings. PATCH requires the exact expectedUpdatedAt, structure:write, workspace/list validation, and a count-only transactional audit. Colors are presentation configuration and never mutate task tags.
• POST /workspaces/:wid/members {email,roleId} adds existing user; PATCH|DELETE /workspaces/:wid/members/:userId.
• GET|POST /workspaces/:wid/roles; POST {name,permissions}; PATCH|DELETE /workspaces/:wid/roles/:id.
• GET /workspaces/:wid/export returns version 8 JSON including workspace hierarchy, documents with attribution/appearance, document-page and document-subpage links, local Tables/columns/records, safe live tableSources descriptors, task/document comments and reactions, items, root field-owner configurations, list status configurations, attachment metadata and committed images metadata. It excludes bytes, storage keys, pending image drafts and secrets. Live source rows belong to the external database and use the per-Table export endpoint. Document sections and document-comment images are empty without documents:read; Table sections are empty without tables:read; projectFields[].projectId may identify a standalone root list as well as a project.
• GET|POST /auth/tokens; POST {name} returns raw {token} once; DELETE /auth/tokens/:id.
• PATCH /auth/profile {name,email?,password?,currentPassword?}. Email/password changes require the current local password and revoke prior sessions, tokens and pending reset links before issuing a fresh browser session.
• PUT|DELETE /auth/profile/photo updates the authenticated account’s photo with an atomic safe audit. Additive migration 0011 stores one capped PNG/JPEG/WebP binary per account, separate from users and workspace attachments. Browser uploads crop/resize to at most 512px before the API’s independent 512 KiB/container validation. GET /users/:id/photo?v=:revision requires current authentication and self/shared-workspace/site-admin visibility, rechecking both access and revision before private/no-store bytes. JSON exposes safe photoUrl/authorPhotoUrl metadata only. The shared Avatar provides initial/error fallback; photo saves keep profile-field drafts mounted. Database backups include photos; workspace exports exclude global account images. See profile-photo contract.
• POST /auth/forgot-password accepts {email} and always returns the same accepted response. Configured local accounts receive a fragment-token link; only its hash is stored, one per account, for 30 minutes. POST /auth/reset-password consumes {token,password}, rejects replay/expiry, and transactionally revokes all sessions and personal tokens with an audit.
• GET /admin/users; POST /admin/users {name,email,password,isAdmin?}; PATCH /admin/users/:id {isAdmin?,disabled?}.
• GET /admin/audit?workspaceId=&limit=&offset=; GET /admin/status.

Item: {id,workspaceId,nodeId,title,description,bodyRevision,status,priority,startDate,dueDate,tags,customFields,assigneeId,checklist,parentId,archivedAt,createdAt,updatedAt}. Mutable fields exclude IDs/timestamps, bodyRevision, and archivedAt; archive state changes only through the bulk endpoint. bodyRevision advances when the description changes and binds inline comment anchors. checklist contains up to 100 {id,text,done} entries, and nullable parentId links a subtask to a task in the same workspace. Ordinary reads and task exports exclude archived tasks; archived=include|only is available to programmatic collection readers, and complete workspace backups include archives. Status must match the destination list’s effective configuration on create/update/move; that is its explicit list workflow when present, otherwise its root project’s statuses. Standalone lists always use an explicit workflow. Omitted create status selects the first effective entry. Legacy IDs backlog|todo|in_progress|review|done remain persisted, not remapped. Priority is none|low|medium|high|urgent; tags are string arrays, custom fields are field-ID-keyed typed objects, and nodeId must reference a list. Membership roles use dedicated task, document, Table, comment, structure, management, automation, credential, and agent permissions. Migration 0007 grants corresponding Table read/write/delete permissions to existing roles with task read/write/delete access while preserving all other role data. Workspace creator receives protected Owner role. Normal membership changes cannot remove the last active Owner, but site-level security suspension can disable that user while retaining ownership for recovery. Site admin access does not implicitly bypass workspace membership for task, document, or Table data.

PATCH tasks accepts an optional expectedUpdatedAt concurrency condition; a stale version returns 409 without mutation. All browser updates send it and only changed fields. Timestamps advance monotonically, including assignment/field cleanup. REST callers omitting it accept last-write-wins semantics. Updates require both read and write permission because they return the resulting task. Site suspension clears assignments and revokes sessions/tokens in the audited transaction.

All five views apply explicit progressive display bounds after the relevant filters/date bucketing, without truncating full-dataset search, totals or exports. Calendar buckets once per render and caps each day/date list; Timeline caps scheduled-month rows and its outside/unscheduled list. Date-view bounds reset on month changes but survive task refreshes. Month navigation/validated jumps preserve early ISO years without JavaScript’s numeric year-0-to-99 constructor offset.

List view reflects the selected hierarchy as actual destination-list sections, including nested and empty lists, so list-specific creation and tag appearance remain unambiguous. Each list supports row selection with atomic archive/delete actions. Multiple typed field filters use AND semantics and may reference hidden built-in or custom fields; optional field grouping remains inside each destination list. All sections share the same progressive task budget, while filtering runs before the bound. Spreadsheet cell and row selection are local UI state; task writes remain versioned API mutations. Sidebar collapse state is browser-local per workspace, while nullable node icon and normalized hex-color metadata is shared and permission-checked.

The baseline schema gives projects and standalone root lists field-configuration ownership. Field assignments follow the root project for project hierarchies and the list itself for standalone lists. Standalone workflows remain in list_status_configs; their field owner controls optional built-ins, custom fields and date formatting. All tasks shows a union with blank, non-editable cells for fields unassigned to a row’s owner. Unassignment and hierarchy moves retain stored values. Task details expose retained unassigned values separately, and formulas still use the full workspace catalog. Both export paths include all root field-owner configurations in projectFields from their own consistent data snapshot. Destructive field deletion uses indexed, single-row value/version reads and advances affected configuration revisions.

Project descriptions, date formats, status configurations and field settings are constrained in the baseline schema. The five canonical status IDs put todo first and mark only done completed. Status/settings changes never rewrite task contents. Snapshot exports decode the metadata from the same read transaction without changing chunk, deadline, authorization or cleanup behavior.

Every list has one workspace-bound list_status_configs row. Project-owned lists use statuses=NULL as the inheritance marker; standalone lists store canonical explicit defaults. The nullable database value is the project-inheritance flag, so there is no absent-row concurrency ambiguity. Rows cascade with their lists. Project status changes scan only tasks in inheriting lists and never rewrite tasks. Adding, replacing or removing an override validates only that list and never rewrites tasks. Overrides remain attached to list IDs across hierarchy moves. Workspace detail, service exports and snapshot-stream exports include every {listId,statuses?,updatedAt,inheritedProjectUpdatedAt?} row. The project revision is resolved through the list’s current ancestry from the same transaction or read snapshot, so project changes and reparenting cannot make a stale inherited-to-override transition succeed.

List-view settings reference the composite workspace membership, authenticated user and optional root project. Membership/project deletion cascades. Separate partial unique indexes cover NULL all-project scope and concrete project scope. Column keys are bounded to core list columns, optional built-ins and workspace custom-field UUIDs; project-scoped custom fields must also be assigned to that project. Title stays visible and sort references a visible ordered column.

The baseline includes one workspace-bound tag-color configuration per list and the legacy validated node icon/color catalogs. Migration 0006 adds nullable appearance shadow columns without rebuilding nodes, backfills the expanded icon value and exact former color hex, and leaves the cascade-sensitive legacy columns/checks intact. Service and snapshot reads expose only the existing icon/color JSON keys, while new writes target the shadow columns. Migration 0001 adds immutable linear|graph automation versions, optimistic-concurrency graph drafts, node-run records, credential versions and OAuth-flow state while converting every existing ordered step version to an equivalent linear graph. Existing action/steps requests, 1-20 ordered execution and {{steps.N.output}} references remain compatible, subject to the no-redirect transport hardening; the legacy PATCH route cannot replace a published graph with steps. Migration 0002_automation_linear_compatibility preserves oversized linear representations by applying the 256 KiB storage constraint only to graph versions. It repairs whole-event {{event}} HTTP-body templates to {{nodes.<triggerId>.output.event}} only in derived linear representations, leaving original step configurations, queued runs, published graphs, credential bindings and user-edited drafts intact. SQLite rebuilds the version table and restores credential bindings atomically; PostgreSQL replaces the size constraint. Rollback intentionally retains the compatibility repair.

Migration 0009 adds nullable icon/color columns to Documents and Tables without rebuilding their referenced tables. All resource kinds share the server’s appearance allowlist and color normalization; appearance changes retain resource-specific permissions, optimistic revisions where supported, safe audits and export metadata. NodeIconPicker owns the searchable catalog used by both hierarchy management and main-page title icons, keeping icon selection separate from title renaming. Site settings expose a public safe showSidebarAttribution boolean (default true), with audited site-administrator updates controlling the sidebar attribution instance-wide.

Published graphs contain exactly one root trigger and an acyclic reachable path of http, webhook, email, log, update_item, condition and switch nodes. Conditions and switches select one branch; parallel execution, loops and unreachable nodes are rejected, while exclusive paths may converge without synchronization semantics. Limits are 50 nodes, 75 edges, 256 KiB per graph, 32 KiB per node configuration, 20 switch cases and 50 visited nodes per execution. The catalog exposes typed event/node manifests; templates use stable node IDs ({{nodes.<id>.output.<path>}}) and validation permits only references guaranteed to dominate the consumer on every incoming path. All automation routes, including legacy CRUD, catalog, drafts, validation, publication, versions, previews, tests and runs, require both automations:manage and items:read; draft saves and publication recheck both inside the transaction. Publication records the publisher and produces an immutable version without changing already queued or active runs. It retains the draft and atomically advances its revision by one, so revisions remain monotonic across save/publish cycles and stale requests cannot reuse an earlier revision.

Event transactions enqueue workspace-scoped runs atomically. The worker claims four-run batches and isolates each run’s Exit while executing up to four deliveries concurrently. Each selected graph path is sequential, heartbeats its lease, stores bounded sanitized node output/logs, and retains at most 500 completed runs per workspace. All expired graph, linear and standalone-webhook leases fail without replay: an external write may have succeeded before acknowledgment failed. Completion is lease-checked; unavailable recovery storage retains the lease for fail-closed expiry. SQLite serializes worker-state I/O separately from concurrent network work to avoid same-process busy-wait deadlocks. Graph execution checks the recorded publisher’s current membership and items:read before execution, before every node, and after DNS/OAuth work before outbound HTTP. update_item operates only on the triggering task as that publisher; its lease/permission checks and task mutation share a transaction. Causation metadata caps nested automation depth at five and suppresses same-automation re-entry. Dry preview validates and returns only the selected path with effect:"none"; real test queues the current published version and can perform its configured effects, except that any graph containing update_item is rejected with 400 before enqueueing because synthetic tests have no triggering task.

Nullable items.archivedAt and an archive-aware workspace/read-order index support archiving. Archive timestamps are server-owned; bulk archive advances each affected task revision while complete workspace exports retain archived records.

The baseline includes workspace/task-scoped comments, immutable reply parents, per-member emoji reactions, validated mention records and private notification state. Comment deletion is a tombstone so descendants and deep links remain stable. Task/workspace deletion cascades discussion and notifications; membership deletion removes that member’s private notifications and reactions.

Backend verification (2026-09-07, isolated Node 24 runtime): root npm run check passed 122 API tests, 29 web unit tests, eight initializer tests, clean TypeScript/Astro diagnostics and both builds. The focused project-fields/MCP/streams set passed 29 tests in source mode and again with compiled HTTP/MCP application paths. Coverage includes legacy migration/attachment/assignment preservation, typed value validation, permissions, audit rollback, status removal/moves, HTTP settings and export parity, complete 42,652,586-byte snapshot export, live revocation, admission cleanup and the actual 30-second deadline. An initial root run timed out; a later run exposed two obsolete fixed-enum filter assertions, corrected to assert unsafe-ID rejection and safe unknown-filter empty results before the final passing run. No browser suite, deployment or external-service integration was run for this backend change; those remain separate verification boundaries.

List uses descriptor-driven rows and typed versioned inline editors. Text/number cells select before Enter/F2/double-click editing; suitable non-text cells open directly, and blur/cell movement autosaves without bypassing conflict retention. Cells are vertically centered within each row, including wrapped tag badges and expanded editors. Its sort menus render in a viewport-positioned portal so the table’s bounded horizontal scroll cannot clip them. Gallery reuses the task-card contract in an Auto-fit or explicit two-to-five-column grid, persists the layout choice per workspace in browser storage, and collapses responsively to one column. Statuses and priorities use contrast-aware badges; task tags use list-specific configured colors or a neutral fallback. Custom-field PATCHes preserve the complete baseline value map; failed/conflicting saves retain drafts and require explicit reload before retry. Shared React controls own behavior that must remain identical across surfaces: Select owns option drawers and keyboard interaction, Modal owns dialog focus/dismissal, CreateWorkspaceDialog owns workspace-creation validation, and TypedFieldInput owns custom-field type rendering and limits. Native inputs remain local when save, conflict or dismissal semantics differ; visual states come from the shared control tokens and disabled controls never receive interactive hover treatment. Appearance preferences live in Account settings and apply before React hydration on application pages. The landing page remains a native, layout-only document whose four plain-text fields come from apps/web/src/landing.json at build time; server rendering reads the public API config over the private application network so an administrator’s visibility setting applies without exposing credentials to the browser.

Account settings (/account) owns profile/photo editing, sign-in security, appearance and personal access tokens. Profile controls remain mounted independently of token and sidebar-workspace loading; profile and token mutations share busy guards so credential revocation cannot race a token reveal. Credential changes clear the one-time token display and refresh the account’s tokens. Workspace settings (/settings) owns selected-workspace metadata, members, roles, fields, export and owner deletion, gated by the existing server permissions. The account menu names these two destinations explicitly; Administration remains at /admin.

Credential routes are separate from the automation route gate: safe metadata reads require either automations:manage or credentials:manage, without an additional items:read requirement. Credential mutations and OAuth operations require credentials:manage.

Table transfer routes import CSV, row-object JSON, or typed hopya-table version 1 JSON into a new/existing local Table. Imports require read/write, validate 500 rows/1 MB and commit all rows, created columns and audits together. Per-Table CSV/JSON exports read the entire local or remote snapshot, independently of grid pagination, with a 30-second deadline, 64 KiB output chunks, eight export slots/two per account and authentication/permission checks before chunks. The snapshot is pinned before response headers. Failure after headers destroys the stream. Browser downloads wait for completion before offering the file.

Table footer calculations use GET /workspaces/:wid/tables/:id/summary, gated by tables:read. The shared Table snapshot reader scans a separate consistent local/source snapshot in bounded chunks, accumulating all-record counts, numeric statistics, precise date bounds and checkbox counts in constant memory. Queries and calculations share four global/two per-account slots and a 30-second checkpoint deadline; current credentials, membership and resource/link access are rechecked between chunks and before return, with snapshot/slot release on every exit. No partial totals are returned on failure. Empty text/null/missing cells are empty; zero/false remain present. Numeric overflow is explicit. Footer selections are per-open-Table UI state; results are loaded on demand and invalidated by saves, record/column changes, imports and Refresh. Headers, cells and the sticky ghost-control footer share one fixed-layout column grid; keyboard cell editing remains available without a top value bar.

Header ordering and the List-style Match all Filters panel use read-only POST /workspaces/:wid/tables/:id/query. One typed sort and up to 20 validated AND filters apply to all local/live records before the display bound; total and optional summary cover every match. A bounded sorted candidate page (at most 500 records/2 MiB) avoids retaining the full dataset. Query cursors bind workspace/Table, schema, filters and ordering; a bounded ID/hash token resolves the anchor in the same snapshot instead of embedding large source values. Changed/missing anchors fail with 409, and current access is always required. Natural snapshot order breaks ties and empty values stay last. Pagination is not a cross-request snapshot. The UI reloads ordered/filtered pages after writes, preserves cell/column drafts by disabling view changes while editing, and uses filtered footer totals. Compact controls expose icon-only Refresh and an accessible options menu for Add column, transfer, clearing order and source details. Per-Table exports remain complete and independent of view filters/order.

The Table toolbar and grid share one bordered frame, with view controls on the left, record/utility actions on the right and record counts below. Column titles and type badges form a single compact group; each header opens a shared TableMenu for typed ordering and permitted local rename/delete actions, with column letter/type metadata inside the menu. These menus render in a viewport-bounded portal outside the horizontal scroller, own arrow/Home/End/initial-letter navigation and Escape/Tab dismissal, and restore focus to the trigger after selection or cancellation. Mobile triggers and menu items retain 44px targets.

The toolbar uses the theme’s subtly contrasting wash surface; column headings have 14px semibold text and 44px-height padded controls. Numeric header menus offer Number display with automatic or 0–10 decimal places and dot/comma/space separators. These account/workspace/Table/column-scoped browser preferences format cells and numeric calculations only. String-based decimal formatting preserves exact live-source bigint/decimal values; storage, editing, copying, filtering, sorting and exports use the underlying values.

Migration 0008 adds workspace-scoped table_connections and table_links; existing Tables remain local. Credential managers can test/save encrypted connections and browse source metadata. Linking also requires Table read/write and grants access through the workspace’s Table permissions. The server permits only operator-listed host:port destinations or existing SQLite files inside a canonical operator root, excluding Hopya’s own database. AES-256-GCM binds encrypted configuration to workspace/connection under a persistent SQL-specific or application key. Source columns are introspected and fingerprinted; primary keys, generated fields and unsupported scalar types are read-only. Exact decimal/bigint values remain text. Composite primary-key cursors are position hints bound to link/workspace/schema, never credentials. Four concurrent source operations, connection/query timeouts, at most 100 columns and byte-bounded record pages constrain work.

Live cell PATCH locks and reads the source row, compares its value-hash revision, applies only changed writable fields and reads back trigger/default results before commit. Permissions and the current link are checked again before mutation and return. Cross-database commit cannot be atomic: a durable count-only Hopya audit intent is committed before contacting the source, followed by an applied event after source commit. An unresolved intent represents an uncertain/failed operation; completion-audit failure returns an explicit reload-required error, never success or an automatic retry. Removing a Hopya Table or workspace removes its links without dropping source tables. Source schema/row creation/deletion remain source-database operations. No arbitrary SQL console, background synchronization, or browser-held credentials are involved.

Separate modules under apps/api/app/integrations use core service operations, never duplicate permission logic. Integration route registration accepts Adonis router and uses shared authenticate(ctx), requirePermission(userId,wid,permission), and service exports from app/core.ts (final exact signatures documented in that file).

• Attachments use GET|POST /workspaces/:wid/items/:id/attachments and GET|DELETE /workspaces/:wid/items/:id/attachments/:attachmentId. Private download, bounded uploads, opaque storage keys, forced attachment disposition.
• Task-body and task/document-comment image drafts use POST /workspaces/:wid/images, authenticated private preview/download, and uploader-only pending-draft deletion. Migration 0010 adds resource-scoped metadata linked to the existing storage ledger. A content mutation claims validated references atomically; task-body images become ordinary task attachments. Pending uploads expire after 24 hours, comment tombstones revoke their images, and cascades/compensation feed bounded garbage collection. Inline routes validate raster containers/dimensions from bytes and recheck access after storage waits; unsupported/remote image URLs remain inert. The editor preserves file retries in tab memory and text/image references in account/target-scoped session drafts. Gallery uses rendered body-image order with separate task-open and carousel controls. See image behavior and limits.
• OIDC uses /auth/sso and /auth/sso/callback, PKCE, state, nonce, issuer/sub binding. No unsafe automatic linking to a local email account.
• Hopya accepts standards-compliant OIDC providers. Keep provider administration private. OIDC is optional for local-password deployments and does not replace Hopya workspace RBAC.
• GET|POST /admin/oidc-identities lists or explicitly binds issuer/subject identities; optional GET userId filter scopes results. DELETE /admin/oidc-identities/:id safely unlinks and revokes all target sessions/tokens atomically with an audit entry. Passwordless users must retain another identity for the configured issuer/client; inactive-issuer rows do not count. Discovery must match the configured issuer exactly, including trailing slash. Matching in-flight exchanges cannot undo an unlink. Auto-provisioning is separate and off by default.
• Agent uses POST /workspaces/:wid/agent {message} to read authorized workspace context and return {reply,proposal?}. Proposals require a separate explicit confirmed mutation through ordinary task routes.
• Agent context queries select only lightweight metadata for 100 recent tasks and 100 lists in SQL; they do not load the workspace then slice it. MCP reads bounded cursor pages and cancels responses exceeding 4 MiB of incoming bytes instead of buffering the complete body first.
• MCP tool schemas are shared by two official-SDK transports. The separate stdio process forwards through authenticated REST. The opt-in SSE routes run inside Adonis, require personal bearer tokens, bind each bounded 30-minute session to one user, reauthenticate message POSTs, and call the same workspace-scoped service boundary as REST. Disabling SSE closes active sessions. Site administration never bypasses workspace permissions.
• Automation credentials are workspace-scoped write-only bearer|api_key|basic|custom_headers|oauth2 profiles. Only safe metadata is returned. Secret versions and temporary PKCE verifiers use AES-256-GCM with workspace, credential, version and type as authenticated context under the dedicated AUTOMATION_KEYRING; a missing/invalid keyring fails credential operations explicitly with 503 without disabling task management. OAuth uses authorization code, one-use hashed state and PKCE S256, but compatibility with external providers has not been validated.
• Credential use requires an exact destination origin and optional exact-or-descendant path prefix. Destination checks resolve DNS, reject embedded credentials/fragments and block IPv4 unspecified, loopback, link-local and multicast/reserved ranges plus IPv6 unspecified, loopback, link-local, multicast and mapped loopback/link-local forms. RFC1918 and IPv6 ULA destinations are allowed; public credential-bearing destinations require HTTPS. Redirects are rejected. AUTOMATION_NETWORK_EXCEPTIONS is an operator-only comma-separated list of exact URL origins that bypasses address/HTTPS blocks, not origin/path credential binding.

Stable Effect 4 uses native tryPromise, result, callback, catch/catchCause, Semaphore, and timeoutOrElse({ duration, orElse }) APIs. Reusable integration generators use fnUntraced; graph actions compose HTTP, credential and SMTP Effects directly. DNS owns cancellable resolvers with a five-second deadline; OAuth has four permits and a twenty-second total deadline. SMTP has two permits and a fifteen-second deadline, with scoped cleanup of actual connection/TLS sockets as well as the transport. Durable SQL outcomes and rotated-token persistence are awaited before releasing their ownership; non-idempotent operations are not blindly retried. Agent requests have time/output/concurrency bounds; no agent operation mutates data. Storage metadata uses a durable object ledger for compensation and hourly bounded cleanup. See Effect workflow review and integration details.

Effect boundary rule: native v4 runPromise and runSync squash a flat Cause to the original failure or defect. Hopya’s shared runSyncThrow/runPromiseThrow helpers in app/database.ts explicitly inspect Exit, preserve HttpError identity and normalize interruption-only causes to AbortError; the Promise bridge accepts an abort signal. Effect.result yields Success.success or Failure.failure for expected failures and does not recover defects. Throwing gen/sync code or rejected promise calls become defects: use try/tryPromise/fail for expected fallible operations so typed recovery and sanitization apply. OIDC’s underlying SDK Promise still uses openid-client’s request timeout; outer Effect interruption does not itself abort that SDK operation. Zod schemas must avoid version-specific type parameters (ops/tsconfig.json resolves zod to the web’s v4): use inferred types or z.ZodType<unknown>.

Assistant browser requests are aborted on close/workspace unmount. The API composes detected client disconnection with a 45-second provider deadline, removes raw HTTP listeners and releases its four-request admission slot on every exit. Ordinary request-body completion is not cancellation. Live credentials and both permissions are rechecked before returning/auditing an answer. Cancellation only bounds Hopya’s HTTP work, not an external provider’s retained context, processing or billing.

One public origin. Reverse proxy routes /api/* and /health to Adonis and everything else to Astro. Browser content never receives provider or automation secrets. Sessions and bearer tokens are random, hashed at rest, bounded lifetime and revocable. Passwords use scrypt. DB mutations and audit events are transactional. Admin audits omit passwords/tokens, automation credential values and AI prompt contents. Storage keys are server-generated, paths never taken from client filenames. Operator-configured AI base URLs permit private local LLMs; graph destinations are manager-configured but remain subject to the automation network and credential-binding policy.

Workspace webhook signing is versioned. Existing migrated version-1 rows retain the historical sha256(secret + "." + body) construction. New or rotated version-2 rows send x-hopya-signature: sha256=<hex HMAC-SHA256(secret, exact body)>; rotation returns the new secret once and upgrades the row to version 2. The shared outbound transport rejects all 3xx responses, including legacy linear requests and both webhook signing versions. This intentional security hardening breaks redirect-dependent integrations to prevent forwarding payloads or secrets beyond the checked destination; configure final endpoint URLs directly rather than relying on legacy redirects.

Direct API mode trusts no forwarded IPs. Compose uses one explicitly trusted private proxy hop that overwrites forwarding headers, and password throttles partition account/IP. A second TLS proxy requires exact-address real-IP configuration; do not publish the private API or broadly trust forwarded chains. Database contents and filesystem objects are not application-encrypted storage; host and database administrators remain trusted.