Appendix: Database Tables/Columns¶
adoption¶
The adoption table holds a row for each movement attached to an animal or person.
- ID
INTEGER A unique, incrementing number that identifies this record
- AdoptionNumber
VARCHAR A unique string identifying this movement (defaults to adoption.ID)
- AnimalID
INTEGER Link to the animal table
- OwnerID
INTEGER Link to the owner table
- RetailerID
INTEGER Link to the owner table for a retailer (0 for no retailer)
- OriginalRetailerMovementID
INTEGER Link to another movement in this table for an original movement to retailer that started this adoption
- EventID
INTEGER Link to the event table for the adoption event this movement came from
- MovementDate
TIMESTAMP The date the animal moved
- MovementType
INTEGER Link to the lksmovementtype table for the type of animal movement (none = reservation, adoption, foster, transfer, escaped, stolen, reclaimed, etc)
- InsuranceNumber
VARCHAR If your shelter does short term insurance when adopting, the policy number
- ReturnDate
TIMESTAMP The date the animal came back to the shelter from this movement (or null for not returned)
- ReturnedReasonID
INTEGER Link to the entryreason table for reason for return
- ReturnedByOwnerID
INTEGER Link to the owner table for the person who returned the animal
- ReasonForReturn
VARCHAR Free text, the reason the animal was returned
- ReservationDate
TIMESTAMP For reservation movements, the reservation date
- Donation
FLOAT A total of all donations attached to this movement
- ReservationCancelledDate
TIMESTAMP If this is a reservation and it has been cancelled, the date it was cancelled
- IsPermanentFoster
INTEGER 1 if this foster movement is permanent
- IsTrial
INTEGER 1 if this is a trial adoption movement
- TrialEndDate
TIMESTAMP The date this trial ends if it is a trial adoption movement
- Comments
VARCHAR Movement comments
- RecordVersion
INTEGER Optimistic lock flag
- CreatedBy
VARCHAR User who created this record
- CreatedDate
TIMESTAMP Date this record was created
- LastChangedBy
VARCHAR User who last changed this record
- LastChangedDate
TIMESTAMP Date this record was last changed
animal¶
The animal table holds a row for each animal on the system. Various denormalised fields at the end are used to track the current movement, whether the animal is on shelter, etc. These are useful for writing reports.
- ID
INTEGER A unique, incrementing number that identifies this record
- AnimalTypeID
INTEGER Link to the animaltype table
- AnimalName
VARCHAR The animal’s name
- NonShelterAnimal
INTEGER 1 if the animal is a non-shelter animal (kept out of figures)
- CrueltyCase
INTEGER 1 if the animal is a cruelty case (all 0/1 fields link to lksyesno table)
- BondedAnimalID
INTEGER Animal ID of bonded animal 1 (or 0)
- BondedAnimal2ID
INTEGER Animal ID of bonded animal 2 (or 0)
- BaseColourID
INTEGER Link to the basecolour table
- SpeciesID
INTEGER Link to the species table
- BreedID
INTEGER Primary breed (link to the breed table)
- Breed2ID
INTEGER Secondary breed (same as primary if not crossbreed)
- BreedName
VARCHAR Name of breed in form breed1 / breed2 if crossbreed
- CrossBreed
INTEGER 1 if animal is a crossbreed
- CoatType
INTEGER Link to lkcoattype table
- Markings
VARCHAR Distinguishing features field
- ShelterCode
VARCHAR The animal’s shelter code
- ShortCode
VARCHAR The short version of the shelter code
- UniqueCodeID
INTEGER The next UUU code can be generated by doing MAX(UniqueCodeID)
- YearCodeID
INTEGER The next NNN code is MAX(YearCodeID) for matching brought in year and type
- AcceptanceNumber
VARCHAR The acceptance number/Litter ID
- DateOfBirth
TIMESTAMP The animal’s date of birth
- AgeGroup
VARCHAR The animal’s age group based on date of birth at the most recent time it entered the shelter
- AgeGroupActiveMovement
VARCHAR The animal’s age group based on date of birth at the last time it left the shelter
- DeceasedDate
TIMESTAMP Date the animal died, if null the animal is still alive
- Sex
INTEGER Link to the lksex table
- Identichipped
INTEGER 1 if the animal is microchipped
- IdentichipNumber
VARCHAR The animal’s microchip number
- IdentichipDate
TIMESTAMP The date the animal was microchipped
- Tattoo
INTEGER 1 if the animal has an ear tattoo
- TattooNumber
VARCHAR The animal’s tattoo number
- TattooDate
TIMESTAMP The date the animal was tattooed
- Neutered
INTEGER 1 if the animal has been neutered/spayed
- NeuteredDate
TIMESTAMP The date the animal was neutered/spayed
- Declawed
INTEGER 1 if the animal has been declawed
- HiddenAnimalDetails
VARCHAR Hidden animal comments
- AnimalComments
VARCHAR The animal’s comments
- OwnersVetID
INTEGER Link to the owner’s vet (owner table, 0 for no value)
- CurrentVetID
INTEGER Link to the current vet (owner table, 0 for no value)
- OriginalOwnerID
INTEGER Link to the original owner (owner table)
- BroughtInByOwnerID
INTEGER Link to the owner who brought the animal in (owner table)
- ReasonForEntry
VARCHAR Freeform text, reason animal was brought to shelter
- ReasonNO
VARCHAR Freeform text, reason original owner didn’t bring the animal in
- DateBroughtIn
TIMESTAMP The date the animal was brought into the shelter
- EntryReasonID
INTEGER Reason for entry (link to entryreason table)
- HealthProblems
VARCHAR Health problems box on Vet tab
- PutToSleep
INTEGER 1 if the animal was euthanised
- PTSReason
VARCHAR Freeform text, reason the animal died
- PTSReasonID
INTEGER Euthanasia category - link to deathreason table
- IsDOA
INTEGER 1 if the animal was dead on arrival to the shelter
- IsTransfer
INTEGER 1 if the animal was a transfer from another animal shelter
- IsGoodWithCats
INTEGER Link to the lksynun table for yes/no/unknown
- IsGoodWithDogs
INTEGER Link to the lksynun table for yes/no/unknown
- IsGoodWithChildren
INTEGER Link to the lksynun table for yes/no/unknown
- IsHouseTrained
INTEGER Link to the lksynun table for yes/no/unknown
- IsNotAvailableForAdoption
INTEGER 1 if the animal should not be included for publishing as adoptable
- HasSpecialNeeds
INTEGER 1 if the animal has special needs
- ShelterLocation
INTEGER Location on shelter (link to internallocation table)
- DiedOffShelter
INTEGER 1 if the animal died off shelter (won’t be included on figures reports)
- Size
INTEGER animal’s size (link to lksize table)
- RabiesTag
VARCHAR The rabies tag field
- Adoptable
INTEGER 1 if the animal is adoptable according to publishing rules
- Archived
INTEGER 1 if the animal is off shelter now
- ActiveMovementID
INTEGER Link to the adoption table for the latest movement for this animal
- ActiveMovementType
INTEGER Link to lksmovementtype for the current movement type
- ActiveMovementDate
TIMESTAMP The current movement date
- ActiveMovementReturn
TIMESTAMP The current movement’s return date
- HasActiveReserve
INTEGER 1 if the animal has an open reservation
- HasTrialAdoption
INTEGER 1 if the animal is on trial adoption
- DisplayLocation
VARCHAR Shows a readable version of the animal’s location. If on shelter, the internal location. If off shelter, the movementtype, accompanied by the person name.
- MostRecentEntryDate
TIMESTAMP The most recent entry date - either DateBroughtIn or ActiveMovementDate
- TimeOnShelter
VARCHAR Readable time spent on shelter (eg: 1 year, 3 months)
- DaysOnShelter
INTEGER Number of days spent on shelter
- DailyBoardingCost
INTEGER The cost of one days board on shelter for this animal
- AnimalAge
VARCHAR Readable animal age (eg: 1 year, 3 months)
- RecordVersion
INTEGER For optimistic locking, each save increments this lock value
- CreatedBy
VARCHAR Name of user that created this record
- CreatedDate
TIMESTAMP Date this record was created
- LastChangedBy
VARCHAR Name of user that last changed this record
- LastChangedDate
TIMESTAMP Date this record was last changed
animalcontrol¶
The animalcontrol table holds a row for every animal control incident on the system.
- ID
INTEGER A unique, incrementing number that identifies this record
- IncidentDateTime
TIMESTAMP The date and time of the incident
- IncidentTypeID
INTEGER A link to the incidenttype table for the type of incident
- CallDateTime
TIMESTAMP The date and time the incident was reported
- CallNotes
VARCHAR The incident description
- CallTaker
VARCHAR The username of the system user who took the call
- CallerID
INTEGER A link to the owner table for the person who called to report the incident
- VictimID
INTEGER A link to the owner table for the person who was the victim (if any)
- DispatchAddress
VARCHAR The address the incident occurred at
- DispatchTown
VARCHAR The incident town/city
- DispatchCounty
VARCHAR The incident county/state
- DispatchPostcode
VARCHAR The incident postcode/zipcode
- DispatchLatLong
VARCHAR A geocode containing the latitude and longitude of the dispatch address
- DispatchedACO
VARCHAR The username of the system user representing the ACO
- PickupLocationID
INTEGER A link to the pickuplocation table
- DispatchDateTime
TIMESTAMP The date and time the ACO was dispatched to the incident
- RespondedDateTime
TIMESTAMP The date and time the ACO arrived at the incident
- FollowupDateTime / FollowupDateTime2 / FollowupDateTime3
TIMESTAMP The date and time the ACO should return to the incident to follow up
- FollowupComplete / FollowupComplete2 / FollowupComplete3
INTEGER 1 if the follow up has been done
- CompletedDate
TIMESTAMP The date the incident was closed and completed
- IncidentCompletedID
INTEGER A link to the incidentcompleted table
- SiteID
INTEGER A link to the site table for multi-site setups
- OwnerID / Owner2ID / Owner3ID
INTEGER A link to the owner table for the suspect
- AnimalDescription
VARCHAR A description of the animal involved in the incident
- SpeciesID
INTEGER A link to the species table for the animal involved in the incident
- Sex
INTEGER A link to the lksex table for the animal involved in the incident
- AgeGroup
VARCHAR The age group of the animal involved in the incident
dbfs¶
The dbfs table holds a row for each file stored in ASM’s database. Name and Path can be concatenated to form a full path. Eg: path = /templates name = cage_card.html
- ID
INTEGER A unique, incrementing number that identifies this record
- Name
VARCHAR The name of the element
- Path
VARCHAR The path to the element
- URL
VARCHAR Either file:[filename] or s3:[filename] for binary data stored in the filesystem or remote object storage like Amazon S3, BackBlaze B2 or Cloudflare R2 etc. If the data is in the Content field, the URL will be contain the text base64:
- Content
CLOB Base64 encoded content or null if the data is in a file storage (see URL)
media¶
The media table holds a row for each piece media attached to an animal, owner, etc.
- ID
INTEGER A unique, incrementing number that identifies this record
- MediaType
INTEGER 1 = File, 2 = Link
- MediaName
VARCHAR The name of the file within the dbfs table - always [media.ID].extension unless MediaType=2 in which case this will hold the link URL
- MediaNotes
VARCHAR The notes accompanying the media file (can optionally be used as description when publishing)
- DocPhoto
INTEGER 1 if this is the preferred photo of an animal for use with document templates
- WebsitePhoto
INTEGER 1 if this is the preferred photo of an animal for use on the web
- WebsiteVideo
INTEGER 1 if this is the preferred video link of the animal
- DocPhoto
INTEGER 1 if this is the preferred photo of an animal for use with generated documents
- ExcludeFromPublish
INTEGER 1 if this photo should be excluded from being used on websites or sent to third parties
- SignatureHash
VARCHAR For media containing documents that have been signed, this field will contain a cryptographic hash of the document file data so that it can be checked for tampering
- NewSinceLastPublish (deprecated)
INTEGER 1 if this media record was created after the last time a publish was done
- UpdatedSinceLastPublish (deprecated)
INTEGER 1 if this media record was updated after the last time a publish was done
- LastPublished (deprecated - see animalpublished table)
TIMESTAMP The date this record was last published to the web
- LastPublishedPF (deprecated)
TIMESTAMP Date this record was last published to PetFinder.com
- LastPublishedAP (deprecated)
TIMESTAMP Date this record was last published to AdoptAPet.com
- LastPublishedP911 (deprecated)
TIMESTAMP Date this record was last published to Pets911.com
- LastPublishedRG (deprecated)
TIMESTAMP Date this record was last published to RescueGroups PetAdoptionPortal.org
- LinkID
INTEGER The ID of the record in the table this media is linked to
- LinkTypeID
INTEGER Link to lksmedialink table to determine linked table
- CreatedDate
TIMESTAMP The date this record was created
- Date
TIMESTAMP Date this record was last updated
- RetainUntil
TIMESTAMP The date to automatically delete this record (or NULL to never delete)
- RecordVersion
INTEGER Optimistic lock flag
owner¶
The owner table holds a row for every person stored in ASM’s database.
- ID
INTEGER A unique, incrementing number that identifies this record
- OwnerType
INTEGER 1 = Individual, 2 = Organization, 3 = Couple
- OwnerCode
VARCHAR A unique code for this record, generated from first two letters of surname and padded ID
- OwnerTitle / OwnerTitle2
VARCHAR The person’s title, eg: Mr
- OwnerInitials / OwnerInitials2
VARCHAR The person’s initials
- OwnerForeNames / OwnerForeNames2
VARCHAR The person’s first name(s)
- OwnerSurname / OwnerSurname2
VARCHAR The person’s surname
- OwnerName
VARCHAR Title, Forenames and Surname concatenated together for display
- OwnerAddress
VARCHAR The address
- OwnerTown
VARCHAR The town or city (depending on locale)
- OwnerCounty
VARCHAR The county or state (depending on locale)
- OwnerPostcode
VARCHAR
- HomeTelephone
VARCHAR
- WorkTelephone / WorkTelephone2
VARCHAR
- MobileTelephone / MobileTelephone2
VARCHAR
- EmailAddress / EmailAddress2
VARCHAR
- DateOfBirth / DateOfBirth2
TIMESTAMP
- IdentificationNumber / IdentificationNumber2
VARCHAR The government issued identification number (passport, driving license, etc)
- Comments
VARCHAR
- GDPRContactOptIn
VARCHAR didnotask,declined,email,post,sms,phone
- ExcludeFromBulkEmail
INTEGER 1 if this person should not receive bulk emails
- IDCheck
INTEGER 1 if the person has been homechecked
- IsBanned
INTEGER 1 if this person has been banned from adopting animals
- IsVolunteer
INTEGER 1 if this person is a shelter volunteer
- IsHomeChecker
INTEGER 1 if this person homechecks people
- IsMember
INTEGER 1 if this person is a shelter member
- MembershipExpiryDate
TIMESTAMP The date this person’s membership expires
- MembershipNumber
VARCHAR The membership number
- IsDonor
INTEGER 1 if this person is a regular donor
- IsDriver
INTEGER 1 if this person is a driver for transport
- IsShelter
INTEGER 1 if this person is another animal shelter
- IsACO
INTEGER 1 if this person is an animal care officer
- IsStaff
INTEGER 1 if this person is shelter staff (permissions exist to prevent users viewing staff records)
- IsFosterer
INTEGER 1 if this person fosters animals
- IsRetailer
INTEGER 1 if this person is a retailer for animals (eg: Pet shop)
- IsVet
INTEGER 1 if this person is a vet
- IsGiftAid
INTEGER (UK only) 1 if this person is eligible for gift aid on their donations
- AdditionalFlags
VARCHAR Comma separated list of all person flags applying to this owner, includes additional person flags created under lookup data as well as the standard ones above.
- HomeCheckAreas
VARCHAR If this person homechecks other people, the areas they are wiling to check
- DateLastHomeChecked
TIMESTAMP The date this person was last homechecked
- HomeCheckedBy
INTEGER ID of the person record who homechecked this person
- MatchAdded
TIMESTAMP Date any looking for match was added to this person
- MatchExpires
TIMESTAMP Date this looking for info expires
- MatchActive
INTEGER 1 If we should consider this person’s looking for info when building the looking for report
- MatchSex
INTEGER
- MatchSize
INTEGER
- MatchAgeFrom
FLOAT
- MatchAgeTo
FLOAT
- MatchAnimalType
INTEGER
- MatchSpecies
INTEGER
- MatchBreed
INTEGER
- MatchBreed2
INTEGER
- MatchGoodWithCats
INTEGER
- MatchGoodWithDogs
INTEGER
- MatchGoodWithChildren
INTEGER
- MatchHouseTrained
INTEGER
- MatchCommentsContain
VARCHAR
- MatchFlags
VARCHAR