> For the complete documentation index, see [llms.txt](https://tech.socialsafety.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://tech.socialsafety.net/ssn-for-organisations/schema-dictionary.md).

# Schema Dictionary

## CBO Tables

CBO following the HSDS schema comprises of 7 tables. We list and show the keys of these tables below:

> Update: For frequently updated and clearer schema you an view the Graphql playground [here](https://ssn-cbo-staging.herokuapp.com/graphql)

## Data Fields and Types Comment

### Organization

The organization record is used to provide basic description and details about each organization delivering services. Each service should be linked to the organization responsible for its delivery. One organization may deliver many services.\
To create an organization I have the following fields:

* id **string**: UNIQ; Each organization must have a unique identifier.
* name **string** The official or public name of the organization. **(Required)**
* tagline **string** A tag line for the organization. 
* alternate\_name **string**. Alternative or commonly used name for the organization.
* description **string**; A brief summary about the organization. It can contain markup such as plain text, HTML or Markdown.**(Required)**
* email **string** The contact e-mail address for the organization.**(Required)**
* url **string** The URL (website address) of the organization.**(Required)**
* tax\_status **string** Government assigned tax designation for tax-exempt organizations.
* tax\_id **string** A government issued identifier used for the purpose of tax administration. **(Required)**
* ein **number** (Employer Identification Number): A unique number assigned by the Internal Revenue Service to every employer in the United States for the purposes of identification. Similar in purpose to the Social Security Number assigned to individuals.
* year\_incorporated **date** The year in which the organization was legally formed.**(Required)**
* legal\_status **string** The legal status defines the conditions that an organization is operating under; Must be one of the following: non-profit, private corporation or a government organization. **(Required)**
* irs\_status **string** Free text description of the status of the the organization. e.g: ***Organization which receives a substantial part of its support from a governmental unit or the general public   170(b)(1)(A)(vi)***
* affiliation **string** Free text describing who the organization is affiliated to. e.g: ***Independent - the organization is an independent organization or an independent auxiliary (i.e., not affiliated with a National, Regional, or Geographic grouping of organizations).***
* ntee\_classification **String** The NTEE (National Taxonomy of Exempt Entities) Classification System was developed by The National Center for Charitable Statistics as part of its keyword searching criteria. This will help an organization be more easily found by users searching the database. See supported list [here](http://archive.icann.org/en/tlds/org/applications/dotorgfoundation/docs/ntee.html)
* ntee\_sub\_classification **String** The sub classification of the ntee parent. See full list of codes [here](http://archive.icann.org/en/tlds/org/applications/dotorgfoundation/docs/ntee.html)

### Services

Services are provided by organizations to a range of different groups. Details on where each service is delivered are contained in the services\_at\_location table.

* id **string** UNIQ; Each service must have a unique identifier.
* organization\_id **string** The identifier of the organization that provides this service.**(Required)**
* program\_id **string** The identifier of the program this service is delivered under.
* name **string** The official or public name of the service.**(Required)**
* alternate\_name **string** Alternative or commonly used name for a service.
* description **string** A description of the service.**(Required)**
* url **string** URL of the service
* email **string** Email address for the service **(Required)**
* status **string** The current status of the service. Must be one of `open` or`close`. **(Required)**
* interpretation\_services **string** A description of any interpretation services available for accessing this service.**(Required)**
* application\_process **string** The steps needed to access the service.**(Required)**
* wait\_time **int** number in mins a client may expect to wait before receiving a service.**(Required)**
* fees **string** Details of any charges for service users to access this service.**(Required)**
* accreditations **string** Details of any accreditations. Accreditation is the formal evaluation of an organization or program against best practice standards set by an accrediting organization.
* category **string** See top level, service parent taxonomy list [here](https://github.com/auntbertha/openeligibility/blob/972004ba59a62d5af9376ecee3d0f0b9a2b88cf0/taxonomy#L7) **(Required)**
* sub\_category **string** See service taxonomy list [here](https://github.com/auntbertha/openeligibility/blob/master/taxonomy) **(Required)**
* locations **string** Comma separated list of location where this service is rendered. This is picked from the list of existing services, and if no location has been created by an organization, this will be blank.
* contacts **string** Comma separated list of contacts for the service. This is picked from the list of existing contacts, and if no contact has been created by an organization, this will be blank.
* eligibilities **string** Comma separated listComma separated list of eligibility criteria for the service. This is picked from the list of existing eligibility.
* serviceAreas **string** Comma separated list of areas covered by the service. This is picked from the list of existing service areas.
* requiredDocuments **string** Comma separated list of documents required to be eligible for the service. This is picked from the list of existing required documents.
* payments **string** Comma separated list of payments accepted by the service. This is picked from the list of existing payments.
* fundings **string** Comma separated list of funding that is available for the service. This is picked from the list of existing funding.
* languages **string** Comma separated list of languages available to the service. This is picked from the list of existing languages.
* physicalAddresses **string** Comma separated list of physical addresses where the service operate. This is picked from the list of existing physical address.
* postalAddresses **string** Comma separated list of postal addresses covered by the service. This is picked from the list of existing postal addresses.
* accessibilityForDisabilities **string** Comma separated list of accessibilities available for disabled people. This is picked from the list of existing accessibilities for disabled people.
* schedule **string** A schedule for availability of the service. This is picked from the list of existing schedules.
* phones **string** Comma separated list of phones for reaching the service contacts. This is picked from the list of existing phones.

### Programs

Some organizations organize their services into programs. A program brings together a number of related services.

* *id* string UNIQ; Each program must have a unique identifier.
* *name* **string** The name of the program **(Required)**
* organization **object** The organization the program belongs to. (Required)
* description **string** The description for the program
* *alternate\_name* **string** An alternative name for the program
* services **Array** A comma separated list of service objects. 

### Location

The location tables provides details of the locations where organizations operate. Locations may be virtual, and one organization may have many locations.

* id string UNIQ; Each location must have a unique identifier
* organization\_id string Each location entry should be linked to a single organization. This is the organization that is responsible for maintaining information about this location. The identifier of the organization should be given here. Details of the services the organisation delivers at this location should be provided in the services\_at\_location table.**(Required)**
* name string The name of the location **(Required)**
* alternate\_name string An alternative name for the location
* description string A description of this location.**(Required)**
* transportation string A description of the access to public or private transportation to and from the location.**(Required)**
* latitude number Y coordinate of location expressed in decimal degrees in WGS84 datum. **(Required)**
* longitude number X coordinate of location expressed in decimal degrees in WGS84 datum **(Required)**
* zipcode: The zip code of the location. **(Required)**

### Contact

The contact table contains details of the named contacts for services and organizations. Note that in the HSDS data package format, if an individual is the contact for multiple services, their details may be duplicated multiple times in this table, each time with a new identifier, and with the rows containing different service ids.

* id string UNIQ; Each contact must have a unique identifier
* organization\_id string The identifier of the organization for which this is a contact. **(Required)**
* service\_id string The identifier of the service for which this is a contact
* name string The name of the person **(Required)**
* title string The job title of the person **(Required)**
* department string The department that the person is part of
* email string The email address of the person **(Required)**

### Eligibility

The eligibility tables contains details of the eligibility criteria for particular services

* id string UNIQ; Each entry must have a unique identifier **(Required)**
* service\_id string The identifier of the service for which this entry describes the eligibility criteria. **(Required)**
* description string The eligibility criteria.**(Required)** 
* eligibility string The eligibility criteria.**(Required)** Must be one of:
  * Infants & toddlers (0-4 years old)
  * Children (5-11 years old)
  * Teens (12-18 years old)
  * Transitional age youth (16-24 years old)
  * Adults (18 years or older)
  * Seniors
  * Other
* *other\_criteria* **string** Custom eligibility criteria that must be filled when `Others` option is selected in eligibilty.

### Service Area

The service\_area table contains details of the geographic area for which a service is available.

* id string UNIQ; Each service area must have a unique identifier
* service\_id string The identifier of the service for which this entry describes the service area **(Required)**
* service\_area string The geographic area where a service is available.This is a free-text description, and so may be precise or indefinite as necessary.**(Required)**
* description string A more detailed description of this service area.Used to provide any additional information that cannot be communicated using the structured area and geometry fields. **(Required)**

### Required Documents

The required\_document table contains details of any documents that are required in order to access or use services.

* id string UNIQ; Each document must have a unique identifier
* service\_id string The identifier of the service for which this entry describes the required document **(Required)**
* document string The document required to apply for or receive the service. e.g. `Government-issued ID`, `EU Passport` **(Required)**
* description string Description for the document required to apply for or receive the service

### Payment 

The payment\_accepted table contains details of the methods of payment that can be used in order to pay for services

* id string UNIQ; Each entry must have a unique identifier
* service\_id string The identifier of the services for which the entry describes the accepted payment methods **(Required)**
* payment string The method(s) of payment accepted for the service **(Required)**
* description string Description for the payment required to apply for or receive the service

### Funding

The funding table describes the sources of funding for a service or organisation.

* id string UNIQ; Each entry must have a unique identifier **(Required)**
* organization\_id string The identifier of the organization in receipt of this funding.**(Required)**
* service\_id string The identifier of the service in receipt of this funding
* source string A free text description of the source of funds for this organization or service.**(Required)** 

### Language

The language table contains details of the languages that are spoken at locations or services. This does not include languages which can only be used with interpretation.

* id **string** UNIQ; Each language must have a unique identifier
* service\_id **string** The identifier of the service for which the entry describes the languages in which services are delivered **(Required)**
* location\_id **string** The identifier of the location for which the entry describes the languages in which services are delivered
* language **string** Comma separated list of languages other than English, in which the service is delivered. Languages are listed as [ISO639-1 codes](https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes) **(Required)**
* description string Description for the language required to apply for or receive the service

### Physical Address

The addresses table contains the physical addresses for locations.

* id string UNIQ; Each physical address must have a unique identifier.
* attention **string** The person or entity whose attention should be sought at the location (Often included as ‘care of’ component of an address.) **(Required)**
* address **string** The first line(s) of the address, including office, building number and street.**(Required)**
* city **string** The city in which the address is located.**(Required)**
* region **string** The region in which the address is located (optional).
* state/province **string** The state or province in which the address is located. **(Required)**
* postal\_code **string** The postal code for the address.**(Required)**
* country **string** The country in which the address is located. This should be given as an [ISO 3361-1 country code](https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes) (two letter abbreviation).**(Required)**

### Postal Address

The postal address table contains the postal addresses for mail to a certain location. This may differ from the physical location.

* id string UNIQ; Each postal address must have a unique identifier
* attention string The person or entity whose attention should be sought at the location (Often included as ‘care of’ component of an address.) **(Required)**
* address string The first line(s) of the address, including office, building number and street. **(Required)**
* city string The city in which the address is located.**(Required)**
* region string The region in which the address is located (optional).**(Required)**
* state\_province string The state or province in which the address is located.**(Required)**
* postal\_code string The postal code for the address.**(Required)**
* country string The country in which the address is located. This should be given as an [ISO 3361-1 country code ](https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes)(two letter abbreviation) **(Required)**

### Accessibility for Disabilities

The accessibility\_for\_disabilities table contains details of the arrangements for access to locations for people who have disabilities.

* id string UNIQ; Each entry must have a unique identifier
* accessibility string Description of assistance or infrastructure that facilitate access to clients with disabilities.**(Required)**
* description string Any further details relating to the relevant accessibility arrangements at this location. E.g. whether advance notice is required to use an accessibility facility.

### Schedule

The schedule table contains details of when a service or location is open. Entries are RFC 5545 RRULES.

* id **string** UNIQ; Each entry must have a unique identifier **(Required)**
* valid\_from **date** The date from which the schedule information is valid. **(Required)**
* valid\_to **date** The last date on which the schedule information is valid. **(Required)**
* timezone **number** The timezone that all dates are expressed as, expressed as a UTC offset. Dates are assumed to be UTC otherwise. **(Required)**
* wkst **String** - The day on which the week starts, eg Mon, Tue and so on. **(Required)**
* byday **string**  - Comma separated days of the week. E.g. Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday. 
* description **string** Human readable description of the availability of the service.**(Required)**
* opens\_at **time** The time when a service or location opens. This should use HH:MM format and should include timezone information, either adding the suffix 'Z’when the date is in UTC, or including an offset from UTC (e.g. 09:00-05:00 for 9am EST.) **(Required)**
* closes\_at **time** The time when a service or location closes. This should use HH:MM format and should include timezone information, either adding the suffix ‘Z’ when the date is in UTC, or including an offset from UTC (e.g. 09:00-05:00 for 9am EST. **(Required)**

### Phone

The phone table contains details of the telephone numbers are used to contact organizations, services, and locations.

* id **string** UNIQ; Each entry must have a unique identifier
* location\_id **string** The identifier of the location where this phone number is located
* service\_id **string** The identifier of the service for which this is the phone number
* organization\_id string The identifier of the organisation for which this is the phone number. **(Required)**
* contact\_id **string** The identifier of the contact for which this is the phone number
* number **string** The phone number **(Required)**
* extension **number** The extension of the phone number **(Required)**
* type **string** Indicates the type of phone service, drawing from the RFC6350 list of types (text (for SMS), voice,fax, cell, video, pager, textphone, mobile). **(Required)**
* language **string** A comma separated list of ISO 639-1, [language codes](https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes) to represent the languages available from this phone service.The three-letter codes from ISO 639-2 provide greater accuracy when describing variants of languages, which may be relevant to particular communities. **(Required)**
* description **string** A description providing extra information about the phone service (e.g. any special arrangements for accessing, or details of availability at particular times.

### Initiatives

The initiatives table contains details of how organizations in an initiative interact with the initiative.

* id: ID 
* address: STRING 
* description: STRING 
* images: LIST OF STRING 
* name: STRING 
* organizationId: STRING 
* serviceId: STRING

#### InitiativeMember

This is a member of the initiative - this is an organization.

* id: ID 
* organizationId: STRING 
* initiativeId: STRING

#### InitiativeOptinRequest

This is a request an organization sends to join an initiative. It is handled by the admin/owner of the initiative.

* id: ID 
* createdAt: DATE 
* initiativeAddress: STRING 
* initiativeId: STRING 
* initiativeName: STRING 
* initiativeOwnerId: STRING 
* organizationId: STRING 
* status: STRING

#### Initiative Relationships

1. `Organization-[:MEMBER_OD]->Initiative`
2. `InitiativeMember-[:BELONGS]->Initiative`
3. `Initiative-[:HAS_SERVICE]->Service`

### Backend Schema Only

* Data Metadata
* id string UNIQ; Each entry must have a unique identifier
* resource\_id string UNIQ; Each service, program. location, address, or contact will have a unique identifier.
* resource\_type string UNIQ; The type of entity being referenced
* last\_action\_date datetime The date when data was changed.
* last\_action\_type string The kind of change made to the data; eg create,update, delete
* field\_name string The name of field that has been modified
* previous\_value string The previous value of a field that has been updated
* replacement\_value string The new value of a field that has been updated
* updated\_by string The name of the person who updated a value.

## Overall ER diagram showing connections **between tables**

![](https://1974413172-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MW3xxRi0Vf9N8eqeKB5%2F-MWrtf7Jv__Faeymfuj4%2F-MWs2Xn2v2FZKbmZ0C-k%2Fentity_relationship_diagram%20\(1\).svg?alt=media\&token=8ac0cace-2752-4cb3-99ae-55f613b0546f)

## **Sample Creation Flow**

Typical flow for creating an organization, a service or a program. The steps listed may not  necessarily be serial. 

### Creating an organization

* Add default metadata about org
* Create a new program 
* Create a service 
* Add an organization location 
* Add a contact 
* Add a phone 
* Add a funding 

### Creating a program

* Add default metadata about programs
* Add services to program

### Creating a service

* Add default metadata about service
* Add service taxonomy 
* Add Eligibilty 
* Add service area 
* Add required document 
* Add funding
* Add language
* Add location. Can pick from existing org location or create new ones. 
* Add accessibilty for disabilities
* Add physical address. Can pick from existing org physical address or create new ones. 
* Add postal address. Can pick from existing org postal address or create new ones.
* Add phone. Can pick from existing org phone or create new ones.
* Add contact. Can pick from existing org contact or create new ones.
* Add schedule. Can pick from existing org schedule or create new ones.