Table Structure For Organization Table

[sauraww]

Proposal: Introduce organisation Table in superposition_schema

Context

We need a top-level organisation table within the superposition_schema to manage company-level data. This table will serve as a foundation for our multi-tenant architecture, allowing each organisation to be associated with one or more workspaces.

Proposed DDL

CREATE SCHEMA IF NOT EXISTS superposition;

-- Optional: Create an enum if we prefer a controlled set of statuses
-- CREATE TYPE superposition.org_status AS ENUM ('active', 'inactive', 'pending_kyb');

CREATE TABLE IF NOT EXISTS superposition.organisation (
    -- Instead of using an `id` column, we can rely on `name` as the primary key
    -- if it's guaranteed unique or stable enough to serve as a primary identifier.
    name VARCHAR(255) PRIMARY KEY NOT NULL, 
    admin_email VARCHAR(255) NOT NULL,
    -- If we prefer an enum type for strict value enforcement, uncomment:
    -- status superposition.org_status NOT NULL DEFAULT 'active',
    -- Otherwise, use a SMALLINT for flexibility:
    status SMALLINT NOT NULL DEFAULT 1, -- e.g. 1=active, 2=inactive, 3=pending_kyb
    
    contact_details JSONB,  -- Flexible storage for KYB and other compliance details
    sector VARCHAR(100),
    industry VARCHAR(100),
    
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);

-- Potential indexes:
-- If we frequently search by organisation name (though it's PK now, so already indexed),
-- we may not need an additional index on `name`.
-- CREATE INDEX idx_organisation_status ON superposition.organisation (status);

[sauraww]

•	Should we keep name as the primary key, or introduce an id column (e.g., UUID) for more flexibility?
•	Enum vs. SMALLINT for status: Do we prefer strict type enforcement or easier extensibility?
•	Any additional fields or indexes we should consider now?
•	Are there compliance or security considerations we need to address for admin_email or contact_details?

[Datron]

@sauraww can we keep status as enum? Small int is too vague. Contact details could be another table or columns in this one, its better to flatten it out. Can we have a column to map the Juspay side spoc for this account? It helps with errors and contacts. Also can we add a created_by and updated_by column as well?

[Datron]

BTW on the type TIMESTAMP WITHOUT TIME ZONE we can just do timestamp for now

[ShreyBana]

One important thing to consider here is that we'll be using the organization name for creating schemas. Should we instead use some kind of id?
Also schemas can only be upto 63 characters (thanks to @sauraww for looking it up), so we'll have to keep those validations in the API if we're going to be a little loose in the DB.

[pratikmishra356]

changes need to be done
id -> varchar with 30 characters limit -> primary key
country_code
contact_email
contact_phone
created_by