Generic ECM system

Goals

• Upload any file and add custom Metadata to it
• Search Functionality over the Metadata
• Api for upload/download creating schemas/updating schemas
• metadata tables created at runtime

uncertenties

• handle colum changes
  • adding is easy, deletion is bad, what about migrating types?

Design ideas

How are SQL Tables defined:

CREATE TYPE contact_method AS ENUM ('email', 'sms', 'other');

CREATE TABLE contact_history (
    uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(uuid),
    contact_date TIMESTAMPTZ NOT NULL,
    method contact_method NOT NULL,
    additional_context TEXT
);

CREATE INDEX idx_contact_history_customer_id ON contact_history (customer_id);
);

Now to manage an Asortment of these we need some generic definitions in our own tables

--table metadata
CREATE TABLE metadata_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);
CREATE INDEX idx_metadata_table_name ON metadata_table (name);

CREATE TYPE column_type AS ENUM ('UUID','BOOLEAN','BIGINT','DATE', 'TIMESTAMPZ', 'INTEGER','DOUBLE PRECISION','VARCHAR','TEXT');

CREATE TABLE metadata_row (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metadata_table_id UUID NOT NULL REFERENCES metadata_table(id),
  name TEXT NOT NULL,
  type column_type NOT NULL,
  length INTEGER DEFAULT 0,
  required boolean DEFAULT false,
  active boolean DEFAULT true
);

--content
CREATE TABLE content (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  custom_id TEXT,
  created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated TIMESTAMPTZ DEFAULT NOW(),
  active BOOLEAN NOT NULL DEFAULT true,
  metadata_table_id UUID NOT NULL REFERENCES metadata_table(id),
  UNIQUE (metadata_table_id, custom_id)
);

CREATE TABLE version (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content_id UUID NOT NULL REFERENCES content(id),
  created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  filename TEXT NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  UNIQUE (content_id, version)
);

Api definition

• Create Schema:

  • name
  • [{ name, type, length, required }]
  • update/replace

• Delete Schema:

  • name

• Upload Content:

  • filename
  • custom_id
  • content
  • [{ name, value }]

• Delete Content:

  • custom_id / id

• Seach Content:

  • schema
  • [{ name, value }]