Developer guide

Introduction

TILSNA presents researchers, companies, research, products, innovations and funding across five focus industries, with dashboards, smart search, detail views, graph analysis and supply-chain visualization. This guide is the engineering reference for the front-end web app, the REST API it consumes, and the PostgreSQL knowledge database behind it.

The platform is delivered against the project TOR and the central-database / API specification. Mandated non-functional standards: microservice architecture, On-Cloud deployment, a relational database (PostgreSQL / MySQL / MSSQL / Oracle), API security with OAuth 2.0 + JWT, WSS/WAS web-security standards, PDPA compliance, and ISO/IEC 29110 project management.

Architecture

Three tiers, loosely coupled so each can scale and deploy independently. The web app never talks to the database directly — it only calls the versioned REST API, which enforces auth and shapes responses for the front-end.

• Web (front-end) — Astro + React islands, served at the edge on Cloudflare. Renders landing, dashboards, explore, detail, graph and auth UI.
• API (services) — REST /api/v1 with a standard JSON envelope. Microservices: catalog/search, graph & supply-chain, auth (Thai ID), import, and (phase 2) admin/CMS — all behind an OAuth 2.0 / JWT gateway.
• Database — PostgreSQL "Core Knowledge Database": master entities + a relationship layer + data-governance & staging tables.
• External — Thai ID (DOPA) for identity, DBD API for company auto-fill, TCELS data sources, and the Excel data-prep import path.

Technology stack

Getting started (web app)

# Node 22.12 (see .nvmrc)
nvm use            # or: install Node 22.12
npm install        # install dependencies

npm run dev        # local dev server → http://localhost:4330
npm run build      # production build (Cloudflare adapter)
npm run preview    # preview the production build

Configure the API base URL via an environment variable (e.g. PUBLIC_API_BASE_URL=https://api.tilsna…/api/v1). Deployment is On-Cloud; this front-end ships to Cloudflare via a build/deploy hook.

Project structure

src/
  pages/        # routes (.astro) → /, /explore, /database, /news, /developer …
  components/   # React islands (home, explore, database, news, site, auth, …)
  layouts/      # Layout.astro (theme tokens, fonts, head)
  lib/          # site.tsx — language + theme context
  i18n/         # content.ts — shared bilingual copy
  data/         # mock JSON (mirrors the API: explore.json, overview.json, news.ts)
public/
  developer/    # architecture / data-model / supply-chain / import diagrams + prompts
  resources/    # brand assets, videos, network diagram

Today the front-end reads mock JSON in src/data that mirrors the API contract, so swapping to the live API is a per-endpoint change with no UI rewrite.

Database schema

The database is an entity + relationship model, not a flattened spreadsheet. Twelve core tables in three groups: master data (the entities shown in the UI), a relationship layer (one row per typed edge, powering graph & supply-chain) and data governance (provenance + import staging).

-- Core knowledge data — 12 tables (logical schema; portable to MySQL / MSSQL / Oracle)

CREATE TABLE industry_categories (
  industry_category_id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,        -- PHARMA_CELL, GENOMICS_PRECISION, …
  name_th VARCHAR(255), name_en VARCHAR(255) NOT NULL,
  description TEXT, is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE organizations (
  organization_id SERIAL PRIMARY KEY,
  name_th VARCHAR(255), name_en VARCHAR(255),
  organization_type VARCHAR(100), country VARCHAR(100),
  website_url TEXT, description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE researchers (
  researcher_id SERIAL PRIMARY KEY,
  full_name_th VARCHAR(255), full_name_en VARCHAR(255),
  academic_position VARCHAR(255),
  organization_id INT REFERENCES organizations(organization_id),
  orcid VARCHAR(100), profile_summary TEXT, research_interest TEXT,
  verification_status VARCHAR(50),         -- verified / pending / needs_update
  last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE expertise_tags (
  expertise_tag_id SERIAL PRIMARY KEY,
  tag_name_th VARCHAR(255), tag_name_en VARCHAR(255),
  industry_category_id INT REFERENCES industry_categories(industry_category_id)
);

CREATE TABLE companies (
  company_id SERIAL PRIMARY KEY,
  company_name_th VARCHAR(255), company_name_en VARCHAR(255),
  registration_no VARCHAR(100), parent_company VARCHAR(255),
  country_origin VARCHAR(100), thailand_status VARCHAR(255),
  business_model VARCHAR(255), thailand_footprint TEXT,
  has_thai_legal_entity BOOLEAN, regulator_status VARCHAR(255),
  main_regulator VARCHAR(255), customer_group TEXT, main_market_channel TEXT,
  has_thailand_manufacturing BOOLEAN, has_boi BOOLEAN, website_url TEXT,
  latest_data_year INT, verification_status VARCHAR(50),
  evidence_strength VARCHAR(50), priority_score INT, note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE research_projects (
  research_project_id SERIAL PRIMARY KEY,
  title_th TEXT, title_en TEXT, abstract TEXT, research_type VARCHAR(100),
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  technology_readiness_level VARCHAR(50), start_year INT, end_year INT,
  lead_organization_id INT REFERENCES organizations(organization_id),
  status VARCHAR(100), source_id INT, last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name_th VARCHAR(255), product_name_en VARCHAR(255),
  product_type VARCHAR(100), description TEXT,
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  regulatory_status VARCHAR(255), fda_license_no VARCHAR(100),
  market_status VARCHAR(100), target_customer_group TEXT, website_url TEXT,
  last_verified_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE innovations (
  innovation_id SERIAL PRIMARY KEY,
  innovation_name_th VARCHAR(255), innovation_name_en VARCHAR(255),
  innovation_type VARCHAR(100), description TEXT,
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  technology_readiness_level VARCHAR(50),
  intellectual_property_status VARCHAR(100),
  commercialization_status VARCHAR(100), last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE funding_sources (
  funding_source_id SERIAL PRIMARY KEY,
  organization_id INT REFERENCES organizations(organization_id),
  funding_source_name VARCHAR(255), country VARCHAR(100),
  funding_type VARCHAR(100), website_url TEXT, description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE funding_programs (
  funding_program_id SERIAL PRIMARY KEY,
  funding_source_id INT REFERENCES funding_sources(funding_source_id),
  program_name VARCHAR(255),
  primary_category_id INT REFERENCES industry_categories(industry_category_id),
  segment TEXT, thailand_relevance TEXT, thailand_footprint TEXT,
  stage VARCHAR(255), instrument VARCHAR(255), typical_ticket_size TEXT,
  target_beneficiary TEXT, application_period TEXT, website_url TEXT,
  evidence_strength VARCHAR(50), priority_score INT,
  verification_status VARCHAR(50), note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE evidence_sources (
  evidence_source_id SERIAL PRIMARY KEY,
  entity_type VARCHAR(100) NOT NULL, entity_id INT NOT NULL,
  source_title TEXT, source_url TEXT, source_type VARCHAR(100),
  evidence_strength VARCHAR(50), retrieved_date DATE, note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE entity_relationships (        -- powers Graph + Supply-chain
  relationship_id SERIAL PRIMARY KEY,
  source_entity_type VARCHAR(100) NOT NULL, source_entity_id INT NOT NULL,
  target_entity_type VARCHAR(100) NOT NULL, target_entity_id INT NOT NULL,
  relationship_type VARCHAR(100) NOT NULL, relationship_strength VARCHAR(50),
  evidence_source_id INT REFERENCES evidence_sources(evidence_source_id),
  note TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Master tables: industry_categories · organizations · researchers · expertise_tags · companies · research_projects · products · innovations · funding_sources · funding_programs. Governance: evidence_sources, staging stg_* + import_batches.

Accounts, access & operations

The TOR requires authentication, role requests with staff/admin approval, a landing CMS, analytics and an audit trail — so the schema adds a platform layer of 11 tables alongside the knowledge data. See the data dictionary for every column.

-- Accounts, access & operations (TOR 3.5–3.10)

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),          -- null for Thai-ID-only accounts
  thai_id_sub VARCHAR(100) UNIQUE,     -- subject id from Thai ID (DOPA)
  full_name VARCHAR(255),
  phone VARCHAR(50),
  line_id VARCHAR(100),
  status VARCHAR(50) DEFAULT 'active', -- active / suspended / pending
  email_verified BOOLEAN DEFAULT FALSE,
  last_login_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
  role_id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,    -- guest / researcher / company_rep / tcels_staff / admin
  name_th VARCHAR(100), name_en VARCHAR(100), description TEXT
);

CREATE TABLE user_roles (
  user_role_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  role_id INT REFERENCES roles(role_id),
  granted_by INT REFERENCES users(user_id),
  granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, role_id)
);

CREATE TABLE role_requests (
  request_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  requested_role VARCHAR(50) NOT NULL,    -- researcher / company_rep
  status VARCHAR(50) DEFAULT 'pending',   -- pending / approved / rejected
  academic_position VARCHAR(255), orcid VARCHAR(100),
  organization_name VARCHAR(255), research_interest TEXT,   -- researcher fields
  company_name VARCHAR(255), registration_no VARCHAR(100),
  company_objective TEXT, registered_capital VARCHAR(100), directors TEXT, -- company fields
  reviewer_id INT REFERENCES users(user_id),
  review_note TEXT, decided_at TIMESTAMP,
  linked_entity_type VARCHAR(50), linked_entity_id INT,    -- set on approval
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE company_members (             -- company representatives (3.8.2)
  company_member_id SERIAL PRIMARY KEY,
  company_id INT REFERENCES companies(company_id),
  user_id INT REFERENCES users(user_id),
  member_role VARCHAR(50) DEFAULT 'representative',  -- owner / representative
  added_by INT REFERENCES users(user_id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (company_id, user_id)
);

CREATE TABLE password_reset_tokens (       -- email password reset (3.5.3)
  token_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  token_hash VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP NOT NULL, used_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE content_articles (            -- landing CMS: news & funding (3.9.3 / 3.10.2)
  article_id SERIAL PRIMARY KEY,
  type VARCHAR(50) NOT NULL,               -- news / funding_announcement
  title_th VARCHAR(255), title_en VARCHAR(255), slug VARCHAR(255),
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  body TEXT, cover_image_url TEXT,
  status VARCHAR(50) DEFAULT 'draft',      -- draft / published / archived
  author_id INT REFERENCES users(user_id), published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE documents (                   -- uploads: company certificate, certs (3.6.3)
  document_id SERIAL PRIMARY KEY,
  owner_type VARCHAR(50) NOT NULL, owner_id INT NOT NULL,  -- role_request / company / researcher
  doc_type VARCHAR(100), file_name VARCHAR(255), file_url TEXT,
  mime_type VARCHAR(100), uploaded_by INT REFERENCES users(user_id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE entity_views (                -- view / visit analytics (3.7.1.5 / 3.10.6)
  view_id BIGSERIAL PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL, entity_id INT,
  user_id INT REFERENCES users(user_id),  -- null for guests
  session_id VARCHAR(100), viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE entity_metrics (              -- derived rankings / citations / growth (3.7 / 3.8)
  metric_id SERIAL PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL, entity_id INT NOT NULL,
  period VARCHAR(20),                      -- 2026-05 / all-time
  citation_count INT DEFAULT 0, view_count INT DEFAULT 0,
  rank_overall INT, computed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_log (                   -- database change history (3.10.6.8)
  audit_id BIGSERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  action VARCHAR(50),                      -- create / update / delete / approve / reject / publish
  entity_type VARCHAR(100), entity_id INT,
  changes JSONB, ip_address VARCHAR(64),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data dictionary

Every table in the schema, grouped. Entities carry verification_status where relevant and link out to evidence_sources for traceability. Jump to a table:

Relationships & graph

All connections live in a single entity_relationships table — one typed, directional row per edge with an optional evidence reference. The same rows drive both the force-directed graph analysis and the layered supply-chain view.

Relationship types: affiliated_with · has_expertise · works_on · derived_to · owned_by / distributed_by · commercialized_by · funded_by · classified_as · related_to. Start with the single table; split into bridge tables (e.g. company_product) later for performance.

Import pipeline

Source data arrives as Excel. It lands in staging tables first, then is validated, mapped to the industry taxonomy, de-duplicated, and committed to master + relationship tables — never written straight into production.

POST /api/v1/import/excel          → { import_batch_id, sheets_detected[] }
POST /api/v1/import/{id}/validate  → { valid_rows, warning_rows, error_rows, warnings[] }
POST /api/v1/import/{id}/commit    → { created{}, updated{}, skipped{ duplicate_companies } }

Mock data

The front-end ships with mock JSON under src/data/ that mirrors the API contract, so the whole UI runs before the live API exists — swapping to the real API is a per-endpoint change with no UI rewrite. Expand a dataset to browse it as a readable table or toggle to the raw JSON.

Download SQL

Two ready-to-run database packages — schema + sample data + media files, zipped. Each unzips to /sql (DDL + seed inserts) and /medias/<entity>/* (images keyed to each row's id). Built with PostgreSQL; portable to MySQL / MSSQL / Oracle.

Regenerate after data changes with bash scripts/build-sql-packages.sh (writes both zips to public/sql-package/). Sample password hashes are placeholders — replace before any real use.

API reference

REST under a single version, https://api.…/api/v1. Every response uses the same envelope so the front-end handles success, lists and errors uniformly.

// success (single)               // list (paginated)
{                                {
  "success": true,                 "success": true,
  "data": { … },                   "data": [ … ],
  "meta": {                        "pagination": {
    "timestamp": "2026-05-29…"       "page": 1, "limit": 10,
  }                                  "total": 100, "total_pages": 10
}                                  }
                                 }
// error
{ "success": false,
  "error": { "code": "ENTITY_NOT_FOUND", "message": "Company not found" } }

Catalog, search & graph

Accounts, roles & operations

Auth, role requests & approval, company members, the landing CMS, analytics and admin — secured with OAuth 2.0 / JWT and scoped per role.

Example — dashboard overview

GET /api/v1/dashboard/overview?industry_category_id=3&verification_status=verified
{
  "summary": {
    "total_researchers": 1284, "total_companies": 642,
    "total_research_projects": 1950, "total_products": 1120,
    "total_innovations": 870, "total_funding_programs": 96
  },
  "industry_distribution": [
    { "industry_category_id": 3, "industry_name_en": "Medical Devices and Medical AI",
      "research_project_count": 560, "product_count": 330,
      "innovation_count": 250, "company_count": 204 }
  ],
  "verification_summary": { "verified": 2460, "pending": 540, "needs_update": 180 }
}

Example — graph

GET /api/v1/graph?center_entity_type=company&center_entity_id=1&depth=2
{
  "nodes": [
    { "id": "company:1",  "entity_type": "company",  "label": "Example Medical Co., Ltd.", "group": "company" },
    { "id": "product:11", "entity_type": "product",  "label": "Wound dressing", "group": "product" }
  ],
  "edges": [
    { "source": "company:1", "target": "product:11", "relationship_type": "owned_by", "weight": 1 }
  ]
}

Authentication & security

Identity supports Thai ID (DOPA) and email/password; password reset is email-based. The API is protected with OAuth 2.0 + JWT, and the platform must meet TCELS security requirements.

• Access control — role-based; least privilege per endpoint.
• Encryption — TLS in transit; encryption at rest for sensitive data.
• API security — OAuth 2.0 authorization + short-lived JWT access tokens.
• Web standards — Website Security Standard (WSS) & Web Application Security Standard (WAS).
• PDPA — Thailand Personal Data Protection Act B.E. 2562: consent, data-subject rights, 30-day response.

Sign-up captures email, password, name and a contact channel (phone / LINE ID); the front-end sign-in, forgot-password and reset-password flows are already built.

Roles, journey & approval

Five roles — guest, researcher, company representative, TCELS staff and admin. A guest signs up, optionally requests an elevated role (researcher or company rep), and staff/admin review and approve it; on approval the user is linked to a researcher/company record and gets a personalized workspace. Staff/admin accounts are created by an administrator only.

Network diagram

Reference deployment / network topology for the On-Cloud environment.

Roadmap & delivery

• Phase 1 — public web portal: landing, overview dashboards, smart search/filter, detail views, graph & supply-chain, auth + role-request UI.
• Phase 2 — staff/admin CMS: content management, company-role approval with DBD auto-fill, user management, exportable reports (Word/PDF), admin analytics; full live API + database.

Timeline: March–May 2026, delivered within 90 days across three milestones (10% / 60% / 30%), per the project TOR.

Sources: core_database.pdf (schema + API spec) · Update TOR 2.5.pdf (requirements). Diagram assets & regeneration prompts live in public/developer/.