Organization Management Guide

Managing organizations via pgAdmin or psql. All queries target the hereby schema.

Connection

Host:     localhost (or AWS RDS endpoint)
Port:     5432
Database: hereby_platform
User:     hereby_admin
Schema:   hereby

---

Quick Reference

List all organizations

SELECT
  id,
  code,
  name,
  type,
  size,
  status,
  "isActive",
  "subscriptionTier",
  timezone,
  "createdAt"
FROM hereby.organizations
WHERE "deletedAt" IS NULL
ORDER BY "createdAt" DESC;

Organization with employee count

SELECT
  o.id,
  o.code,
  o.name,
  o.status,
  o.type,
  o.size,
  COUNT(e.id) AS employee_count
FROM hereby.organizations o
LEFT JOIN hereby.employees e
  ON e."organizationId" = o.id
  AND e."deletedAt" IS NULL
WHERE o."deletedAt" IS NULL
GROUP BY o.id
ORDER BY employee_count DESC;

Organization full detail

SELECT *
FROM hereby.organizations
WHERE code = 'BESTRH'
  AND "deletedAt" IS NULL;

---

Create Organization

INSERT INTO hereby.organizations (
  id, name, "legalName", code, "taxId",
  type, size, status,
  industry, email, phone,
  timezone, locale, currency,
  "fiscalYearStart",
  settings,
  "isActive",
  "subscriptionTier",
  address,
  "billingInfo",
  "createdAt", "updatedAt"
) VALUES (
  gen_random_uuid(),
  '새병원',                          -- name
  '의료법인 새병원',                    -- legalName
  'NEWHOSPITAL',                     -- code (unique)
  '123-45-67890',                    -- taxId
  'SME',                             -- type: CORPORATION | STARTUP | SME | NONPROFIT | GOVERNMENT | OTHER
  'SMALL',                           -- size: MICRO | SMALL | MEDIUM | LARGE | ENTERPRISE
  'TRIAL',                           -- status: ACTIVE | INACTIVE | SUSPENDED | TRIAL
  'Healthcare',                      -- industry
  'admin@newhospital.co.kr',         -- email
  '02-1234-5678',                    -- phone
  'Asia/Seoul',                      -- timezone
  'ko-KR',                           -- locale
  'KRW',                             -- currency
  1,                                 -- fiscalYearStart (month 1-12)
  '{
    "workHours": { "weeklyLimit": 52, "dailyStandard": 8 },
    "payroll":   { "payDay": 25, "cutoffDay": 20 },
    "features":  ["attendance", "payroll", "leave", "overtime"]
  }'::jsonb,
  true,                              -- isActive
  'starter',                         -- subscriptionTier
  '{
    "street": "서울시 강남구 테헤란로 123",
    "city": "서울",
    "state": "서울특별시",
    "postalCode": "06234",
    "country": "KR"
  }'::jsonb,
  '{
    "companyName": "의료법인 새병원",
    "taxId": "123-45-67890",
    "address": {
      "street": "서울시 강남구 테헤란로 123",
      "city": "서울",
      "state": "서울특별시",
      "postalCode": "06234",
      "country": "KR"
    },
    "contact": {
      "name": "홍길동",
      "email": "billing@newhospital.co.kr",
      "phone": "02-1234-5678"
    }
  }'::jsonb,
  NOW(), NOW()
);

---

Update Organization

Change status

UPDATE hereby.organizations
SET status = 'ACTIVE', "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Update subscription

UPDATE hereby.organizations
SET
  "subscriptionTier" = 'enterprise',
  "subscriptionExpiresAt" = '2027-12-31',
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Update payroll settings

UPDATE hereby.organizations
SET
  settings = jsonb_set(
    jsonb_set(settings, '{payroll,payDay}', '10'),
    '{payroll,cutoffDay}', '5'
  ),
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Update work hours

UPDATE hereby.organizations
SET
  settings = jsonb_set(settings, '{workHours}', '{"weeklyLimit": 40, "dailyStandard": 8}'),
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Add a feature

UPDATE hereby.organizations
SET
  settings = jsonb_set(
    settings,
    '{features}',
    (settings->'features')::jsonb || '["nurse-scheduler"]'::jsonb
  ),
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Update contact info

UPDATE hereby.organizations
SET
  email = 'new-admin@newhospital.co.kr',
  phone = '02-9876-5432',
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

---

Deactivate / Suspend Organization

Soft-delete (preferred — preserves data):

UPDATE hereby.organizations
SET
  status = 'SUSPENDED',
  "isActive" = false,
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Full soft-delete (marks as deleted):

UPDATE hereby.organizations
SET
  "deletedAt" = NOW(),
  "isActive" = false,
  "updatedAt" = NOW()
WHERE code = 'NEWHOSPITAL';

Never hard-delete. All entities use soft deletes via deletedAt.

---

Audit Queries

Organization with all related counts

SELECT
  o.code,
  o.name,
  o.status,
  (SELECT COUNT(*) FROM hereby.employees e WHERE e."organizationId" = o.id AND e."deletedAt" IS NULL) AS employees,
  (SELECT COUNT(*) FROM hereby.teams t WHERE t."organizationId" = o.id AND t."deletedAt" IS NULL) AS teams,
  (SELECT COUNT(*) FROM hereby.roles r WHERE r."organizationId" = o.id AND r."deletedAt" IS NULL) AS roles,
  (SELECT COUNT(*) FROM hereby.payroll_records p WHERE p."organizationId" = o.id AND p."deletedAt" IS NULL) AS payroll_records
FROM hereby.organizations o
WHERE o."deletedAt" IS NULL
ORDER BY o.code;

Recently modified organizations

SELECT code, name, status, "updatedAt"
FROM hereby.organizations
WHERE "deletedAt" IS NULL
ORDER BY "updatedAt" DESC
LIMIT 10;

Organizations by subscription tier

SELECT
  "subscriptionTier",
  COUNT(*) AS org_count,
  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active
FROM hereby.organizations
WHERE "deletedAt" IS NULL
GROUP BY "subscriptionTier"
ORDER BY org_count DESC;

Expired subscriptions

SELECT code, name, "subscriptionTier", "subscriptionExpiresAt"
FROM hereby.organizations
WHERE "deletedAt" IS NULL
  AND "subscriptionExpiresAt" < CURRENT_DATE
ORDER BY "subscriptionExpiresAt";

---

Enum Reference

Column	Values
type	CORPORATION, STARTUP, SME, NONPROFIT, GOVERNMENT, OTHER
size	MICRO, SMALL, MEDIUM, LARGE, ENTERPRISE
status	ACTIVE, INACTIVE, SUSPENDED, TRIAL

JSONB Field Schemas

settings

{
  "workHours": { "weeklyLimit": 52, "dailyStandard": 8 },
  "payroll": { "payDay": 25, "cutoffDay": 20 },
  "features": ["attendance", "payroll", "leave", "overtime"]
}

address / billingAddress

{
  "street": "서울시 강남구 테헤란로 123",
  "city": "서울",
  "state": "서울특별시",
  "postalCode": "06234",
  "country": "KR"
}

billingInfo

{
  "companyName": "의료법인 새병원",
  "taxId": "123-45-67890",
  "address": { "street": "...", "city": "...", "state": "...", "postalCode": "...", "country": "KR" },
  "contact": { "name": "홍길동", "email": "billing@example.co.kr", "phone": "02-1234-5678" }
}