A framework to build simple business apps (filemaker/access complexity) entirely out of PostgreSQL (PG) that auto generates the web (desktop? mobile?) UI, with minimal configuration.

🚪 Auth

User authentication (e.g. log-ins) and Authorization are done done through PG's ROLEs and GRANTs.

C.R.U.D.

Creating

The form for creation of a single entry is based on the names and types of the fields in the table's schema. Each PG type has its own "write-mode" implementation. Required form fields (i.e., NOT NULL fields) are marked with an asterisk. Similarly, validation is driven by CHECKs and underlying data types.

Here are some examples for "write-mode" widgets:

Inserting into multiple tables at once

TODO: views + PG RULEs?

👓 Reading

Listing and visualizing tables is similar to pgweb. A convention (i.e., starting names with _) is adopted for hiding tables/views. The visibility of objects is also dictated by PG's access permissions. Even finer-grained access to data is achieved through PG's Row Security Policies.

Each PG type has its "read-mode" visual representation. Implementing one for custom types is easy.

🔎 Searching

Trivial cases are inferred by the indexes that exist on a table:

More complex cases are handled by writing a PG function from which the search form is generated. The form fields are inferred from the types and names of the function's input arguments. Performance optimization (e.g., indexing) is left to the developer.

✏️ Updating

Similar to Creating

Deleting

From the list, view, if you're authorized to do so. Likewise in singular view form.

TODO: how to deal with cascades?

↝ Foreign Keys

Fields that can act as foreing-keys also need to specify a corresponding "description" field. This requires an entry in postgres-web's config.

The "description" is used instead of the actual value when displaying it in a table/view.

This would also allow for a lazy?-fuzzy?-searchable-dropdown (lfsd) (a la Bootstrap Select) to be generated for searching, creating and updating.

🗄️ Blob storage

Integration with blob storages (for e.g. photos, documents) is easily configured and completely transparent to user.

Example usage:

select ("key"
       , mime_type
       , content_length
       , content :: bitea)
from bucket

TODO: encription?

TODO: streaming?!?

Cool storages to look into:

❓ Open questions

🎨 Prior art