Sheeted

• 📎 Overview
• 📎 Getting Started
• 📎 Usage
  • Entity type
  • Schema
  • View
  • AccessPolicies
  • Hook
  • Validator
  • Actions
  • Sheet
  • Creating app
  • More information
• 📎 TIPS
  • Can I add sheet sources easily?
  • Can I use a raw mongoose model of the sheet?
• 📎 Generated REST API
  • Common request headers
  • List all sheets
  • Get a sheet
  • List entities
  • Get an entity
  • Create an entity
  • Update an entity
  • Delete entities
• 📎 Development

📎 Overview

Sheeted is a table UI web application framework.

It aims to make it extremely easy to develop table-based web applications, which are often used for organizations internal use or for some management use. With Sheeted, you will be released from boring coding not concerned with business rules. You can develop practical Table UI web applications 10x faster with Sheeted.

Features:

• Auto generated REST API and UI
• Flexibility to define business rules such as data structure, validations, and access policies
• Authentication with SAML

📎 Getting Started

Sheeted provides CLI to create Sheeted app project. Run the command below:

$ npx @sheeted/cli project <your_project_name>

The command creates a directory named <your_project_name> and files all you need such as package.json. Then you can start to develop in the project.

$ cd <your_project_name>
$ cat README.md # You will find how to setup the project.

📎 Usage

A Sheeted web application consists of Sheets, which represent tables. A Sheet conststs of one type and some objects as below.

• Entity type
• Schema
• View
• AccessPolicies
• Hook
• Validator
• Actions

Let's take a look one by one.

Entity type

Entity type is the data format of a row in Sheet. It's an interface in TypeScript. Every Entity must have "id" for unique identity. To ensure this, Entity type extends EntityBase.

Example:

import { EntityBase, IAMUserEntity } from '@sheeted/core'

import { Genre, Format } from '../../constants'

export interface BookEntity extends EntityBase {
  title: string
  like: number
  price: number
  genre: Genre
  formats: Format[]
  url?: string
  buyer: IAMUserEntity
  buyDate: number
  readFinishedAt: number
  readMinutes: number
  publicationYear: number
  comment?: string
}

Schema

Schema can define some properties of each field in Entitiy. It has the same fields as Entity's.

Example:

import { Types, IAM_USER_SHEET, Schema } from '@sheeted/core'

import { Genres, Formats } from '../../constants'

import { BookEntity } from './book.entity'

export const BookSchema: Schema<BookEntity> = {
  title: {
    type: Types.Text,
    unique: true,
  },
  like: {
    type: Types.Numeric,
    readonly: true,
  },
  price: {
    type: Types.Numeric,
  },
  genre: {
    type: Types.Enum,
    enumProperties: {
      values: Genres,
    },
  },
  formats: {
    type: Types.EnumList,
    enumProperties: {
      values: Formats,
    },
  },
  url: {
    type: Types.Text,
    optional: true,
  },
  buyer: {
    type: Types.Entity,
    readonly: true,
    entityProperties: {
      sheetName: IAM_USER_SHEET,
    },
  },
  buyDate: {
    type: Types.CalendarDate,
  },
  readFinishedAt: {
    type: Types.CalendarDatetime,
    optional: true,
  },
  readMinutes: {
    type: Types.Time,
  },
  publicationYear: {
    type: Types.CalendarYear,
  },
  comment: {
    type: Types.LongText,
    optional: true,
  },
}

View

View is about UI such as a column title.

Example:

import { View } from '@sheeted/core'
import { CALENDAR_DATETIME_FORMAT } from '@sheeted/core/build/interceptors'

import { BookEntity } from './book.entity'

export const BookView: View<BookEntity> = {
  title: 'Books',
  icon: 'menu_book',
  display: (entity) => entity.title,
  enableDetail: true,
  defaultSort: {
    field: 'title',
    order: 'asc',
  },
  columns: [
    { field: 'title', title: 'TITLE', style: { minWidth: '10em' } },
    { field: 'like', title: 'LIKE' },
    {
      field: 'price',
      title: 'PRICE',
      numericOptions: {
        formatWithIntl: {
          locales: 'ja-JP',
          options: { style: 'currency', currency: 'JPY' },
        },
      },
    },
    {
      field: 'genre',
      title: 'GENRE',
      enumLabels: { comic: 'COMIC', novel: 'NOVEL' },
    },
    {
      field: 'formats',
      title: 'FORMATS',
      enumLabels: { paper: 'PAPER', kindle: 'KINDLE' },
    },
    { field: 'url', title: 'URL', textOptions: { isLink: true } },
    { field: 'buyer', title: 'BUYER' },
    { field: 'buyDate', title: 'BUY DATE' },
    { field: 'readFinishedAt', title: 'FINISHED READING' },
    { field: 'readMinutes', title: 'READ TIME' },
    { field: 'publicationYear', title: 'YEAR OF PUBLICATION' },
    { field: 'comment', title: 'COMMENT', style: { minWidth: '15em' } },
    {
      field: 'updatedAt',
      title: 'LAST UPDATED',
      numericOptions: { formatAsDate: CALENDAR_DATETIME_FORMAT },
    },
  ],
}

AccessPolicies

AccessPolicies is a set of access policies based on roles. It's an array of AccessPolicy.

import { AccessPolicy, Context } from '@sheeted/core'

import { Roles, Role, ActionIds } from '../../constants'

import { BookEntity } from './book.entity'

export const BookAccessPolicies: AccessPolicy<BookEntity, Role>[] = [
  {
    action: 'read',
    role: Roles.DEFAULT_ROLE,
  },
  {
    action: 'create',
    role: Roles.DEFAULT_ROLE,
  },
  {
    action: 'update',
    role: Roles.DEFAULT_ROLE,
    column: {
      effect: 'deny',
      columns: ['genre'],
    },
    condition: (book: BookEntity, ctx?: Context<Role>): boolean => {
      return book.buyer && ctx?.user.id === book.buyer.id
    },
  },
  {
    action: 'update',
    role: Roles.EDITOR_ROLE,
  },
  {
    action: 'delete',
    role: Roles.DEFAULT_ROLE,
    condition: (book: BookEntity, ctx?: Context<Role>): boolean => {
      return book.buyer && ctx?.user.id === book.buyer.id
    },
  },
  {
    action: 'delete',
    role: Roles.EDITOR_ROLE,
  },
  {
    action: 'custom',
    role: Roles.DEFAULT_ROLE,
    customActionId: ActionIds.LIKE,
  },
]

Hook

Hook is a set of functions which will be executed after creating / updating / destroying entities.

Example:

import { Hook } from '@sheeted/core'
import { IAMUserRepository } from '@sheeted/mongoose'

import { BookEntity } from './book.entity'
import { BookRepository } from './book.repository'

export const BookHook: Hook<BookEntity> = {
  async onCreate(book, ctx, options) {
    const user = await IAMUserRepository.findById(ctx.user.id)
    if (!user) {
      throw new Error(`user not found for id "${ctx.user.id}"`)
    }
    await BookRepository.update(
      book.id,
      {
        buyer: user,
      },
      {
        transaction: options.transaction,
      },
    )
  },
}

Validator

Validator defines validations on creating / updating entities.

Example:

import { Validator, ValidationResult } from '@sheeted/core'

import { BookEntity } from './book.entity'

export const BookValidator: Validator<BookEntity> = (_ctx) => (
  input: Partial<BookEntity>,
  _current: BookEntity | null,
): ValidationResult<BookEntity> => {
  const result = new ValidationResult<BookEntity>()
  if (input.price) {
    if (!Number.isInteger(input.price)) {
      result.appendError({
        field: 'price',
        message: 'Must be integer',
      })
    }
    if (input.price < 0) {
      result.appendError({
        field: 'price',
        message: 'Must be greater than or equal to 0',
      })
    }
  }
  return result
}

Actions

Actions represents custom operations to entities. It's an array of Action.

Example:

import { Action } from '@sheeted/core'

import { ActionIds } from '../../constants'

import { BookEntity } from './book.entity'
import { BookRepository } from './book.repository'

export const BookActions: Action<BookEntity>[] = [
  {
    id: ActionIds.LIKE,
    title: 'Increment like count',
    icon: 'exposure_plus_1',
    perform: async (entity: BookEntity): Promise<void> => {
      await BookRepository.update(entity.id, {
        like: entity.like + 1,
      })
    },
  },
]

Sheet

Now we can define Sheet. It's the main object bundling above objects.

Example:

import { Sheet } from '@sheeted/core'

import { Role, SheetNames } from '../../constants'

import { BookEntity } from './book.entity'
import { BookSchema } from './book.schema'
import { BookValidator } from './book.validator'
import { BookView } from './book.view'
import { BookAccessPolicies } from './book.access-policies'
import { BookActions } from './book.actions'
import { BookHook } from './book.hook'

export const BookSheet: Sheet<BookEntity, Role> = {
  name: SheetNames.BOOK,
  Schema: BookSchema,
  Validator: BookValidator,
  View: BookView,
  AccessPolicies: BookAccessPolicies,
  Actions: BookActions,
  Hook: BookHook,
}

Creating app

After defining sheets, you can create application server with createApp(). This function just returns express app.

Function createApp() needs arguments as below.

• AppTitle: title of application.
• Sheets: sheets array.
• Roles: role objects array.
• DatabaseDriver: database driver. Currently only supported driver is mongo driver.
• ApiUsers: array of an api user which has userId and accessToken. This is used for API access.

Example:

import { createApp } from '@sheeted/server'
import { MongoDriver } from '@sheeted/mongoose'

import { config } from '../util/config.util'
import { defaultUsers } from '../util/seeder.util'

import { RoleLabels } from './constants'
import { BookSheet } from './sheets/book/book.sheet'

const admin = defaultUsers[1]

export const app = createApp(
  {
    AppTitle: 'Book Management App',
    Sheets: [BookSheet],
    Roles: RoleLabels,
    DatabaseDriver: MongoDriver,
    ApiUsers: [
      {
        userId: admin.id,
        accessToken: 'f572d396fae9206628714fb2ce00f72e94f2258f',
      },
    ],
    options: {
      iamUserView: {
        title: 'User Management',
      },
    },
  },
  {
    ...config,
  },
)

More information

For more information about usage, please visit:

• examples/
• API Documentation

📎 TIPS

Can I add sheet sources easily?

You can create sheet source files via CLI.

$ npx @sheeted/cli sheet dir/to/sheet-name

Can I use a raw mongoose model of the sheet?

@sheeted/mongoose provides compileModel() function to access mongoose Models, or you can use the model from *.model.ts if you create a sheet via CLI.

📎 Generated REST API

You can use the generated REST API. The format of a response is JSON.

Common request headers

You need authorization header in every request which is defined in Application.ApiUsers.

Authorization: token <access token>

List all sheets

GET /api/sheets

Get a sheet

GET /api/sheets/:sheetName

List entities

GET /api/sheets/:sheetName/entities

Parameters

Name	Type	Description
page	number	a page number of list
limit	number	limit count of entities
search	string	search string
sort	array of object	sort objects

Get an entity

GET /api/sheets/:sheetName/entities/:entityId

Create an entity

POST /api/sheets/:sheetName/entities

Set JSON of an entity in the request body.

Update an entity

POST /api/sheets/:sheetName/entities/:entityId

Set JSON of changes in the request body.

Delete entities

POST /api/sheets/:sheetName/entities/delete

Set JSON of entity ids to be deleted as below.

{
  "ids": ["entityId1", "entityId2"]
}

📎 Development

Requirements:

• Node.js >= 14
• docker-compose
• direnv

Install. This project uses yarn workspaces.

$ yarn install

Run docker containers.

$ docker-compose up -d

Run UI development server.

$ yarn w/ui start

Run an example app server.

$ node examples/build/account-management

Then, access to http://localhost:3000 on your browser and log in with demo/demo.