Database tables reference#

Introduction#

How tables are created#

The add command creates all the tables below.

How values are extracted#

Most values are extracted from OCDS JSON as SQL text. This is the case even if the JSON value is of a different type; for example, if the value of an id field is serialized as a JSON integer, it will be stored as text in the SQL tables.

There are two cases in which other types are used:

  • Date fields are converted to the timestamp type. Warning: If the value is an invalid date like "2020-02-30", or if the year is less than or equal to 0000, it will be converted to NULL.

  • Number fields are converted to the numeric type. Warning: If the value is an invalid number like "123a", it will be converted to NULL.

Metadata#

These tables are created and populated by the add command.

summaries.selected_collections#

This table is created (if it doesn’t exist) by the add command, in a separate summaries schema. It is a many-to-many relationship between the schema name, and each of the collection IDs that the user provided when creating each schema. It is not removed by the remove command, but the entries for that schema are removed.

If you need to change the collections to be summarized, remove the schema with the remove command and re-create it with the add command. This ensures that the schema’s name reflects its contents.

Column Name

Data Type

Description

schema

text

A Postgres schema name

collection_id

integer

A Kingfisher Process collection ID

note#

This table contains the note that the user provided when creating the schema.

Column Name

Data Type

Description

id

integer

An auto-incrementing integer

note

text

A user-assigned note

created_at

timestamp

The UTC time at which the note was created

Fields#

field_counts#

Column Name

Data Type

Description

collection_id

bigint

id from the Kingfisher Process collection table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”

path

text

JSON path of the field, excluding array indices

object_property

bigint

Number of occurrences of the field, across all array entries and all releases

array_count

bigint

If the field is an array, cumulative length of its occurrences, across all array entries and all releases

distinct_releases

bigint

Number of releases in which the field occurs

Releases#

release_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

table_id

integer

id from the Kingfisher Process release, compiled_release or record table

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

package_data_id

integer

id from the Kingfisher Process package_data table

package_version

text

Value of the version field in the package, or “1.0” if not set

date

timestamp

Value of the date field in the release object

tag

jsonb

Value of the tag array in the release object

language

text

Value of the language field in the release object

parties_role_counts

jsonb

JSONB object in which each key is a unique roles entry and each value is its number of occurrences across all parties arrays

total_parties_roles

numeric

Cumulative length of all parties/roles arrays

total_parties

bigint

Length of the parties array

total_planning_documents

bigint

Length of the planning/documents array

planning_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the planning/documents array

total_tender_documents

bigint

Length of the tender/documents array

tender_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the tender/documents array

total_awards

bigint

Length of the awards array

first_award_date

timestamp

Earliest date across all award objects

last_award_date

timestamp

Latest date across all award objects

total_award_documents

numeric

Cumulative length of all awards/documents arrays

total_award_items

numeric

Cumulative length of all awards/items arrays

total_award_suppliers

bigint

Cumulative length of all awards/suppliers arrays

sum_awards_value_amount

numeric

Sum of all awards/value/amount values (NOTE: This ignores any differences in currency)

total_unique_award_suppliers

bigint

Number of distinct suppliers across all award objects, using the unique_identifier_attempt field

award_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all awards/documents arrays

total_contracts

bigint

Length of the contracts array

total_contract_link_to_awards

bigint

Number of contracts/awardID values that match an awards/id value

first_contract_datesigned

timestamp

Earliest dateSigned across all contract objects

last_contract_datesigned

timestamp

Latest dateSigned across all contract objects

total_contract_documents

numeric

Cumulative length of all contracts/documents arrays

total_contract_milestones

numeric

Cumulative length of all contracts/milestones arrays

total_contract_items

numeric

Cumulative length of all contracts/items arrays

sum_contracts_value_amount

numeric

Sum of all contracts/value/amount values (NOTE: This ignores any differences in currency)

total_contract_implementation_documents

numeric

Cumulative length of all contracts/implementation/documents arrays

total_contract_implementation_milestones

numeric

Cumulative length of all contracts/implementation/milestones arrays

contract_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all contracts/documents arrays

contract_milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all contracts/milestones arrays

contract_implementation_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all contracts/implementation/documents arrays

contract_implementation_milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all contracts/implementation/milestones arrays

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all document arrays

total_documents

numeric

Cumulative length of all document arrays

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all milestone arrays

total_milestones

numeric

Cumulative length of all milestone arrays

source_id

text

source_id from the Kingfisher Process collection table

data_version

timestamp

data_version from the Kingfisher Process collection table

store_start_at

timestamp

store_start_at from the Kingfisher Process collection table

store_end_at

timestamp

store_end_at from the Kingfisher Process collection table

sample

boolean

sample from the Kingfisher Process collection table

transform_type

text

transform_type from the Kingfisher Process collection table

transform_from_collection_id

integer

transform_from_collection_id from the Kingfisher Process collection table

deleted_at

timestamp

deleted_at from the Kingfisher Process collection table

release

jsonb

data from the Kingfisher Process data table. This is the release, compiled release, record or embedded release.

package_data

jsonb

data from the Kingfisher Process package_data table. This is the package metadata from the release package or record package. NULL if the release_type is “compiled_release”.

release_check

jsonb

Data Review Tool output

record_check

jsonb

Data Review Tool output

field_list

jsonb

All JSON paths in the release object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

release_summary_no_data#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

table_id

integer

id from the Kingfisher Process release, compiled_release or record table

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

package_data_id

integer

id from the Kingfisher Process package_data table

package_version

text

Value of the version field in the package, or “1.0” if not set

date

timestamp

Value of the date field in the release object

tag

jsonb

Value of the tag array in the release object

language

text

Value of the language field in the release object

parties_role_counts

jsonb

JSONB object in which each key is a unique roles entry and each value is its number of occurrences across all parties arrays

total_parties_roles

numeric

Cumulative length of all parties/roles arrays

total_parties

bigint

Length of the parties array

total_planning_documents

bigint

Length of the planning/documents array

planning_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the planning/documents array

total_tender_documents

bigint

Length of the tender/documents array

tender_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the tender/documents array

total_awards

bigint

Length of the awards array

first_award_date

timestamp

Earliest date across all award objects

last_award_date

timestamp

Latest date across all award objects

total_award_documents

numeric

Cumulative length of all awards/documents arrays

total_award_items

numeric

Cumulative length of all awards/items arrays

total_award_suppliers

bigint

Cumulative length of all awards/suppliers arrays

sum_awards_value_amount

numeric

Sum of all awards/value/amount values (NOTE: This ignores any differences in currency)

total_unique_award_suppliers

bigint

Number of distinct suppliers across all award objects, using the unique_identifier_attempt field

award_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all awards/documents arrays

total_contracts

bigint

Length of the contracts array

total_contract_link_to_awards

bigint

Number of contracts/awardID values that match an awards/id value

first_contract_datesigned

timestamp

Earliest dateSigned across all contract objects

last_contract_datesigned

timestamp

Latest dateSigned across all contract objects

total_contract_documents

numeric

Cumulative length of all contracts/documents arrays

total_contract_milestones

numeric

Cumulative length of all contracts/milestones arrays

total_contract_items

numeric

Cumulative length of all contracts/items arrays

sum_contracts_value_amount

numeric

Sum of all contracts/value/amount values (NOTE: This ignores any differences in currency)

total_contract_implementation_documents

numeric

Cumulative length of all contracts/implementation/documents arrays

total_contract_implementation_milestones

numeric

Cumulative length of all contracts/implementation/milestones arrays

contract_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all contracts/documents arrays

contract_milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all contracts/milestones arrays

contract_implementation_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all contracts/implementation/documents arrays

contract_implementation_milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all contracts/implementation/milestones arrays

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences across all document arrays

total_documents

numeric

Cumulative length of all document arrays

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences across all milestone arrays

total_milestones

numeric

Cumulative length of all milestone arrays

Parties#

parties_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

party_index

bigint

Position of the party in the parties array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

party_id

text

Value of the id field in the party object

name

text

Value of the name field in the party object

roles

jsonb

Value of the roles array in the party object

identifier

text

Hyphenation of identifier/scheme and identifier/id in the party object

unique_identifier_attempt

text

Value of the id field in the party object if set, otherwise the identifier if set as above, otherwise the value of the name field in the party object

additionalidentifiers_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalIdentifiers array in the party object

total_additionalidentifiers

integer

Length of the additionalIdentifiers array in the party object

party

jsonb

The party object

field_list

jsonb

All JSON paths in the party object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

buyer_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

buyer

jsonb

The buyer object

buyer_id

text

Value of the id field in the buyer object

name

text

Value of the name field in the buyer object

identifier

text

Hyphenation of identifier/scheme and identifier/id in the buyer’s entry in the parties array

unique_identifier_attempt

text

Value of the id field in the buyer object if set, otherwise the identifier if set as above, otherwise the value of the name field in the buyer’s entry in the parties array, otherwise the value of the name field in the buyer object

additionalidentifiers_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalIdentifiers array in the buyer’s entry in the parties array

total_additionalidentifiers

integer

Length of the additionalIdentifiers array in the buyer’s entry in the parties array

link_to_parties

integer

1 if any parties/id value matches this buyer’s id value, otherwise 0

link_with_role

integer

1 if the buyer’s entry in the parties array has ‘buyer’ in its roles array, otherwise 0

party_index

bigint

Position of the buyer in the parties array

field_list

jsonb

All JSON paths in the buyer object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

procuringEntity_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

procuringentity

jsonb

The procuring entity object

procuringentity_id

text

Value of the id field in the procuring entity object

name

text

Value of the name field in the procuring entity object

identifier

text

Hyphenation of identifier/scheme and identifier/id in the procuring entity’s entry in the parties array

unique_identifier_attempt

text

Value of the id field in the procuring entity object if set, otherwise the identifier if set as above, otherwise the value of the name field in the procuring entity’s entry in the parties array, otherwise the value of the name field in the procuring entity object

additionalidentifiers_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalIdentifiers array in the procuring entity’s entry in the parties array

total_additionalidentifiers

integer

Length of the additionalIdentifiers array in the procuring entity’s entry in the parties array

link_to_parties

integer

1 if any parties/id value matches this procuring entity’s id value, otherwise 0

link_with_role

integer

1 if the procuring entity’s entry in the parties array has ‘procuringEntity’ in its roles array, otherwise 0

party_index

bigint

Position of the procuring entity in the parties array

field_list

jsonb

All JSON paths in the procuringentity object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

tenderers_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

tenderer_index

bigint

Position of the tenderer in the tenderers array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

tenderer

jsonb

The tenderer object

tenderer_id

text

Value of the id field in the tenderer object

name

text

Value of the name field in the tenderer object

identifier

text

Hyphenation of identifier/scheme and identifier/id in the tenderer’s entry in the parties array

unique_identifier_attempt

text

Value of the id field in the tenderer object if set, otherwise the identifier if set as above, otherwise the value of the name field in the tenderer’s entry in the parties array, otherwise the value of the name field in the tenderer object

additionalidentifiers_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalIdentifiers array in the tenderer’s entry in the parties array

total_additionalidentifiers

integer

Length of the additionalIdentifiers array in the tenderer’s entry in the parties array

link_to_parties

integer

1 if any parties/id value matches this tenderer’s id value, otherwise 0

link_with_role

integer

1 if the tenderer’s entry in the parties array has ‘tenderer’ in its roles array, otherwise 0

party_index

bigint

Position of the tenderer in the parties array

field_list

jsonb

All JSON paths in the tenderer object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

Planning section#

planning_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

budget_amount_amount

numeric

Value of the budget/amount/amount field in the planning object

budget_amount_currency

text

Value of the budget/amount/currency field in the planning object

budget_projectid

text

Value of the budget/projectID field in the planning object

total_documents

bigint

Length of the documents array in the planning object

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the documents array of the planning object

total_milestones

bigint

Length of the milestones array in the planning object

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences in the milestones array of the planning object

planning

jsonb

The planning object

field_list

jsonb

All JSON paths in the planning object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

planning_documents_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

document_index

bigint

Position of the document in the documents array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

document

jsonb

The document object

documenttype

text

Value of the documentType field in the document object

format

text

Value of the format field in the document object

field_list

jsonb

All JSON paths in the document object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

planning_milestones_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

milestone_index

bigint

Position of the milestone in the milestones array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

milestone

jsonb

The milestone object

type

text

Value of the type field in the milestone object

code

text

Value of the code field in the milestone object

status

text

Value of the status field in the milestone object

field_list

jsonb

All JSON paths in the milestone object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

Tender section#

tender_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

tender_id

text

Value of the id field in the tender object

title

text

Value of the title field in the tender object

status

text

Value of the status field in the tender object

description

text

Value of the description field in the tender object

value_amount

numeric

Value of the value/amount field in the tender object

value_currency

text

Value of the value/currency field in the tender object

minvalue_amount

numeric

Value of the minValue/amount field in the tender object

minvalue_currency

text

Value of the minValue/currency field in the tender object

procurementmethod

text

Value of the procurementMethod field in the tender object

mainprocurementcategory

text

Value of the mainProcurementCategory field in the tender object

additionalprocurementcategories

jsonb

Value of the additionalProcurementCategories field in the tender object

awardcriteria

text

Value of the awardCriteria field in the tender object

submissionmethod

text

Value of the submissionMethod field in the tender object

tenderperiod_startdate

timestamp

Value of the tenderPeriod/startDate field in the tender object

tenderperiod_enddate

timestamp

Value of the tenderPeriod/endDate field in the tender object

tenderperiod_maxextentdate

timestamp

Value of the tenderPeriod/maxExtentDate field in the tender object

tenderperiod_durationindays

numeric

Value of the tenderPeriod/durationInDays field in the tender object

enquiryperiod_startdate

timestamp

Value of the enquiryPeriod/startDate field in the tender object

enquiryperiod_enddate

timestamp

Value of the enquiryPeriod/endDate field in the tender object

enquiryperiod_maxextentdate

timestamp

Value of the enquiryPeriod/maxExtentDate field in the tender object

enquiryperiod_durationindays

numeric

Value of the enquiryPeriod/durationInDays field in the tender object

hasenquiries

text

Value of the hasEnquiries field in the tender object

eligibilitycriteria

text

Value of the eligibilityCriteria field in the tender object

awardperiod_startdate

timestamp

Value of the awardPeriod/startDate field in the tender object

awardperiod_enddate

timestamp

Value of the awardPeriod/endDate field in the tender object

awardperiod_maxextentdate

timestamp

Value of the awardPeriod/maxExtentDate field in the tender object

awardperiod_durationindays

numeric

Value of the awardPeriod/durationInDays field in the tender object

contractperiod_startdate

timestamp

Value of the contractPeriod/startDate field in the tender object

contractperiod_enddate

timestamp

Value of the contractPeriod/endDate field in the tender object

contractperiod_maxextentdate

timestamp

Value of the contractPeriod/maxExtentDate field in the tender object

contractperiod_durationindays

numeric

Value of the contractPeriod/durationInDays field in the tender object

numberoftenderers

numeric

Value of the numberOfTenderers field in the tender object

total_tenderers

integer

Length of the tenderers array in the tender object

total_documents

bigint

Length of the documents array in the tender object

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the documents array of the tender object

total_milestones

bigint

Length of the milestones array in the tender object

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences in the milestones array of the tender object

total_items

bigint

Length of the items array in the tender object

tender

jsonb

The tender object

field_list

jsonb

All JSON paths in the tender object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

tender_summary_no_data#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

tender_id

text

Value of the id field in the tender object

title

text

Value of the title field in the tender object

status

text

Value of the status field in the tender object

description

text

Value of the description field in the tender object

value_amount

numeric

Value of the value/amount field in the tender object

value_currency

text

Value of the value/currency field in the tender object

minvalue_amount

numeric

Value of the minValue/amount field in the tender object

minvalue_currency

text

Value of the minValue/currency field in the tender object

procurementmethod

text

Value of the procurementMethod field in the tender object

mainprocurementcategory

text

Value of the mainProcurementCategory field in the tender object

additionalprocurementcategories

jsonb

Value of the additionalProcurementCategories field in the tender object

awardcriteria

text

Value of the awardCriteria field in the tender object

submissionmethod

text

Value of the submissionMethod field in the tender object

tenderperiod_startdate

timestamp

Value of the tenderPeriod/startDate field in the tender object

tenderperiod_enddate

timestamp

Value of the tenderPeriod/endDate field in the tender object

tenderperiod_maxextentdate

timestamp

Value of the tenderPeriod/maxExtentDate field in the tender object

tenderperiod_durationindays

numeric

Value of the tenderPeriod/durationInDays field in the tender object

enquiryperiod_startdate

timestamp

Value of the enquiryPeriod/startDate field in the tender object

enquiryperiod_enddate

timestamp

Value of the enquiryPeriod/endDate field in the tender object

enquiryperiod_maxextentdate

timestamp

Value of the enquiryPeriod/maxExtentDate field in the tender object

enquiryperiod_durationindays

numeric

Value of the enquiryPeriod/durationInDays field in the tender object

hasenquiries

text

Value of the hasEnquiries field in the tender object

eligibilitycriteria

text

Value of the eligibilityCriteria field in the tender object

awardperiod_startdate

timestamp

Value of the awardPeriod/startDate field in the tender object

awardperiod_enddate

timestamp

Value of the awardPeriod/endDate field in the tender object

awardperiod_maxextentdate

timestamp

Value of the awardPeriod/maxExtentDate field in the tender object

awardperiod_durationindays

numeric

Value of the awardPeriod/durationInDays field in the tender object

contractperiod_startdate

timestamp

Value of the contractPeriod/startDate field in the tender object

contractperiod_enddate

timestamp

Value of the contractPeriod/endDate field in the tender object

contractperiod_maxextentdate

timestamp

Value of the contractPeriod/maxExtentDate field in the tender object

contractperiod_durationindays

numeric

Value of the contractPeriod/durationInDays field in the tender object

numberoftenderers

numeric

Value of the numberOfTenderers field in the tender object

total_tenderers

integer

Length of the tenderers array in the tender object

total_documents

bigint

Length of the documents array in the tender object

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the documents array of the tender object

total_milestones

bigint

Length of the milestones array in the tender object

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences in the milestones array of the tender object

total_items

bigint

Length of the items array in the tender object

tender_items_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

item_index

bigint

Position of the item in the items array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

item

jsonb

The item object

item_id

text

Value of the id field in the item object

quantity

numeric

Value of the quantity field in the item object

unit_value_amount

numeric

Value of the unit/value/amount field in the item object

unit_value_currency

text

Value of the unit/value/currency field in the item object

classification

text

Hyphenation classification/scheme and classification/id in the party object

additionalclassifications_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalClassifications array in the item object

total_additionalclassifications

integer

Length of the additionalClassifications array in the item object

field_list

jsonb

All JSON paths in the item object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

tender_documents_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

document_index

bigint

Position of the document in the documents array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

document

jsonb

The document object

documenttype

text

Value of the documentType field in the document object

format

text

Value of the format field in the document object

field_list

jsonb

All JSON paths in the document object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

tender_milestones_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

milestone_index

bigint

Position of the milestone in the milestones array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

milestone

jsonb

The milestone object

type

text

Value of the type field in the milestone object

code

text

Value of the code field in the milestone object

status

text

Value of the status field in the milestone object

field_list

jsonb

All JSON paths in the milestone object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

Awards section#

awards_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

award_index

bigint

Position of the award in the awards array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

award_id

text

Value of the id field in the award object

title

text

Value of the title field in the award object

status

text

Value of the status field in the award object

description

text

Value of the description field in the award object

value_amount

numeric

Value of the value/amount field in the award object

value_currency

text

Value of the value/currency field in the award object

date

timestamp

Value of the date field in the award object

contractperiod_startdate

timestamp

Value of the contractPeriod/startDate field in the award object

contractperiod_enddate

timestamp

Value of the contractPeriod/endDate field in the award object

contractperiod_maxextentdate

timestamp

Value of the contractPeriod/maxExtentDate field in the award object

contractperiod_durationindays

numeric

Value of the contractPeriod/durationInDays field in the award object

total_suppliers

integer

Length of the suppliers array in the award object

total_documents

bigint

Length of the documents array in the award object

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the documents array of the award object

total_items

bigint

Length of the items array in the award object

award

jsonb

The award object

field_list

jsonb

All JSON paths in the award object as well as in the related contracts’ contract object (prefixed by contracts/), expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

award_suppliers_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

award_index

bigint

Position of the award in the awards array

supplier_index

bigint

Position of the supplier in the suppliers array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

supplier

jsonb

The supplier object

supplier_id

text

Value of the id field in the supplier object

name

text

Value of the name field in the supplier object

identifier

text

Hyphenation of identifier/scheme and identifier/id in the supplier’s entry in the parties array

unique_identifier_attempt

text

Value of the id field in the supplier object if set, otherwise the identifier if set as above, otherwise the value of the name field in the supplier’s entry in the parties array, otherwise the value of the name field in the supplier object

additionalidentifiers_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalIdentifiers array in the supplier’s entry in the parties array

total_additionalidentifiers

integer

Length of the additionalIdentifiers array in the supplier’s entry in the parties array

link_to_parties

integer

1 if any parties/id value matches this supplier’s id value, otherwise 0

link_with_role

integer

1 if the supplier’s entry in the parties array has ‘supplier’ in its roles array, otherwise 0

party_index

bigint

Position of the supplier in the parties array

field_list

jsonb

All JSON paths in the supplier object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

award_items_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

award_index

bigint

Position of the award in the awards array

item_index

bigint

Position of the item in the items array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

item

jsonb

The item object

item_id

text

Value of the id field in the item object

quantity

numeric

Value of the quantity field in the item object

unit_value_amount

numeric

Value of the unit/value/amount field in the item object

unit_value_currency

text

Value of the unit/value/currency field in the item object

classification

text

Hyphenation classification/scheme and classification/id in the party object

additionalclassifications_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalClassifications array in the item object

total_additionalclassifications

integer

Length of the additionalClassifications array in the item object

field_list

jsonb

All JSON paths in the item object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

award_documents_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

award_index

bigint

Position of the award in the awards array

document_index

bigint

Position of the document in the documents array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

document

jsonb

The document object

documenttype

text

Value of the documentType field in the document object

format

text

Value of the format field in the document object

field_list

jsonb

All JSON paths in the document object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

Contracts section#

contracts_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

awardid

text

Value of the awardID field in the contract object

link_to_awards

integer

1 if any awards/id value matches this contract’s awardID value, otherwise 0

contract_id

text

Value of the id field in the contract object

title

text

Value of the title field in the contract object

status

text

Value of the status field in the contract object

description

text

Value of the description field in the contract object

value_amount

numeric

Value of the value/amount field in the contract object

value_currency

text

Value of the value/currency field in the contract object

datesigned

timestamp

Value of the dateSigned field in the contract object

period_startdate

timestamp

Value of the period/startDate field in the contract object

period_enddate

timestamp

Value of the period/endDate field in the contract object

period_maxextentdate

timestamp

Value of the period/maxExtentDate field in the contract object

period_durationindays

numeric

Value of the period/durationInDays field in the contract object

total_documents

bigint

Length of the documents array in the contract object

document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the documents array of the contract object

total_milestones

bigint

Length of the milestones array in the contract object

milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences in the milestones array of the contract object

total_items

bigint

Length of the items array in the contract object

total_implementation_documents

bigint

Length of the implementation/documents array in the contract object

implementation_document_documenttype_counts

jsonb

JSONB object in which each key is a unique documentType value and each value is its number of occurrences in the implementation/documents array of the contract object

total_implementation_milestones

bigint

Length of the implementation/milestones array in the contract object

implementation_milestone_type_counts

jsonb

JSONB object in which each key is a unique type value and each value is its number of occurrences in the implementation/milestones array of the contract object

contract

jsonb

The contract object

field_list

jsonb

All JSON paths in the contract object as well as in the related award’s award object (prefixed by awards/), expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

contract_items_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

item_index

bigint

Position of the item in the items array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

item

jsonb

The item object

item_id

text

Value of the id field in the item object

quantity

numeric

Value of the quantity field in the item object

unit_value_amount

numeric

Value of the unit/value/amount field in the item object

unit_value_currency

text

Value of the unit/value/currency field in the item object

classification

text

Hyphenation classification/scheme and classification/id in the party object

additionalclassifications_ids

jsonb

Hyphenation of scheme and id for each entry of the additionalClassifications array in the item object

total_additionalclassifications

integer

Length of the additionalClassifications array in the item object

field_list

jsonb

All JSON paths in the item object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

contract_documents_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

document_index

bigint

Position of the document in the documents array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

document

jsonb

The document object

documenttype

text

Value of the documentType field in the document object

format

text

Value of the format field in the document object

field_list

jsonb

All JSON paths in the document object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

contract_milestones_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

milestone_index

bigint

Position of the milestone in the milestones array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

milestone

jsonb

The milestone object

type

text

Value of the type field in the milestone object

code

text

Value of the code field in the milestone object

status

text

Value of the status field in the milestone object

field_list

jsonb

All JSON paths in the milestone object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

Contract implementation section#

contract_implementation_transactions_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

transaction_index

bigint

Position of the transaction in the transactions array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

transaction

jsonb

The transaction object

date

text

Value of the date field in the transaction object

value_amount

numeric

Value of the value/amount field, or the deprecated amount/amount field, in the transaction object

value_currency

text

Value of the value/currency field, or the deprecated amount/currency field, in the transaction object

field_list

jsonb

All JSON paths in the transaction object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

contract_implementation_documents_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

document_index

bigint

Position of the document in the documents array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

document

jsonb

The document object

documenttype

text

Value of the documentType field in the document object

format

text

Value of the format field in the document object

field_list

jsonb

All JSON paths in the document object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.

contract_implementation_milestones_summary#

Column Name

Data Type

Description

id

bigint

An identifier for a row in the Kingfisher Process release, compiled_release or record table

contract_index

bigint

Position of the contract in the contracts array

milestone_index

bigint

Position of the milestone in the milestones array

release_type

text

Either “release”, “compiled_release”, “record” or “embedded_release”. If “release”, the data was read from the release table. If “compiled_release”, the data was read from the compiled_release table. If “record”, the data was read from a record’s compiledRelease field in the record table. If “embedded_releases”, the data was read from a record’s releases array in the record table.

collection_id

integer

id from the Kingfisher Process collection table

ocid

text

Value of the ocid field in the release object

release_id

text

Value of the id field in the release object (NULL if the release_type is “compiled_release” or “record”)

data_id

integer

id from the Kingfisher Process data table

milestone

jsonb

The milestone object

type

text

Value of the type field in the milestone object

code

text

Value of the code field in the milestone object

status

text

Value of the status field in the milestone object

field_list

jsonb

All JSON paths in the milestone object, expressed as a JSONB object in which keys are paths and values are numbers of occurrences. Paths exclude array indices. This column is only available if the –field-lists option is used.